Chapter 18: Advanced Access Query Techniques
671
Understanding Action Queries
So far, the queries I’ve covered are typical select queries. As the name implies, a select query selects
records from a single table, or a number of tables, and arranges the selected records as a recordset.
The recordset is then used by the application, most often as a data source for a form or report.But, in most business situations, there is often a need to perform an action on a group of records,
such as deleting inactive customers from the Customers table, or updating the retail price in every
record in the Products table.An action query is a type of query that performs an action (update, delete, and so on) against a
group of records. The records are chosen selected as in any typical select query, and then the
action is applied to the recordset.Types of action queries
Action queries can’t be created using the Access Query Wizard. An action query starts out as a
select query that returns the set of desired records. When you’re satisfied that the records are
selected correctly, you convert the select query to an action query. Running the action query per-
forms the operation on the records. Here are the four different types of Access action queries:l (^) Update Query: Updates data in an existing table. Update queries perform bulk mainte-
nance on tables, such as adjusting product prices or customer discounts.
l (^) Make Table Query: Makes a new database table from the selected records. For example,
you may want to create an archive table of all inactive customer records.
l (^) Delete Query: Deletes data in a specified table that matches a set of criteria (a filter).
Extending the previous example, when the inactive customers have been archived as a
separate table, you might use a delete query to remove the inactive customer records from
the Customers table.
l (^) Append Query: Adds new records to an existing table. For example, one of your former
customers, whom you haven’t heard from in several years, wants to make a purchase. You
might use an append query to retrieve the customer’s contact information and add it to
the active Customers table.
Tip
You can quickly identify action queries in the Database window by the special exclamation point icon situation
near the query name in the Navigation Pane. The icons next to the action queries in the Navigation Pane are
miniatures of the action query icons as they appear in the ribbon (see the query icons in Figure 18.9).
Creating action queries
Creating an action query usually begins by creating a select query. You specify the fields for the
query, add the query’s criteria (if needed), and review the selected records by viewing the query as