Access 2013: The Missing Manual

Chapter 9. Queries That Change Tables

Queries are most famously known for their ability to show small subsets of huge amounts of information. This type of query is 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 again. And even if you do need to modify some details regularly (like product prices or warehouse 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 automated fashion.

But before you skip this chapter for greener pastures, it’s important to consider some cases where action queries are surprisingly ...