Access 2007: The Missing Manual

Chapter 8. Queries That Update Records

Queries are most famously known for their ability to show small subsets of huge amounts of information. This type of query’s called a select query, and it’s the variety you learned about in the previous two chapters.

Many Access fans don’t realize that queries have another identity. Not only can you use them to search for information, but you can also use them to change data. Queries that take this more drastic step—whether it’s deleting, updating, or adding records—are known collectively as action queries.

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