- slide 1 of 5
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.
- slide 2 of 5
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.
- slide 3 of 5
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).
- slide 4 of 5
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).