Microsoft Office Tutorials and References
In Depth Information
Changing Data with Update Queries
You may create a query to find orders that haven’t yet been shipped,
including orders for Golden Whistle, a discontinued item for which you have a
substitute. Then you can use the update query to change the item number in
records that meet those criteria to New Golden Whistle, the replacement item.
Using the update query when you work on lots and lots of data or when you
want to update multiple fields makes sense. But before you delve in to the
complexities of an action query, consider whether you can use the much
simpler Find and Replace dialog box to find and replace data instead. (See
Book II, Chapter 3 for more information on the Find and Replace dialog box.)
You can use the Find and Replace dialog box in a datasheet created by a
query; if you change the data in the query, the table holding the underlying
data reflects the change.
To create an update query, follow these steps:
1. Back up the database, or make copies of the tables and/or fields that
will be affected by the update.
Update queries can be hard to get right, so play it safe in case you need
to get your data back the way it was before you ran the update query.
2. Create a new select query in Design view.
See Chapter 1 of this minibook for more information on creating a query.
Include tables that you plan to update or that you need fields from to
establish the update criteria.
3. Put fields in the design grid.
Add the fields you want to see in the datasheet, the fields you want to
use with criteria to tell Access exactly what to update, and the fields you
want to change by using the update query.
See Chapter 1 of this minibook for more information on using the
4. Add the criteria to tell Access how to choose the records you want to
Figure 3-1 shows the select query that finds all unshipped orders for the
Golden Whistle. You see two fields included in the query: Shipped, to
look for orders that haven’t been shipped (this is a Yes/No field; No finds
unshipped orders), and Product ID, to look for orders that contain the
Golden Whistle product.
5. Click the View button to view the datasheet to make sure that all the
records you want to update, and none that you don’t, are included.
Edit the query as needed until you see only the records you want to
update. Figure 3-2 shows the datasheet for the query shown in Figure 3-1.