Append Query in Microsoft Access
|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|
Create an append query
Follow these steps to create an append query:
The New Query dialog box appears.
The Show Table dialog box appears.
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.
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.
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.
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.
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
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.
ID – AutoNumber
FirstName – Text (50)
LastName – Text (50)
Company – Text (50)
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.
ID – AutoNumber
FirstName – Text (50)
LastName – Text (50)
Company – Text (50)
CustomerType – Text (50)
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.
The append query can be designed in the Design view of the Query window. Follow these steps:
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.
The destination table of an append query may contain an AutoNumber field. You have two choices:
Delete from tblCustomers;
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).
When you run an append query, you may first want to reset the AutoNumber field to 1. Follow these steps:
Removing the ID column prevents duplicate IDs from being appended to the destination table.
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:
FullName: [FirstName] & " " & [LastName]
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:
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.
Only the new records should be displayed (records with no matching first names).
When you run this query, only new records from tblCustomerLocal are appended to tblCustomers.
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:
This disables warning messages when running a macro.
This enables warnings (if not, warnings will not appear if the user runs any of the action queries).
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."
This error message can appear for one of the following violations:
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.
This error normally occurs when:
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.
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:
How to create a Microsoft Access Append Query:
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:
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:
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:
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 on 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:
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 on 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:
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:
We now need to Run this query, using the Run button to 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:
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.