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

How to create a Microsoft Access Append query with criteria:

A Microsoft Access append query can be used to add records to an existing database table. Prior to creating an append query, decide upon the two Microsoft Access tables that you would like to use. One table will contain the records you would like to add and the other table will be the table to which the records will be added. At the completion of this "how-to" will be a database table that has additional records added to it based upon specified criteria.

Select the two database tables that will be included in the query. Ensure that the data types for the information in the corresponding tables are the same. For example, if the information in the date field is being added to a table, ensure that the field in the corresponding table is formatted for dates.

1. Click on "Queries" in the "Objects" menu
2. Select "New" or "Create query in Design view".
   
   If "New" is selected, a "New Query" dialog box will open. In the dialog box, select "Design View" then click "Ok".
   
   If "Create query in Design view" is selected, the query design view will automatically appear. Regardless of which option selected, the "Show Table" dialog box will appear.
3. In the "Show Table" dialog, select the table that you would like to pull the records from.
4. Double-click on the table to add it to your design view.
5. Click "Close".
6. Go to the "Query" option in the menu. Go to "Append Query". It contains the plus sign and the exclamation point. The "Append Query" option can also be selected by clicking the down arrow beside the "Query Type" icon (overlapping spreadsheets).
7. The "Append" dialog box will open.
8. Using the "Table Name" drop down box, select the table to which the records will be added.
   
   
   The Append dialog, where you choose the table to append the records to
9. Click "Ok"
10. Click on and drag the field or fields that will be added to the table to the grid below the table.
11. Click in the "Append To" field.
12. Click on the down arrow to select the field that the information will be added to. The fields that are in the drop-down box are from the table that the data will be added to. Complete this step for all of the fields and information that will be added.
13. If records containing only certain criteria will be added, click in the "Criteria" field under the field that will be used to isolate the specific data.
    
    For example, if the field added is "Date" and the records to be added are between July 15 and August 31 then use the "Criteria" field to isolate these records. The information in the "Criteria" field for the above example would be "Between 7/1/2005 and 8/31/2005". Only data between and inclusive of these dates will be added.
14. To preview the data prior to adding it to the table, go to the spreadsheet icon in the upper-left corner.
15. Click on the icon.
16. The preview will show the information that will be added to the table based on the criteria selected. If the "Criteria" field was not used, the preview will include all records. If the desired records appear, the query can be run. If the desired records do not appear, adjust the criteria accordingly.
17. To return to the "query design" view, click the protractor icon in the upper-right corner.
18. To run the query, click the exclamation point in the icon menu.
19. A dialog box will appear advising of the number of rows that will be added and to ensure that the programmer wants to add the rows.
20. Click "Yes" to add the rows or "No" to change the query.
21. To save the query, click the "disk" icon in the icon menu.
22. Name the query.
23. Click "x" or "File" then "Close" to close the query window and return to the main database window.

An append query is a very efficient and quick way to add a group of records to a table. Other helpful expressions to use when isolating criteria are:

Readers of this article may also like to check out other articles on this subject:

• Creating Action Queries - Update, Append and Delete Queries
• Creating an Append Query in Microsoft Access
• Creating a Delete Query in Microsoft Access
• Automating Archiving Records in Microsoft Access, using Append and Delete Queries