About action queries (MDB)

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.

ShowDelete 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 (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 Icon image, it is a one-to-many relationship. If both ends are marked with a 1, it's a one-to-one relationship.

ShowImportant considerations when using a delete query

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

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

ShowMake-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:

 
 
Applies to:
Access 2003