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).
Creating an Append Query
Here is the procedure for creating the append query:
- 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.
- 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.
- 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.
- 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.
- 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.
- Click OK to close the dialog box.
- 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.
- 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.
- 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.
- 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.
- 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.
- 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.
|Figure 4: tblContacts after the |
Append Query has been run.