Adding rows by using an append query

Applies to
Microsoft Office Access 2003
Microsoft Access 2000 and 2002


Most Valuable Professional logo

This article was written by Naresh Nichani, a Microsoft Access MVP based in Chennai, India. Naresh runs a software development firm that specializes in Visual Basic development. Along with Brian Reilly, a Microsoft PowerPoint MVP, Naresh is currently involved in building trade show lead management software.

This article introduces append queries and addresses some key issues that you might encounter when using them.

In this article

Append queries

Create an append query

Append data from two or more similar tables or queries into a single table

Append data to an AutoNumber field

Reset an AutoNumber field to 1

Append data from two fields into one

Avoid duplicate records when appending data

Suppress a dialog box for action queries after the testing phase

Common errors when you run an append query

Append queries

An append query adds a group of records from one or more tables to the end of one or more tables. For example, you might have data on new customers stored in a linked table and need to add this data to another table. You could use an append query. An append query can also do the following:

  • Append data from more than one table to a single table.
  • Append data based on matching fields. For example, you may want to append a customer only if that customer name is not already present in the customers table.
  • Append a calculated field. For example, you can create a full name calculated field by combining the text from the first name and last name fields. You can then append the full name values to a full name field in a table.

Create an append query

Follow these steps to create an append query:

  1. In the Database window, click Queries and then click New.

The New Query dialog box appears.

New Query dialog box

  1. Click Design View and then click OK.

The Show Table dialog box appears.

Show Table dialog box

  1. Select the source table from which you want to append data. For example, to append data from table tblCustomerLocal, select tblCustomerLocal in the list, click Add, and then click Close.
  2. Change the query type to Append Query by clicking Append Query on the Query menu.

Note that the default query type is Select Query. If the Append Query menu choice is not visible on the Query menu, click the arrow at the bottom to expand the menu.

Query menu with Append Query

  1. Select the table to which you want to append data (the destination table). This table must already exist in the current database or in another database. Select a table in the Table Name drop-down list and click OK — in this case, click tblCustomers.

Append dialog box

  1. You are now in the Query Designer. Select the fields you want to append from the source table.

To select a field, double-click the field name in the field list, and it will be added to the list of fields to append. In the following illustration, the FirstName, LastName, and Company fields are selected.

Append query in Query Designer

A new row is added to the Query Designer when you are building an append query — this row is labeled Append To. If you click a cell in this row, a drop-down list of the fields in the destination table appears. The cells in the Append To row let you select which field in the destination table you want the data appended to. If the source table and destination table contain a field with the same name, Access automatically defaults to that field name. In this case, both the source table and destination table have fields named FirstName, LastName, and Company. As a result, Access automatically selects these fields in the Append To drop-down list. The drop-down list you use to select a field in the destination table looks like the following illustration.

Append To box in Query Designer

  1. Click Run on the Query menu, or click the Run button on the toolbar.

A message appears telling you how many records will be appended. If you click Yes, the records are appended to the destination table. The Access message looks something like the following illustration.

Confirmation message for append query

Note that you cannot run an append query by clicking the Datasheet view button when the query is open in Design view (this is the way that you typically run a select query). When you click Datasheet view, Access displays only the records that will be appended and does not actually append the records to the destination table. Only clicking the Run button actually executes the append query.

Append data from two or more similar tables or queries into a single table

Normally, an append query has a single source table and a single destination table. However, suppose you want to merge data from two or more tables into one. In the following procedure, you want to append data from three similar tables into a new table.

  1. Create a table named tblCustomerLocal in a new database with these fields and data types:

ID – AutoNumber

FirstName – Text (50)

LastName – Text (50)

Company – Text (50)

  1. Create two more tables with the same structure as tblCustomerLocal. Name them tblCustomerInternational and tblCustomerDiscontinued.
  2. Populate these three tables with some test data.
  3. Now we will append the data from these three tables to a new table named tblCustomers:

As part of the append query, you will also create a new field called CustomerType, which may contain the value Local, International, or Discontinued, depending on the source table.

  1. Create a table named tblCustomers that contains these fields and data types:

ID – AutoNumber

FirstName – Text (50)

LastName – Text (50)

Company – Text (50)

CustomerType – Text (50)

  1. Create a union query named qryUnionSelectCustomers that will merge the data from the three customer tables.

A union query combines data from two or more select queries. This type of query can only be created in SQL view and not in Design view. To create a union query, click the Query tab in the Database window and then click New. Do not select any tables in the Show Table dialog box. On the View menu, click SQL View and type the following SQL text in the Select Query window:

SELECT FirstName, LastName, Company, "Local" 
as [CustomerType] FROM tblCustomerLocal
UNION SELECT FirstName, LastName, Company, "International" 
as [CustomerType] FROM tblCustomerInternational
UNION SELECT FirstName, LastName, Company, "Discontinued" 
as [CustomerType] FROM tblCustomerDiscontinued;

This query creates a new field named CustomerType.

  1. Now, create an append query to append the results of the union query into the tblCustomers table.

The append query can be designed in the Design view of the Query window. Follow these steps:

  1. Create a new query and select the query qryUnionSelectCustomers as the source table for the query.
  2. Change the query type to Append (on the Query menu, click Append Query), and select the table tblCustomers as the table to append the data to (the destination table).
  3. Add the fields FirstName, LastName, Company, and CustomerType to the append query in the Query Designer. Since fields with the same name already exist in the tblCustomers table, they are automatically selected in the Append To row.

Append query named qryAppendCustomers in Query Designer

  1. Save the query as qryAppendCustomers.
  2. Click the Run button to run the query.
  3. Open the tblCustomers table, and you see that data from the three tables has been appended into a single table.

You can also check the CustomerType field in the tblCustomers table to see that a value has been inserted into that field for each record, even though the field did not exist in any of the three source tables.

Append data to an AutoNumber field

The destination table of an append query may contain an AutoNumber field. You have two choices:

  • Let Access generate the AutoNumber field automatically in the destination table.    To do this, do not include the AutoNumber field in the append query. If you do not include the AutoNumber field as part of the append query, it is generated automatically in the destination table. In the preceding example, the ID column is an AutoNumber field and is not part of the append query. As a result, the ID column is generated automatically in the tblCustomers table.
  • Keep the AutoNumber value from the original table.    To do this, include the AutoNumber column as part of the append query. If you include an AutoNumber field as part of an append query, Access will not generate the AutoNumber when the query is adding the data to the table, and will instead use the value from the source table or tables. It is important to note that if you are appending data to an AutoNumber field, you need to make sure the data is unique in all the source tables that you are appending from. To test this, modify the preceding example. Follow these steps:
    1. Delete all records in the table tblCustomers. To do this, create a new delete query that contains this SQL:
Delete from tblCustomers;
  1. Save this query as qryDeleteCustomers and then run the query. Make sure all records were deleted from the table tblCustomers.
  2. Add the ID field to the union query qryUnionSelectCustomers. The SQL for this union query will now look like this, with the addition of the ID field:
SELECT ID, FirstName, LastName, Company, "Local" 
as [CustomerType] FROM tblCustomerLocal
UNION SELECT ID, FirstName, LastName, Company, "International" 
as [CustomerType] FROM tblCustomerInternational
UNION SELECT ID, FirstName, LastName, Company, "Discontinued" 
as [CustomerType] FROM tblCustomerDiscontinued; 

Now add the ID column as a new column to the append query named qryAppendCustomers — the append query now looks like this in Design view (with the new ID column added).

Append query qryAppendCustomers with ID column added

  1. Click Run to run the query. If your source tables contain duplicate ID values (for example, records in the tblCustomerLocal, tblCustomerInternational, or tblCustomerDiscontinued table have the same ID), you see a message like the following.

Microsoft Office Access can't append allthe records in the append query

  1. Click Yes to run the query. Note that the append query fails on some records because the ID values are not unique across the source tables.

Reset an AutoNumber field to 1

When you run an append query, you may first want to reset the AutoNumber field to 1. Follow these steps:

  1. Delete all records in the destination table. From the preceding example, run the query qryDeleteCustomers.
  2. Open the append query named qryAppendCustomers in Design view, delete the ID column, and save the query.

Removing the ID column prevents duplicate IDs from being appended to the destination table.

  1. Run a compact and repair operation on the database. On the Tools menu, click Database Utilities and then click Compact and Repair Database.
  2. Now run the append query and note that the AutoNumber field starts from 1.

Append data from two fields into one

Sometimes, you need to append data from two fields into one. For example, suppose you need to combine the values from the FirstName and LastName fields and append them to a field named FullName. To do this, follow these steps:

  1. Create a new field named FullName in the table tblCustomers with a data type of Text (100).
  2. Run the query qryDeleteCustomers to delete all records from the table tblCustomers.
  3. Open the append query named qryAppendCustomers in Design view and add a new column that contains this expression:
FullName: [FirstName] & " " & [LastName]
  1. In the Append To row, select FullName from the drop-down list.

FullName expression in Query Designer

  1. Save and then run the query.

Avoid duplicate records when appending data

When you run an append query, you want to avoid appending duplicate records. For example, you may want to append records from the table tblCustomerLocal to the table tblCustomers. After you append some records, you should not append the same records again (only new records should be appended).

You need to create a find unmatched query — a query that finds only the records in the source table that do not have matching records in the destination table.

To do this, follow these steps:

  1. Run the query qryDeleteCustomers to delete all records from the table tblCustomers.
  2. Create a new query, and in the New Query dialog box, click Find Unmatched Query Wizard.

New Query dialog box

  1. Select the table from which you want to select the records — tblCustomerLocal.
  2. Select the table that contains no records — tblCustomers.

Find Unmatched Query Wizard: Which table or query contains the related records?

  1. Now you need to match the two tables on a field. Select FirstName in the tblCustomerLocal field list and FirstName in the tblCustomers field list, and then click the «=» button in the wizard.

Running the Find Unmatched Query Wizard ensures that only records with a value in the FirstName field in tblCustomerLocal and no matching value in the FirstName field in tblCustomers show up in the query result. Suppose "Harry" exists as a value in the FirstName field in tblCustomerLocal but not in the FirstName field in tblCustomers. The record containing "Harry" from the tblCustomerLocal table would show up in the query result as a record that you want to append.

Find Unmatched Query Wizard: What piece of information is in both tables?

  1. Click Next and select the fields to display. Select FirstName, LastName, and Company.

Find Unmatched Query Wizard: What fields do you want to see in the query results?

  1. Click Next and save the query as tblCustomerLocal Without Matching tblCustomers.
  2. Click Finish.
  3. Now test this query by adding some records to the source table (tblCustomerLocal), and append them to the destination table (tblCustomers) by running the append query (qryAppendCustomers).
  4. Then add some more new records to the table tblCustomerLocal and run the find unmatched query (tblCustomerLocal Without Matching tblCustomers).

Only the new records should be displayed (records with no matching first names).

  1. Now create an append query named qryAppendNewLocalCustomers that appends records to the table tblCustomers. Do this by creating a new query that uses the query named tblCustomerLocal Without Matching tblCustomers as the source table and the table named tblCustomers as the destination table. The query looks like the following illustration in the Query Designer.

Append query qryAppendNewLocalCustomers in Query Designer

When you run this query, only new records from tblCustomerLocal are appended to tblCustomers.

Suppress a dialog box for action queries after the testing phase

After an append query has been well-tested, you may want to call it by running a macro that displays no warnings. To do this, follow these steps:

  1. Create a new macro.
  2. In the first row of the macro, select Set Warnings, and in the Action Arguments section, set Warnings On to No.

This disables warning messages when running a macro.

macAppend macro: SetWarnings action to No

  1. In the next row, select Open Query, and in the Action Arguments section, select the name of the append query — in this case, qryAppendNewLocalCustomers.

macAppend Macro: OpenQuery action arguments

  1. In the third row, again select Set Warnings and set Warnings On to Yes.

This enables warnings (if not, warnings will not appear if the user runs any of the action queries).

macAppend macro: SetWarnings action to Yes

  1. Save and run the macro to run an append query that displays no warnings.

Common errors when you run an append query

When you run an append query, you may receive an error message that says, "Microsoft Office Access can't append all the records in the append query."

Error message

This error message can appear for one of the following violations:

  • Lock Violation — Is the destination table locked? The destination table could be open in Design view or open by another user on the network. This would cause a lock violation and make the append query fail.
  • Key Violation — The destination table may have a primary key or an index with the No Duplicates property set to Yes. Check the design of the destination table for the primary key and any index with No Duplicates set to Yes. Check the data you are appending to make sure there are no key violations in the primary key or any index with No Duplicates set to Yes.
  • Validation Rule Violation — Check the design of the destination table for any required fields. If any field is required and you do not append data to this field, you get this error. Also, check the destination table for any Text fields with Allow Zero Length set to No. If you are appending blank data to a Text field that has Allow Zero Length set to No, you can get this error. Also, check for validation rules for the fields. For example, you may have the following validation rule for the Quantity field: >=10  — in this case, you cannot append records with a quantity less than 10.

Another common problem is when the Enter Parameter Value dialog box appears. When you run an append query, you may see a dialog box like this.

Enter Parameter Value dialog box: CustomerType1

This error normally occurs when:

  • One of the fields in the query is spelled incorrectly.
  • The spelling of the field name in the source table has changed.
  • The field has been deleted in the source table.

For example, in our query, suppose that the CustomerType field was misspelled as CustomerType1 — in that case, Access prompts you for the value of CustomerType1. Access expects you to pass it a parameter (since it cannot find the field in the source table), and whatever value you type in this dialog box will be appended to the query.

Top of Page Top of Page

 
 
Applies to:
Access 2003