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.
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 (one-to-one relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of one, and only one, record in the related table.), or from multiple tables in a one-to-many relationship (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.), if cascading deletes (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) 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 (Design view: A view that shows the design of these database objects: tables, queries, forms, reports, and macros. In Design view, you can create new database objects and modify the design of existing objects.) by looking at the joins (join: An association between a field in one table or query and a field of the same data type in another table or query. Joins tell the program how data is related. Records that don't match may be included or excluded, depending on the type of join.) 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 (delete query: A query (SQL statement) that removes rows matching the criteria that you specify from one or more tables.), 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 (Datasheet view: A view that displays data from a table, form, query, view, or stored procedure in a row-and-column format. In Datasheet view, you can edit fields, add and delete data, and search for data.).
- 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 (one-to-many relationship: An association between two tables in which the primary key value of each record in the primary table corresponds to the value in the matching field or fields of many records in the related table.), and you've enabled cascading deletes (cascading delete: For relationships that enforce referential integrity between tables, the deletion of all related records in the related table or tables when a record in the primary table is deleted.) 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.
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.
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.
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 (Microsoft Access database: A collection of data and objects (such as tables, queries, or forms) that is related to a particular topic or purpose.). 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 (data access page: A Web page, published from Access, that has a connection to a database. In a data access page, you can view, add to, edit, and manipulate the data stored in the database. A page can also include data from other sources, such as Excel.) 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 (SQL string/statement: An expression that defines an SQL command, such as SELECT, UPDATE, or DELETE, and includes clauses such as WHERE and ORDER BY. SQL strings/statements are typically used in queries and in aggregate functions.) 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 (record source: The underlying source of data for a form, report, or data access page. In an Access database, it could be a table, query, or SQL statement. In an Access project, it could be a table, view, SQL statement, or stored procedure.) 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.