Resources

Online Shop

Info

Microsoft Access Append Queries

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:

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:

<=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.