Append Query
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 the following:
'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 query.
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.
Return from 'Append
Query' to Simply Access Home Page
or
Return to Microsoft Access Queries