Microsoft® Access® 2010 24-Hour Trainer

27

Creating Update Queries

Update queries are used to modify the values in a specific field in one or more existing records for a given table. An Update query can change the value in a single record or modify multiple records. In most normalized database systems, standard practice is to add new records to modify data, instead of modifying existing records.

However, at times updating information is necessary, and an Update query is the tool to use. A simple example is a part number that has been stored with a single-letter prefix. Management would like to remove the prefix and just store the basic part number. If you are using a normalized data structure, the part number should be stored in a part number table, with foreign keys for the part stored in a transaction table. In the case of a normalized database, you would develop an Update query to update the part number, probably using the Mid() function.

In this lesson, you learn how to create and use Update queries, how to set some of the settings, and the ramifications of using them.

LESSON SETUP

For this lesson you need Access 2010 and the Lesson 27 files from the book's website at www.wrox.com. You should be familiar with creating database objects and working with queries to complete this lesson successfully.

UPDATE QUERIES IN ACCESS

An Update query in Access is like a Find & Replace command on steroids. The query performs much the same function, but with many more capabilities. With capabilities like updating multiple fields ...