How to Create an Append Query in Access
- Create a SELECT query to determine the records that will be appended. Apply any required query criteria
- In the query design view, click on the drop-down arrow to the right of the Query Type button and choose Append Query
- 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
- If needed, modify the query further so that the correct fields will be appended with the desired new data
- Click on the Run (!) button to run the action query
- When informed of the number of records to be appended in the Microsoft Access dialog box, click Yes
- 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:
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.