This tip on Append Query is suitable for all versions
of Microsoft Access the differences
between the versions are included where needed.
Note: Access 2003 refers to Mircosoft Access 2003 or
earlier and Access 2007 refers to Microsoft Access 2007 or later.
To build a query that will append your data from one
table to another table in Microsoft Access do the following:
Open a NEW query in Design view.
In Access 2003, you do this by clicking on the NEW
button when the QUERY object is selected and then selecting Design View from
the options provided.
In Access 2007, you do this by clicking on the 'Create
tab' and then selecting the 'Query Design' option
The SHOW TABLE window will open.
Choose the table you wish to append the data from,
i.e. the table that currently has the data.
Close the SHOW TABLE window.
The Design view of the query will be displayed.
In Access 2003; on the menu bar click on QUERY, then
select 'APPEND QUERY' from the drop down list.
In Access 2007; click on 'APPEND QUERY' on the ribbon.
The APPEND window will open.
From the Drop down list, select the table you wish to
append the data to.
Close the APPEND window.
Next – DOUBLE CLICK on each of the fields you wish to
append to the new linked table. If the field in the old table has the same name
as the field in the new table, the field name will automatically appear in the
‘Append To’ row, otherwise you will have to choose it from the drop down list.
When all fields have been added to the design grid,
close and save the query.
You will see your new QUERY displayed in the QUERY
list. Note it will have a GREEN CROSS and an EXCLAMATION MARK next to it, this
identifies it as an APPEND QUERY.
DO NOT RUN THIS QUERY YET………….
At the moment it will append all your data. What you
need to decide is what data you wish to append, i.e. you may wish to add some
criteria to the query. For the purpose of this exercise I am going to use the
example of all data that is older than 1 year.
Therefore, re-open your QUERY in Design view. Then, in
the field which has the appropriate date field, in the CRITERIA column, type
'less than' Date()-365
Note: Replace 'less than' with the actual symbol.
This formula will append all data that is less than
today’s date – 365 days (1 year).
When you are in Design view, click on the VIEW button.
This does not RUN the query, but will display all the data that will be
appended. Check to see if this appears to be correct.
When you are happy with the result, close and save the
You can run the QUERY now if you like, just
double-click on it. You will be prompted with some warnings about the fact you
are about to run an APPEND QUERY - just click on OK.
After the QUERY has been run, check to see if the data
is what you expected, in the table it has been appended to.