|
To build an append query in Microsoft Access do the following:
Open a NEW query in Design view. You do this by clicking on the NEW button when the QUERY object is selected and then selecting Design View from the options provided.
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.
On the menu bar click on QUERY, then select APPEND QUERY from the drop down list.
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 APPEND 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 APPEND QUERY in Design view. Then, in the field which has the appropriate date field, in the CRITERIA column, type the following:
“<” Date()-365
Note: Remove the talking marks, I need them to show the less than sign in html.
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 APPEND 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 APPEND QUERY has been run, check to see if the data is what you expected.
Do you like this tip? Subscribe to my weekly Newsletter to receive tips
weekly via email.
Click Here to subscribe.
|
