Getting Acquainted with Action Queries

Action queries create new tables or modify the data in existing tables. Access offers the following four types of action queries:

• Make-table queries create new tables from the data contained in query result sets. One of the most common applications for make-table queries is to create tables that you can export to other applications or that summarize data from other tables. A make-table query provides a convenient way to copy a table to another database. In some cases, you can use make-table queries to speed the generation of multiple forms and reports based on a single, complex query.

• Append queries add new records to tables from the query's result set.

• Delete queries delete records from tables that correspond to the rows of the query result set.

• Update queries change the existing values of fields of table records that correspond to rows of the query result set.

By default, new queries you create are select queries. After opening a new or existing query in Design view, you can change its type to one of the four action queries by making a selection from the Query menu.

Note

Access data projects (ADP) offer the same Query menu choices for action queries when you create a new stored-procedure query. Stored procedures have an additional type of append query, called an Append Values procedure, which lets you add a new record with values you type in the da Vinci Query Design grid. SQL Server functions and views don't support make-table queries.

This chapter shows you how to create each of the four types of Jet action queries and how to use Access's cascading deletions and cascading updates of related records. Cascading deletions and cascading updates are covered here because these referential-integrity features are related to delete and update action queries, respectively.

Tip

Always make a backup copy of a table that you intend to modify with an action query. Changes made to table data with action queries are permanent; an error can render a table useless. Invalid changes made to a table with an action query containing a design error often are difficult to detect.