Appending your table in MS Access

Overview

An append query selects records from one or more data sources and copies the selected records to an existing table.

For example, suppose that you acquire a database that contains a table of potential new customers, and that you already have a table in your existing database that stores that kind of data. You'd like to store the data in one place, so you decide to copy it from the new database into your existing table. To avoid entering the new data manually, you can use an append query to copy the records.

Benefits of using an append query

By using a query to copy data, you can:

• Append multiple records in one pass    If you copy data manually, you usually have to perform multiple copy/paste operations. By using a query, you select all the data at once, and then copy it.
• Review your selection before you copy it    You can view your selection in Datasheet view and can make adjustments to your selection as needed before you copy the data. This can be particularly handy if your query includes criteria or expressions, and you need several tries to get it just right. You cannot undo an append query. If you make a mistake, you must either restore your database from a backup or correct your error, either manually or by using a delete query.
• Use criteria to refine your selection    For example, you might want to only append records of customers who live in your city.
• Append records when some of the fields in the data sources don't exist in the destination table     For example, suppose that your existing customer table has eleven fields, and the new table that you want to copy from only has nine of those eleven fields. You can use an append query to copy the data from the nine fields that match and leave the other two fields blank.

Basic steps of an append query

The process of creating an append query follows these basic steps:

• Create a select query    You start by selecting the data that you want to copy. You can adjust your select query as needed, and run it as many times as you want to make sure you are selecting the data that you want to copy.
• Convert the select query to an append query    After your selection is ready, you change the query type to Append.
• Choose the destination fields for each column in the append query    In some cases, Access automatically chooses the destination fields for you. You can adjust the destination fields, or choose them if Access did not.
• Preview and run the query to append the records    Before you append the records, you can switch to Datasheet view for a preview of the appended records.

 IMPORTANT   You cannot undo an append query. Consider backing up your database or the destination table. For more information about backing up a database, see the article Back up a database.