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.
- Click on "Queries" in the "Objects" menu
- 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
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.
- In the "Show Table" dialog, select the table that you
would like to pull the records from.
- Double-click on the table to add it to your design view.
- Click "Close".
- 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
- The "Append" dialog box will open.
- 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
- Click "Ok"
- Click on and drag the field or fields that will be added to the
table to the grid below the table.
- Click in the "Append To" field.
- 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.
- 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.
- To preview the data prior to adding it to the table, go to the
spreadsheet icon in the upper-left corner.
- Click on the icon.
- 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.
- To return to the "query design" view, click the protractor
icon in the upper-right corner.
- To run the query, click the exclamation point in the icon menu.
- 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.
- Click "Yes" to add the rows or "No" to change
- To save the query, click the "disk" icon in the icon
- Name the query.
- 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
Readers of this article may also like to check out other articles
on this subject: