Article ID: 304448
Article Last Modified on 1/31/2007
APPLIES TO
- Microsoft Access 97 Standard Edition
This article was previously published under Q304448
Novice: Requires knowledge of the user interface on single-user computers.
This article applies only to a Microsoft Access database (.mdb).
SUMMARY
Action queries are used when you want to perform tasks such as inserting, updating, or deleting records. You can convert an existing select query to an action query by changing its query type. This article describes the different types of action queries and shows you how to change a select query to an action query.
MORE INFORMATION
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 can use a delete query to remove products that are discontinued or for which there are no orders.
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 can 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, because a query cannot delete records from the primary table and the related tables at the same time.
For additional information about relationships and designing databases, click the article number below to view the article in the Microsoft Knowledge Base:
288949 Where to Find Information About Designing a Database in Access
Important Considerations When Using a Delete Query
- Once you delete records by using a delete query, you cannot 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 Datasheet on the View menu.
- 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 may delete records in related tables, even if they are not included in the query. This can happen when your query contains only the table that is on the "one" side of a one-to-many relationship, and you have enabled cascading deletes for that relationship. When you delete records from the "one" table, you will also delete records from the "many" table.
For example, consider a relationship between a Customers and Orders table with cascade deletes enabled. Deleting a record from the Customers table would delete the related orders in the Orders 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.
For more information about creating delete queries, click Microsoft Access Help on the Help menu, type create a delete query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
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.
For more information about creating update queries, click Microsoft Access Help on the Help menu, type create an update query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
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, you would like to append it to your Customers table. Append queries are also helpful for:
- Appending fields based on criteria. For example, you may want to append only the names and addresses of customers with outstanding orders.
- Appending records when some of the fields in one table do not 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 more information about creating append queries, click Microsoft Access Help on the Help menu, type create an append query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
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 may 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.
- 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.
For more information about creating make-table queries, click Microsoft Access Help on the Help menu, type create a make-table query in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Converting a Select Query to an Action Query
- Start Microsoft Access, and then open the sample database Northwind.mdb.
- On the View menu, click Database Objects, and then click Queries.
- Click the Invoices query, and then click Design.
- On the Query menu, click Update Query to change this select query to an update query.
- In the UnitPrice column for the Order Details table in the query design grid, type the following expression in the Update To row. This expression will increase the unit price for orders placed by 10 percent.
[Order Details].[UnitPrice] * 1.10
- In the ShipCountry column, type USA in the Criteria row.
- On the Query menu, click Run to update the unit price for orders shipped to the USA by 10 percent.
- Close the Invoices query without saving it.
Changes to the Query Design Grid When Changing Query Type
After you change the query type from a select query to an action query, the query design grid will change as follows.
- Update query
A row is added in the grid at the bottom of the screen titled "Update To." Enter an expression in this row to update a group of records based on the expression.
- Append query
You are prompted for the name of the table to append records to, and you should see a row titled "Append To." This is the name of the field in the table that you are appending records to. Microsoft Access automatically fills in field names that match between the two tables.
- Delete query
A row is added to the query design grid at the bottom titled "Delete." To delete a group of records based on criteria, change the Delete row to Where and enter the expression that the query should use as criteria in the Criteria row.
- Make-Table Query
You are prompted for the name of a new table to create. You can also create this table in a different database. The table will be created with the field names and data types from the existing table.
Additional query words: inf
Keywords: kbhowto KB304448
