databasedev.co.uk - database solutions and downloads for microsoft access

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:

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:

<=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 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. This is done by Creating a Delete Query in Microsoft Access