Use an append query to add records to a table

An append query can make it quicker for you to add new records to an existing table. The append query works by selecting records from one or more data sources and copying them to an existing table. For example, say that you acquire a database that contains a table of new customers, and since you already have another table in your database that stores similar data, you'd like to add those new customers to your table. Instead of copying the data from the acquired database into the table in your existing database, use an append query.

Note: The append query isn’t available in Access apps, but you can use an append query in a web database by opening the database in Access.

Create and run an append query

In this section

Step 1: Use a query to select the records

Step 2: Convert the select query to an append query

Step 3: Choose the destination fields

Step 4: Preview and run the append query

Unblock your query

Some benefits of using an append query

Important:  Since you cannot undo an append query, consider backing up your database or the destination table before using an append query.

Step 1: Use a query to select the records

  1. Open the database that contains the records that you want to copy.

  2. On the Create tab, in the Queries group, click Query Design.

    The query designer opens, and the Show Table dialog box appears.

  3. Double-click the tables or queries that contain the records that you want to copy, and then click Close.

    The tables or queries appear as one or more windows in the query designer. Each window lists the fields in a table or query. This figure shows a typical table in the query designer.

  4. Double-click each field that you want to add. The selected fields appear in the Field row in the query design grid.

    The data types of the fields in the source table must be compatible with the data types of the fields in the destination table. Text fields are compatible with most other types of fields. Number fields are only compatible with other number fields. For example, you can append numbers to a text field, but you cannot append text into a number field.

    Tip:  To quickly add all the fields in a table, double-click the asterisk (*) at the top of the list of table fields.

  5. You can also enter one or more criteria in the Criteria row of the design grid. The following table shows some example criteria the effect they have on a query.

Criteria

Effect

> 234

Returns all numbers greater than 234. To find all numbers less than 234, use < 234.

>= "Callahan"

Returns all records from Callahan through the end of the alphabet.

Between #2/2/2010# And #12/1/2010#

Returns dates from 2-Feb-10 through 1-Dec-10 (ANSI-89). If your database uses the ANSI-92 wildcard characters, use single quotation marks (') instead of pound signs. Example: Between '2/2/2010' And '12/1/2010'.

Not "Germany"

Finds all records where the exact contents of the field are not exactly equal to "Germany." The criterion will return records that contain characters in addition to "Germany," such as "Germany (euro)" or "Europe (Germany)".

Not "T*"

Finds all records except those beginning with T. If your database uses the ANSI-92 wildcard character set, use the percent sign (%) instead of the asterisk (*).

Not "*t"

Finds all records that do not end with t. If your database uses the ANSI-92 wildcard character set, use the percent sign instead of the asterisk.

In(Canada, UK)

In a list, finds all records containing Canada or UK.

Like "[A-D]*"

In a Text field, finds all records that start with the letters A through D. If your database uses the ANSI-92 wildcard character set, use the percent sign instead of the asterisk.

Like "*ar*"

Finds all records that include the letter sequence "ar". If your database uses the ANSI-92 wildcard character set, use the percent sign instead of the asterisk.

Like "Maison Dewe?"

Finds all records that begin with "Maison" and that also contain a 5-letter second string in which the first 4 letters are "Dewe" and the last letter is unknown (indicated by a question mark). If your database uses the ANSI-92 wildcard character set, use the underscore (_) instead of the question mark.

#2/2/2010#

Finds all records for February 2, 2010. If your database uses the ANSI-92 wildcard character set, surround the date with single quotation marks instead of pound signs (#). Example: '2/2/2010'.

< Date() - 30

Returns all dates more than 30 days old.

Date()

Returns all records containing today's date.

Between Date() And DateAdd("M", 3, Date())

Returns all records between today's date and three months from today's date.

Is Null

Returns all records that contain a null (blank or undefined) value.

Is Not Null

Returns all records that contain a value.

""

Returns all records that contain a zero-length string. You use zero-length strings when you need to add a value to a required field, but you don't yet know what that value is. For example, a field may require a fax number, but some of your customers may not have fax machines. In that case, you enter a pair of double quotation marks with no space between them ("") instead of a number.

  1. On the Design tab, in the Results group, click Run.

  2. Verify that the query returned the records that you want to copy. If you need to add or remove fields from the query, switch back to Design view and add fields as described in the preceding step, or select the fields that you don't want and press Delete to remove them from the query.

Step 2: Convert the select query to an append query

Note:  The append query option is not available in an Access App.

  1. On the Home tab, in the View group, click View, and then click Design View.

  2. On the Design tab, in the Query Type group, click Append.

  3. Specify whether to append records to a table in the current database, or to a table in a different database.

    • To append records to a currently open table:    In the Append dialog box, click Current Database, select the destination table from the Table Name combo box, and then click OK.

    • To append records in another database   : In the Append dialog box, click Another Database, and in the File Name box, enter the location and name of the destination database. In the Table Name combo box, enter the name of the destination table, and then click OK.

Step 3: Choose the destination fields

The way that you choose destination fields depends on how you created your select query in Step 1.

If you...

Access...

Added all the fields from your source table or query

Adds all the fields in the destination table to the Append to row in the design grid

Added individual fields to the query or used expressions, and the field names in the source and destination tables match

Automatically adds the matching destination fields to the Append to row in the query

Added individual fields or used expressions, and any of the names in the source and destination tables don't match

Adds the matching fields, and leaves unmatched fields blank

If Access leaves fields blank, you can click a cell in the Append to row and select a destination field.

This figure illustrates how you click a cell in the Append to row and select a destination field.

A drop-down list in an Append to row

Note:  If you leave the destination field blank, the query will not append data to that field.

Step 4: Preview and run the append query

  1. To preview your changes, switch to Datasheet view.

    Tip:  To quickly switch views, right-click the tab at the top of the query, and then click the view that you want.

  2. Return to Design view, and then click Run Button image to append the records

    Note:  When running a query that returns a large amount of data, if you see an error message that says you will not be able to undo the query, try increasing the limit on the memory segment to 3MB.

Top of Page

Unblock your query

When you run an append query and it seems like nothing is happening, check the Access status bar for the following message:

This action or event has been blocked by Disabled Mode.

To fix this, click Enable Content in the Message Bar.

Note:  When you enable the append query, you also enable all other database content.

Top of Page

Some benefits of using an append query

When you use an append 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.

To create a new table from a selection of data, or to merge two tables into one new table, see how to save query results as a table.

Top of Page

Share Facebook Facebook Twitter Twitter Email Email

Was this information helpful?

Great! Any other feedback?

How can we improve it?

Thank you for your feedback!

×