Microsoft Access 2010 Bible

Part III: More-Advanced Access Techniques

672

a datasheet. When you’re satisfied that the query is selecting the appropriate records, return to

Design view and choose the type of action (make table, update, or delete) you want to perform.

When you run the query, the query modifies the selected records.

Creating an update action query

It’s possible to individually update records in a table by using a form or by changing records in a

query’s datasheet. However, these approaches are impractical if there are many records to change.

In addition, manual updates introduce transcription errors as you enter new text into fields.

The best way to handle this type of event is to use an update action query to change multiple

records as a single operation. You save time and eliminate transcriptional errors that crop up in

manually edited records.

Before embarking on a new action query, it’s always a good idea to back up the tables affected by

the query’s action, if possible. Changes to Access databases can’t be rolled back or undone, so the

additional time required to copy a table (which can be as easy as selecting the table in the

Navigation Pane, pressing Ctrl+C and then Ctrl+V, and accepting the default name for the copied

table) is easily justified.

Let’s create an update action query:

1. Create a very simple select query that selects the RetailPrice and Category fields
   from tlbProducts.


2. Use = “trucks” as the criteria for Category.


3. Select Datasheet View from the View drop-down list in the Results group in the Design
   ribbon tab to preview the records that will be updated.


4. Click the Select button in the Query Type group on the ribbon.

Access adds the Update To row to the QBE grid.

5. Enter the following expression in the Update To cell in the RetailPrice field in the QBE
   grid:

[RetailPrice] * 1.05

6. Click the Run button in the Results ribbon group.

Access displays a message box indicating how many records will be updated, and asking
you to verify the changes to be made to the database.

7. Either click OK to commit the changes, or click No to return to the query’s Design view.

The completed update query is shown in Figure 18.34. Notice the similarities between this query

and the select queries discussed in Chapter 4. In fact, the only indication that qryUpdateRe-

tailPrices is an update query is the presence of the Update To row in the QBE grid.