| If a select query were a film, the credits at the end would announce with complete confidence: "No underlying values have been harmed in the making of this query." If it were an action query, however, the People for the Ethical Treatment of Data would want to keep a close eye on the production. Until now, you've been working with queries that retrieve records from tables. You have manipulated and calculated the records in various ways. But the values stored in the underlying tables remain untouched. The action query, as its name implies, is a "do something" query. It is indeed a query, and it's created on the design grid. But instead of simply retrieving records, the action query can have a powerful impact on your tables. I next discuss four types of action queries: make table, delete, append, and update. Warning Label Before I go any further, let me shout an obvious but necessary warning: Because action queries can change thousands of records at once, they have the power to decimate your database. There is no Undo command for an action query. Don't execute any action queries until you make sure all of your records are safely backed up. On the other hand, I don't want to scare you off using them, either. Action queries can be helpful when you need to change many records quickly. To take a look at out how they work, I'm going to ask you to make a copy of the sample Northwind.mdb database. (It's in a folder named something like Program Files\Microsoft Office\OFFICE11\ SAMPLES, or use Windows Search to find the file.) The Northwind database has lots of records, so it will work better than the relatively sparse Nifty Lions. The examples will be mostly artificial and pointless, but they will show you how the various action queries work. Make Table Occasionally, you'll want to create a new table based on an existing table that you can use in the same or (more often) another database. Often you can just copy the table from the Database window and paste it; you can save just the structure of the table or both the structure and the records (see Chapter 13, "Importing and Exporting," for a discussion of these procedures). But the make table query offers the advantage of setting criteria to limit the records you want. 1. | With your copied Northwind database opened, double-click the Orders table to open it.
The Orders table has data about all of Northwind's orders. Note that there is a Freight field with the shipping charge. You'll make a new table that contains just orders with freight charges above $100.
| 2. | Close the Orders table. With the Orders table selected in the Database window, choose Insert, Query. With Design View selected, click OK.
Note that an action query uses the same design grid as other queries.
| 3. | Choose Query, Make Table Query (see Figure 9.17).
Figure 9.17. The icons for action queries all include an exclamation point. 
Note that the Make-Table query has an exclamation point in its icon. All the action queries include this punctuation.
| 4. | In the Make Table dialog box, type Heavy Freight Orders for the table name. Create the table in the current database. Click OK.
Note that I have used a name that is consistent with the naming convention used for other Northwind objects.
| 5. | Double-click the * in the Orders field list.
All the fields from the Orders table will be included in the new table.
| 6. | Double-click Freight to add it to the grid. Deselect the check box in the Show row.
| 7. | Type >100 in the Criteria row of the Freight column (see Figure 9.18).
Figure 9.18. A make-table query in Design view. 
| 8. | Click View to go to Datasheet view. Note that there are 187 records in the datasheet.
This step is optional, but it's a good idea to preview the records Access will paste into the new table. You can effectively review the potential impact of an action query before doing any damage and to ensure that it will do what is expected.
| 9. | Click View to return to Design view. Click Run. Access tells you that it will paste 187 records into a new table. Click Yes to confirm.
Note that you had to click Run to have the query create the table. In select queries, clicking View had the same effect as running it.
| 10. | Choose File, Close. Click Yes to save the design of the query. Type Freight Orders and click OK.
Often make-table and other action queries are created just for one-time use and are not saved. But you'll save it here to see how a saved action query works.
| 11. | In the Database window, click Tables and open Heavy Freight Orders. It has the 187 records you viewed in the query. Close the table.
| 12. | Click Queries. Find the Freight Orders query.
The query is identified by the make table query icon, shown in Figure 9.19.
Figure 9.19. The action query Freight Orders is a separate query in the Database window. When you attempt to run it, you get a message that states the impending action. 
| 13. | Double-click the Freight Orders query to run it. You get a message that you are about to run a make-table query (refer to Figure 9.19).
Access will make you another table using the same criteria. (If it's in the same database, you need to rename the table with the same name, or Access will delete it.)
Another option would have been to open the query in Design view. You could then modify the criteria and run the query from there.
| 14. | Click No in the message.
|
Creating a Delete Query When I warned you that action queries can wreak havoc with your records, I was primarily thinking about the delete query. Besides removing the records in the specific table, the deletions can spread like a southern California wildfire and rip out records throughout your database. Let's see why. Choose Tools, Relationships. The Orders table has relationships with several tables, but it has a one-to-many relationship with the Order Details table. Double-click the line that connects the two field lists to open the Edit Relationships dialog box. (It's trickyyou have to click in the little bit of open space right in the middle of the line.) Cascade Delete Related Records is in force. As you saw in Chapter 4, "Establishing Relationships," if you delete a record on the "one" side of the relationship, all the records on the "many" side will be deleted as well. So if you delete, say, Order ID 10255, all the order detail records for 10255 will be affected, too. The effect of a delete query can be much more dramatic. Suppose you delete records in the Customers table. The Customers table has a one-to-many relationship with the Orders table. If Cascade Delete Related Records were in force between Customers and Orders, orders for those customers would be deleted as well. And if you delete Orders records, related records in the Order Details are gone, too. Of course, that might be exactly what you want to have happen, which is why you selected Cascade Delete Related Records in the first place. But recognize the wave of destruction you might be unleashing. Some database experts recommend that cascading deletes not be implemented precisely because of the devastating impact it might have. Now let's do the example. 1. | Right-click the relationship line between the Orders and Order Details tables.
| 2. | Choose Edit, Delete and confirm your change.
You're going to add back the Orders records a little later, so leave the order details records as they are. By deleting the relationship, the order details records will remain untouched.
| 3. | Close the Relationships window.
If for some reason you modified the layout of the Relationships window, you will be asked whether you want to save these changes. As discussed in Chapter 4, this "save your changes" message when you close the Relationships window applies only to modifications to the layout, not to the editing of any relationships.
| 4. | In the Database window, select the Orders table. Choose Insert, Query and, with Design view selected, click OK to create a new query.
| 5. | Choose Query, Delete Query. The Delete row is added to the grid.
| 6. | Choose File, Save. Name the query Delete Freight Orders and click OK.
| 7. | Double-click the asterisk in the field list to add all the fields to the grid.
| 8. | Open the drop-down list for the Delete row.
You have two choices, From and Where. From indicates the field or fields from which you are deleting records. Where denotes that the column contains criteria that in some way will limit the records to be deleted. Leave the selection as From.
| 9. | Double-click Freight to add it to the grid. The Delete row displays Where.
| 10. | Type >100 in the Criteria row of the Freight column.
| 11. | Click View. As in the make table query, you see the 187 records that will be deleted.
| 12. | Click View to return to Design view.
| 13. | Click Run and confirm the deletion by clicking Yes.
| 14. | Open the Orders table. There are now only 643 records in it, 187 fewer than the 830 that existed before you ran the query. Close the Orders table.
| 15. | Close the query and save your changes.
|
Creating an Update Query As its name implies, you use an update query to change many records at once. Suppose the ruling in a court case required shippers to refund 12% of the freight charges to Northwind for all orders to France or Italy. You could create an update query to reduce freight costs for orders to those countries by 12%. 1. | In the Database window, choose the Heavy Freight Orders table. Choose Insert, Query and create a new query in Design view.
| 2. | Save the query as Update Freight Orders.
| 3. | Double-click the Freight field to add it to the grid.
| 4. | Double-click the ShipCountry field to add it to the grid.
| 5. | Save the query as Freight Discount Update.
| 6. | Choose Query, Update Query. The Update To row appears on the design grid.
| 7. | In the Update To row of the Freight column, type [Freight]*0.88. This expression will reduce the freight cost by 12%.
| 8. | In the Criteria row of the ShipCountry field, type France Or Italy.
| 9. | Click View to see the records that will be affected.
First, note that these freight prices are before the update query has been run and the 12% discount has been effected.
Second, although you did include all the information needed to run an update query, it would be nice to see just which records you will be updating.
| 10. | Click View. Double-click OrderID to add it to the grid. In the Update To column, type [OrderID].
These additions enable you to see the OrderIDs that will be updated.
| 11. | Double-click ShipCountry to add it to the grid. In the UpdateTo Column, type [ShipCountry].
| 12. | Click View to see the 14 records that will be updated.
| 13. | Click View to return to Design view. Delete the OrderID and ShipCountry columns you just added.
| 14. | Click Run. Access tells you that 14 records will be updated. Click Yes to update the records.
You can't undo an action query after you've run it. But using a little algebra, you can accomplish the same task.
| 15. | As shown in Figure 9.20, edit the Update To row of the Freight field to [Freight]*(1/0.88).
Figure 9.20. In this update query, criteria is entered in the ShipCountry field to limit the records that are updated. 
| 16. | Click Run to run the query and confirm. Click View.
The freight costs are now the same as before you ran any update queries.
| 17. | Close the query and save your changes.
|
Creating an Append Query Last in the set of action queries is the append query, which is used for adding records to another table. We'll add the records from the Heavy Freight Orders table back to the Orders table. 1. | In the Database window, choose the Heavy Freight Orders table. Choose Insert, Query and create a new query in Design view.
| 2. | Save the query as Orders Append.
| 3. | Choose Query, Append Query.
| 4. | In the Append To dialog box, open the Table Name drop-down list and select Orders. The database is the current database. Click OK.
| 5. | Double-click the asterisk in the Heavy Freight Orders field list to add all the fields to the grid.
Because all the fields in both the source and target tables are the same, you can just double-click the asterisk to include all fields.
| 6. | Click View. These 187 records will be added back to the Orders table.
| 7. | Click View to return to Design view. Click Run and confirm the append.
| 8. | Open the Orders table. There are 830 records, just as there were before you ran the delete query. Close the Orders table.
| 9. | Close the query and save your changes.
|
NOTE When you append records that have a primary key with an AutoNumber data type, you have the choice of including the primary key from the source table (in this case, Heavy Freight Orders) in the target table (in this case, Orders). In this example, because you wanted to append the records with the same IDs they've had all along, you included the primary key on the design grid. (By using the asterisk, you effectively added the OrderIDs to the query.) If you want to assign new IDs to the records, don't include the primary key on the grid. Access will append the records, the first of which will have an ID one larger than the highest number ever entered in this AutoNumber field.
Completing Action Queries Just for completeness, leave the Northwind database exactly as you started it. (Because you made a copy, this section is completely optional.) 1. | Choose Tools, Relationships to open the Relationships window.
| 2. | Click OrderID in the Orders field list. Drag and drop it on OrderID in the Order Details field list.
| 3. | Select Enforce Referential Integrity and Cascade Delete Related Records. Click Create. Close the Relationships window.
|
In this example, you created five new objects. First, you created the new table Heavy Freight Orders. Second, you created four action queries, all of which can be easily identified on the Queries tab of the Database window by their exclamation marks. |
