Adding new records using an Append query in Microsoft Access 2019

Use an Append query when you wishto add new records to an existing table by way of copying and pastingfrom one table to another. The field names do not have to be the same,but it is essential that the data is the same. For instance, to copy avalue from one table to another table, the data type has to be a numberin both tables.

If both tables contain the same field names and field sizes, then thetransition is very simple. If the tables contain the same field namesbut not the same field sizes, then data could be lost in the transitionfrom one table to the next. If the field names are not the same, you canspecify which fields in the destination table they would match. Tocreate such a query, follow these steps:

  1. Create a new query in Design View,using the example database from the previous topic.
  2. When prompted by the Show Tabledialog box, select the NewKidsEnrolledtable. Click Add, and then Close to move to the query grid.
  3. Click on the Append icon, whichappears in the Query Type group.
  4. Select the name of the table into which you want to append the data.For this example, we will use ElkPupilsTbl. Click on the OK icon to continue, as illustrated in thefollowing screenshot:
ms office 459
  1. Add the fields from the NewKidsEnrolled table that you wish toappend to the ElkPupilsTbl table. Notethat you do not have to select all the fields, only those you wish toappend. Additionally, note that criteria can also be set using the Criteria: row on the query grid. The fieldscan be seen in the following screenshot:
ms office 694
  1. If the fields match those of the table you are appending to, thenAccess will add the corresponding field to match those of the NewKidsEnrolled table. If the field namesdiffer from those that are in the appended table, click to specify thefield that matches.
  2. Click the Run icon on the Results group to append the data. You willbe warned that five rows will be appended from the NewKidsEnrolled table to the ElkPupilsTbl table. Note that this cannot beundone. The message can be seen in the following screenshot:
ms office 65
  1. Click Yes to continue. The ElkPupilsTbl table is updated with the newrecords. Save the query as AppendNewKids.

Now that you have learned how to append records, let’s investigateCrosstab queries in the next topic.

Leave a Comment