Changing Data with an Update Query | Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))

3 4

Entering new records is only one part of maintaining reliable data. Often, you’ll need to adjust existing data. Updates are generally made to individual records, but occasionally you might need to modify an entire group of records. Can you imagine making the same change to hundreds or even thousands of records? An update query is the way to go when you’re dealing with large groups of records. Update queries allow you to state your change in the form of criteria, and then the query takes care of the entire group.

For example, suppose you want to increase the price of each product listed in the Northwind database by 2 percent. You could spend a lot of time calculating new prices for all 77 products in the Products table and then modify every record yourself. A better idea is to run a query.

note

The examples used in this chapter refer to the Northwind database. Because this database ships with Access, you might not want to alter the Northwind files. In these examples, you’ll create copies of each table you plan to modify to act as a backup in case the query has unexpected results. After completing each query exercise, you can delete the modified tables and rename the copies with their original names, or you can work with the copies instead of the original tables. Another option is to create a blank database and import the tables.

To run an update query on the Northwind database, follow these steps:

Make a copy of the Products table. (See the section "Protecting Your Data," for details on copying a table.)
In the Database window, click Tables on the Objects bar, and select the Products table.
Choose Query from the New Object button’s drop-down list. (You’ll find the New Object button on the Database toolbar.)
Double-click Design View in the New Query dialog box, or choose Design View (the default), and click OK.
In the Query Design window, drag the UnitPrice field to the design grid.
Choose Query, Update Query. The design grid will display an Update To row.
Enter the following expression in the UnitPrice field’s Update To cell (as shown in Figure 10-2):
```
 [UnitPrice]+([UnitPrice]*0.02) 
```
note

When you create an expression in a text box’s Control Source property, it must begin with an equal sign. Expressions in queries don’t need the equal sign.

Figure 10-2. This expression modifies the existing entry.

This expression evaluates to the original value of UnitPrice plus 2 percent of UnitPrice. Be sure to include the brackets, or Access will interpret the field name as a string and delete the data in the UnitPrice field when you run the query.
From the View button’s drop-down list, choose Datasheet View to view the results of the query before you actually run it.
Access won’t display the new values; it displays only those values that the query will update. In this case, that includes all the values in the UnitPrice field.
Return to Design view.
Click Run on the Query Design toolbar to update the UnitPrice entry for each record. Access will display the confirmation request shown in Figure 10-3, indicating the number of records your query will update. Click Yes.
Figure 10-3. Click Yes to update the Products table.
Open the Products table, and review the new prices, shown in Figure 10-4. For example, Chai was originally $18 per unit, as shown in the first record of the table. Now, it’s $18.36—an increase of 2 percent.
Figure 10-4. The query modified each UnitPrice value.

tip

If you want to stop a query after clicking Run, press Ctrl+Break.

Understanding the SQL UPDATE Statement

Update queries use the SQL UPDATE statement to modify data. The UPDATE statement takes the following form:

 UPDATE table/query  SET col = expression WHERE criteria;

You can update multiple fields in an update query by setting appropriate criteria expressions for each field that needs updating. In this case, the query’s SQL statement will have multiple SET clauses, separated by commas.

Let’s take a look at the update query’s SQL statement from the previous example:

 UPDATE Products  SET Products.UnitPrice = [UnitPrice]+([UnitPrice]*0.02);

The UPDATE keyword is the first clue that you’re dealing with a query that modifies existing data. The SET clause identifies the field that the query updates and the field’s new value.

Specifying Criteria

Updating data won’t always be as clear-cut as in the previous example. Sometimes you need to update some records but not others. For instance, suppose you want to increase the price of only those products that cost less than $20. You can still use an update query, but you have to be specific about which records the query should modify. You do this by adding a criteria expression.

Follow these steps to create a query that contains a criteria expression:

Repeats steps 1 through 7 from the example.
Add the following expression to the Criteria cell of the UnitPrice field (as shown in Figure 10-5):
```
 <20 
```
Figure 10-5. The Criteria expression restricts updates to those records in which the unit price is less than $20.
Run the query. Click Yes to confirm that you want to update the records.
Open the Products table and review the UnitPrice values. Only those items that cost less than $20 have been updated.
As you can see in Figure 10-6, Chef Anton’s Cajun Seasoning (the fourth record) is the first record the query didn’t update because the UnitPrice value is $22, which is greater than $20.

Figure 10-6. This query modified only some of the UnitPrice values.

Review this query’s SQL statement, shown here:

 UPDATE Products  SET Products.UnitPrice = [UnitPrice]+([UnitPrice]*0.02) WHERE (((Products.UnitPrice)<20));

You can see that this SQL statement is quite similar to our first query’s SQL statement. However, this one includes a WHERE clause that restricts the query.

Update queries work just as well with multiple tables. For example, the following SQL statement updates the "many" side of the join based on criteria in the "one" side:

 UPDATE Orders  INNER JOIN [Order Details]  ON Orders.OrderID = [Order Details].OrderID  SET [Order Details].Discount = 0.1 WHERE Orders.CustomerID = "CHOPS";

Specifically, the query states that when the CustomerID value equals "CHOPS", update the Discount value to 0.1 (10 percent).

note

You can’t create a multiple-table update query if one of the tables in the join is a summary query. For example, including a GROUP BY clause would result in a summary query.

Updating Both Sides of a Relationship

Relationships play a part in the update process, even though this part isn’t always readily apparent. In fact, you might not need an update query at all if the correct relationships exist. When updating records in a one-to-many relationship, you typically update many records. However, the perspective changes a bit if you need to change both sides of that relationship. For instance, let’s suppose you want to change a primary key value. Naturally, you’d also need to update all the related records—that is, the foreign key values. You can approach this situation in two ways. You can change the primary key value and then run an update query to modify the related table (foreign key values) accordingly.

The second approach is to create a relationship (if none exists) between the related fields in the Relationships window and select the Enforce Referential Integrity and Cascade Updated Related Fields check boxes in the Edit Relationships dialog box. (See Chapter 4, "Creating a Database," for more details on setting options in the Edit Relationships dialog box.) Access will automatically update the foreign key field in the related table when you change the primary key value. Remember that the cascading updates solution works only when you update the primary and foreign key values. (Keep in mind that you can’t update the values in an AutoNumber field.)

For definitions of database terms, see Chapter 3, "Introduction to Database Design."