<< Back

Append Query in Microsoft Access

 

Adding rows by using 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
   
Another Article on Creating an Append Query
 

Adding rows by using an append query

 

The following information applies to:

Microsoft Office Access 2003
Microsoft Access 2000 and 2002

 

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

   
Top of Page


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.

   
Top of Page

 

Append data to an AutoNumber field

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

  1. 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.
  2. 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:
    3. SELECT ID, FirstName, LastName, Company, "Local"
    4. as [CustomerType] FROM tblCustomerLocal
    5. UNION SELECT ID, FirstName, LastName, Company, "International"
    6. as [CustomerType] FROM tblCustomerInternational
    7. UNION SELECT ID, FirstName, LastName, Company, "Discontinued"
    8. 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.

     

       
    Top of Page

     

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.
   
Top of Page

 

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.
   
Top of Page

 

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.

   
Top of Page

 

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.
   
Top of Page

 

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:

  1. 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.
  2. 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.
  3. 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:

  1. One of the fields in the query is spelled incorrectly.
  2. The spelling of the field name in the source table has changed.
  3. 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

 

Creating an Append Query in Microsoft Access

 

A Microsoft Access append query adds (appends) records from the database table that you are using to another database table. If you are wanting to append records in a database, the table that you want to append records to must exist. Records can be appended to a table in the current database that you are working in, or into another Microsoft Access database.

Microsoft Access append queries are good for adding data to a different table where the data is based upon a selection criteria. However, append queries are not always the most efficient way of adding records to another database. If you need to add all records and fields from one table to another table, the append query is not the best way to do it. Using Copy and Paste options in this case would be the best solution.

When you decide to work with an append query, you should ensure that you are aware of:

 

  • If you are attempting to append records to another (external) database, you will need to know the name and location of the database.
  • If there is a Primary Key field in the database table that you are appending records to, the records that you are adding will not be allowed to contain either a duplicate Primary Key value or a Null value. If this happens, Microsoft Access will not append the data and you will not be warned.
  • If you choose all of the fields using the asterisk (*) in the Query By Example (QBE) window in Microsoft Access, you can not also use individual fields in the same table. This will be assumed that you are attempting to add the field contents twice, therefore the data will not be appended.

 

How to create a Microsoft Access Append Query:

 

  1. Create a SELECT query to determine the records that will be appended. Apply any required query criteria
  2. In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Append Query
  3. In the Append dialog box, select the table that you want to Append To from the list of tables in the current database, or select to append this to another database and either Browse to this, or enter the full file path including the database name
  4. If needed, modify the query further so that the correct fields will be appended with the desired new data
  5. Click on the Run (!) button to run the action query
  6. When informed of the number of records to be appended in the Microsoft Access dialog box, click Yes
  7. Close the query, saving if required

 

Now consider the following scenario example, that will illustrate the use of a Microsoft Access append query:

Your company runs training courses, with students signing up for these courses. Student membership lasts for 2 years. All of this information relating to their details is stored in your StudentInformation table. You would like to remove any records from this table that are not of current students, so you will archive records by initially appending the data from your StudentInformation table into another table (ExpiredStudents) and then deleting these records from the StudentInformation table.

The original StudentInformation table, containing 52 records, would look like the following:

 

The original Student Information table, containing 52 student records

 

The original Student Information table, containing 52 student records

 

As you will see, the above table contains contact information relating to the student members. You will see from the fields included, that there is a field detailing when the Student Enrolled on the course (dtmEnrolled). This field also is displaying records older than two years old.

 

In our scenario, we only wish to keep records in the table where the student is still currently active. With the courses being a two year duration, we only want records that are greater than or equal to todays date minus two years.

To allow us to append records to an archive table, we must first have the archive table created, whether it be in the active database or in another database. The table should ideally have exactly the same structure as the original table and contain the same fieldnames and data type/sizes.

 

In our example we have an archived table created, named tblExpiredStudents, which will hold the historical records of students whose two years have expired.

We now need to create the Append Query that will copy the records from the original table to the historical archive table. In a new SELECT query, in design view we will add all of the fields from the StudentInformation table as shown:

The initial design of the query, prior to changing to the append query type

 

You will see that we have added a criteria to the dtmEnrolled field, as specified we want to remove any records that are older than two years from today's date. The criteria that is applied is:

 

<=DateAdd("yyyy",-2,Date())

 

This will display only records that are older than 2 years from todays date.

Before we change the query type to an Append query, we should check the results that this displays. We can do this by clicking on the Datasheet button Datasheet buttonon the toolbar.

 

If we check the datasheet displaying the results of running the query with the DateAdd criteria applied, we will now see that the record count is displaying only 20 records. This is showing 20 records that need to be removed due to them being greater than 2 years old:

 

The query datasheet results, with the DateAdd criteria applied.

 

Once we are happy with the results that are going to be appended to the archive table, we can then change the query type to an append query. To do this we need to go back into the Microsoft Access query design view.

Once in design view, we change the query type using the Query Type button The Query Type buttonon the toolbar. From the query type drop-down list, change the query type to an Append Query. You will then be presented with the Append dialog box, where you can then choose the table that you want to append the data to. From the list, choose the table if this is in the same database or enter the file location and database name for an external database table:

 

The append dialog, where you can choose the table to append to.

 

Choose the table and click on OK.

 

You will now see the the query design includes a new row labeled Append To, which details the fields in the new table (selected previously) that the data will be appended to. You will see below this row in the query design:

 

The append query design, showing the Append To row in the design grid.

We now need to Run this query, using the Run button The Run button to run the action queryto append the data to the other database table. The warning dialog box indicates the number of records that will be appended, click Yes to accept this:

The warning message, warning that you are going to append records to the new table.

 

This will now have appended (copied) the records to the archive table.

Ideally, as you are archiving the records, you will now delete these records from the original table.

 

   
Top of Page