Append Queries: Automatically Append Data from One Table to Another

Append Queries are really useful when you acquire a new table full of data, and want to add it to an existing table in your database.  This scenario may arise, for example, if your company purchases a mailing list, and you are asked to add the new names to those you already have stored.  Rather than having to retype the new names by hand, an append query can be set up to copy the data from the new table into that which is already in use.

I have created an exercise to demonstrate how this process works.  Lets begin by looking at the two tables we are going to be working with:

Figure 1: tblContacts - the existing table consisting
of  ID (autonumber), FirstName (text) and Surname (text).

Figure 2: tblMoreNames - new data to be appended, consisting of
CustomerId (autonumber), Title (text), Initials (text), and LastName (text).
Notice the fields names are somwhat different and there is also a
risk of duplicating the ID fields from both tables.
More about this soon.

As you can see, figure 1 shows tblContacts, our existing table containing 15 records.  We are going to create an append query to add the 10 new records from tblMoreNames to the data already stored in this table.  You may have noticed that the field names contained in both tables are slightly different, and tblMoreNames has a Title field which tblContacts does not.  You may also have spotted that there is a risk of us attempting to append duplicate data from the CustomerID field of tblMoreNames.  Access would prevent this because the ID field in tblContacts is a Primary Key, so needs to be a unique value.   The simple solution to this, is for us not to include the customerId field (or anyother incompatible field)  in the append query.  As such, we shall just append data from the Initials and LastName fields of tblMoreNames to the FirstName and Surname fields of tblContacts.  Although the field names are slightly different, the data they contain is compatible, and for the purposes of this exercise, it is ok for us to append an Initial in place of a FirstName - the idea here is to demonstrate that the field names do not have to match exactly.

Creating an Append Query

Here is the procedure for creating the append query:

1. Click the QUERY DESIGN icon (located in the OTHER group of the CREATE ribbon).   The QUERY DESIGN window then opens along with the SHOW TABLE dialog form.   
2. The next step is to add tblMoreNames to the QUERY DESIGN window.  Do this by clicking ADD in the SHOW TABLE dialog form.  Notice it is the table containing the data to be appended that we have selected.
3. Click the APPEND icon from the QUERY TYPE group of the DESIGN ribbon.  As you do this, you will see the APPEND dialog box open.
4. You are now asked to select the name of the original table to which the new data is to be appended. So select tblContacts from the drop down list.  
5. You are also asked whether this table is stored in the current database or in an external database. In this exercise both tables are stored in the current database.  This is the default button displayed in the option group, so there should not be any need to change it.
6. Click OK to close the dialog box.
7. Next we are going to select the fields from tblMoreNames to be appended. To do this drag and drop the Initials and LastName fields from the table (in the top half of the window) down onto the design grid.
8. Next we are going to tell Access which fields the data from Initials and Lastname will be appended to.  To do this go down to the APPEND TO row of the design grid (see figure 3 below), and select FirstName in the Initials column, and Surname in the LastName Column.

   

   Figure 3: The Query Design Grid.

9. We could add query criteria at this stage, but this particular exercise does not require any.  If we did, however, this is added in the CRITERIA row just like it is with a select query. 
10. If you want to view the data that is going to be appended, click the VIEW icon from the RESULTS group of the DESIGN ribbon.  It is especially important to do this if any if any criteria is applied in step 9 above.
11. Once you are satisfied the correct data is going to be appended, click the RUN icon, again from the RESULTS group of the DESIGN ribbon.
12. A dialog box opens informing us that 10 rows are going to be appended, and asking us to confirm that we want to go ahead with this operation.  Click YES to complete.

To see the result of our Append Query, re-open tblContacts.  

Figure 4: tblContacts after the
Append Query has been run.

We can now see the new records appended to the end of our original data.  Notice each of the newly appended records has been automatically allocated a unique ID number.  This, of course, is because the ID field in the original table had been set to the AUTONUMBER datatype.