Access 2007[c] The Missing Manual

8.1. Understanding Action Queries

Action queries aren't quite as useful as select queries, because they tend to be less flexible. You create an ideal query once, and reuse it over and over. Select queries fit the bill, because you'll often want to review the same sort of information (last week's orders, top-selling products, class sizes, and so on). But action queries are trickier, because they make permanent changes.

In most cases, a change is a one-time-only affair, so you don't have any reason to hang onto an action query that just applies the same change all over again. And even if you do need to modify some details regularly (like product prices or ware-house stocking levels), the actual values you set aren't the same each time. As a result, you can't create an action query that can apply your change in an auto-mated fashion.

But before you skip this chapter for greener pastures, it's important to consider some cases where action queries are surprisingly handy. Action queries shine if you have:

• Batch tasks that you want to repeatedly apply . Some tasks can be repeated exactly. You may need to copy a large number of records from one table to another, delete a batch of old information, or update a status field across a group of records. If you need to perform this kind of task over and over again, action queries are a perfect timesaver.

• Complex or tedious tasks that affect a large number of records . Every once in a while, a table needs a minor realignment . You may decide that it's time to increase selling prices by 15 percent, or you may discover that all orders linked to customer 403 really should point to customer 404. These are one-off tasks, but they affect a large number of records. To polish them off, you need to spend some serious time in the datasheetor you can craft a new action query that makes the change more efficiently . When you're done, you decide whether you delete the action query, or save it in case you want to modify and reuse your work later on.

• Tasks that depend on a single piece of information, which you supply every time you run the query . You can create an action query that also uses parameters, which allow you to supply critical values every time you run the query. (Query parameters are explained in Section 7.4.) Using query parameters, you can change a relatively inflexible query (like one that deletes a specific record) to a more flexible one (like one that deletes any record you choose).

8.1.1. Testing Action Queries (Carefully)

In the wrong hands, action queries are nothing but a high-tech way to shoot your-self in the foot . They commit changes (usually to multiple records), and once you've applied the changes, you can't reverse them. Some database fans avoid action queries completely.

If you do decide to use action queries (and there are plenty of handy tricks you can accomplish with them), then you need to take the right precautions . Most importantly, before you use an action query, make a database backup! This step's especially crucial when you're creating a new action query, because it may not always generate the result you expect. To make a backup, you can copy your .accdb database file (just like you would any other file; one way is to right-click it, and then select Copy). If you don't want to mess with Windows Explorer, then you can create a backup without leaving Access by selecting the Office button Manage Back Up Database (Section 1.3.1).

Backups are great for disaster recovery, but it's still a good idea to avoid making a mistake in the first place. One safe approach is to start by creating a select query. You can then make sure your query's selecting the correct records before taking the next step and converting it into an action query (by choosing one of the action query types in the Query Tools Design Query Type section of the ribbon).

8.1.2. The Action Query Family

Access has four types of action queries:

• An update query changes the values in one or more records.

• An append query selects one or more records, and then adds them to an existing table.

• A make-table query selects one or more records, and then creates a new table for them.

• A delete query deletes one or more records.

In the following sections, you'll try out all of these queries.