About action queries

An action query is a query that makes changes to or moves many records in just one operation. There are four types of action queries: delete, update, append, and make-table.

Delete query

A 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.

You can use a delete query to delete records from a single table, from multiple tables in a one-to-one relationship, or from multiple tables in a one-to-many relationship, if cascading deletes are enabled. For example, you could use a delete query to delete all customers from Ireland and all their orders. However, if you need to include the "many" table along with the "one" table in order to add criteria, you must run the query twice, since a query cannot delete records from the primary table and the related tables at the same time.

You can distinguish a one-to-many relationship in query Design view by looking at the joins between tables. If one end of a join is marked with the infinity symbol , it is a one-to-many relationship. If both ends are marked with a 1, it's a one-to-one relationship.

Important considerations when using a delete query

• Once you delete records using a delete query, you can't undo the operation. Therefore, you should preview the data that the query selected for deletion before you run the query. You can do this by clicking View on the toolbar, and viewing the query in Datasheet view.
• You should maintain backup copies of your data at all times. If you delete the wrong records, you can retrieve them from your backup copies.
• In some cases, running a delete query might delete records in related tables, even if they're not included in the query. This can happen when your query contains only the table that's on the "one" side of a one-to-many relationship, and you've enabled cascading deletes for that relationship. When you delete records from the "one" table, you'll also delete records from the "many" table.
• When a delete query contains more than one table, such as a query that deletes duplicate records from one of the tables, the queryÆs UniqueRecords property must be set to Yes.

Update query

An 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.

Append query

An 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.

Make-table query

A 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 forms, reports, or 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 automatically by using a macro or code.
• 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 might 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 page, so you don't have to rerun the query each time you open the form, report, or page. However, the data in the table is frozen at the time you run the make-table query.