HOW TO: Convert a Select Query to an Action Query in Access 2000 (304355)

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.

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.

back to the top

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: 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 querys UniqueRecords property must be set to Yes.For additional information about setting the UniqueRecords property in a delete query, click the article number below to view the article in the Microsoft Knowledge Base:

  240098 "Could Not Delete from Specified Tables" Error Message in Delete Query

For additional information about creating delete queries, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:
back to the top

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 additional information about creating update queries, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:
back to the top

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 additional information about creating append queries, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:
back to the top

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 additional information about creating make-table queries, click the "Working with Queries" topic on the Microsoft Access 2000 Help page at the following Microsoft Web site:
back to the top

Converting a Select Query to an Action Query

1. Start Microsoft Access and open the Northwind.mdb sample database.
2. On the View menu, click Database Objects, and then click Queries.
3. Click the Invoices query, and then click Design.
4. On the Query menu, click Update Query to change this select query to an update query.
5. 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

6. In the ShipCountry column, type USA in the Criteria row.
7. On the Query menu, click Run to update the unit price for orders shipped to the USA by 10 percent.
8. Close the Invoices query without saving it.

back to the top

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.

back to the top