What is an action query and when would you use one?
An action query is a query that makes changes to many records in just one operation. There are four types of action queries: delete, update, append, and make-table.
This topic provides examples of:
Delete query
Update query
Append query
Make-table query
Delete query
Deletes a group of records from one or more tables. For example, you could use a delete query to remove products that are discontinued or for which there are no orders. With delete queries, you always delete entire records, not just selected fields within records.
For information on creating delete queries, click 
.
Return to top
Update query
Makes global changes to a group of records in one or more tables. For example, you can raise prices by 10 percent for all dairy products, or you can raise salaries by 5 percent for the people within a certain job category. With an update query, you can change data in existing tables.
For information on creating update queries, click .
Return to top
Append query
Adds a group of records from one or more tables to the end of one or more tables. For example, suppose that you acquire some new customers and a database containing a table of information on those customers. To avoid typing all this information in, you'd like to append it to your Customers table. Append queries are also helpful for:
- Appending fields based on criteria. For example, you might want to append only the names and addresses of customers with outstanding orders.
- Appending records when some of the fields in one table don't exist in the other table. For example, in the Northwind sample database, the Customers table has 11 fields. Suppose that you want to append records from another table that has fields that match 9 of the 11 fields in the Customers table. An append query will append the data in the matching fields and ignore the others.
For information on creating append queries, click .
Return to top
Make-table query
Creates a new table from all or part of the data in one or more tables. Make-table queries are helpful for:
- Creating a table to export to other Microsoft Access databases. For example, you might want to create a table that contains several fields from your Employees table, and then export that table to a database used by your personnel department.
- Creating data access pages that display data from a specified point in time. For example, suppose you want to display a data access page on 15-May-96 that displays the first quarter's sales totals based on the data that was in the underlying tables as of 9:00 A.M. on 1-Apr-96. A data access page based on a query or SQL statement extracts the most up-to-date data from the tables (the data as of 15-May-96), rather than the records as of a specific date and time. To preserve the data exactly as it was at 9:00 A.M. on 1-Apr-96 , create a make-table query at that point in time to retrieve the records you need and store them in a new table. Then use this table, rather than a query, as the basis for the data access page.
- Making a backup copy of a table.
- Creating a history table that contains old records. For example, you could create a table that stores all your old orders before deleting them from your current Orders table.
- Improving performance of forms, reports, and data access pages based on multiple-table queries or SQL statements. For example, suppose you want to print multiple reports that are based on a five-table query that includes totals. You may be able to speed things up by first creating a make-table query that retrieves the records you need and stores them in one table. Then you can base the reports on this table or specify the table in an SQL statement as the record source for a form, report, or data access page, so you don't have to rerun the query for each report. However, the data in the table is frozen at the time you run the make-table query.
For information on creating make-table queries, click .
Return to top