Access 2010: The Missing Manual

Update Queries

An update query searches for some records and then modifies them. Usually, you’ll limit your modifications to a single field, but Access lets you change as many fields as you want. You also have a fair bit of flexibility in how you apply the update. The simplest option is to stuff an entirely new value into a field. You could create a query that moves all the products in one category into another by entering a new value in the CategoryID field. Alternatively, you could take the current values in a field and change them, using an expression (a specialized database formula that can perform a variety of different calculations). You could increase all your product prices by 10 percent, or add a week to the due date of every outstanding project.

Tip

If you have a relatively straightforward, one-time-only update to make, you may prefer to use the datasheet’s find-and-replace feature (Searching). This approach gives you the chance to review the matches and choose whether to apply the change for each value.

The example that follows uses the Products and ProductsCategories tables from the Boutique Fudge database (which is described on The Chocolate Store). The query updates all the products in the Beverages category, increasing their prices by 10 percent. You can try this example for yourself by downloading the examples for this chapter from the Missing CD page at www.missingmanuals.com/cds.

Here’s how you can create the update query:

1. Create a new query by choosing Create→Queries→Query ...