Start With an Example
Let’s say you have a current asset database in which you track several attributes of each asset. You then inherit a new asset tracking database from a different company. You want to copy the data from the new database into your existing database. An append query will let you add data to the existing database from the new database.
I set up a simple blank database with a single table called “Assets”. The assets table has five fields: ID, Item Type, Color, Weight and Date Added to Inventory. I then added a few sample rows to the datasheet view (Figure 1).
Next, we’ll set up our “New Assets” table that we’ll later append to the existing Assets table. In the “New Assets” table I only have three fields: Name of Item, Description and Weight of Item. I then populated this table with some data (Figure 2).
Now, we want to take the data from the New Assets table and append it to the original table. We would expect each row to be added to the existing table with the data from the columns that exist in both tables. Note that the field names don’t line up exactly, but the data type for each field is the same. Text can be copied into text fields, number into number fields, etc.
The first step is to create a select query to display the data we wish to copy to the Assets table.
1. To do this, we’ll click the Create -> Query Design button.
2. Select the table we wish to copy from – the New Assets table in this case and click Add.
3. Double click each field we want to append. In this case we want the Item Type and Sales fields. Note that you can use expressions and other types of criteria to obtain only the specific results you want. Since our table is very simple we’ll just return all results (Figure 3).
4. Click the Run button to view the results (Figure 4).
5. Once you are happy with the results we’ll turn this select query into an append query. Click back on the Design View. Next click the Append button under Query Type. The Append window will open.
6. Select the Assets table and click OK.
7. Now we should see an “Append To” row show up. For each field we’ll want to align the names so they append properly. For example, the “Name of Item” field in the “New Assets” table will line up with the “Item Type” field in the original Assets table. Likewise, Description lines up with Color and “Weight of Item” lines up with Weight (Figure 5).
8. Once we’re happy we can click the Run button to run the append query. You should receive a warning letting you know how many rows will be added. Go ahead and click OK.
9. Next, you can reopen your original table and see the results of the append operation (Figure 6).
Append queries aren’t especially hard to use but can be a life saver if you need to copy large amounts of data from one table to another. Normal copy operations only allow a single row to be copied so consider using an Append query next time you need to do a big copy operation.