• Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint
Share this Page URL

Chapter 11. Using Action Queries > Updating Values of Multiple Records in a Tab...

Updating Values of Multiple Records in a Table

Update queries change the values of data in a table. Such queries are useful when you must update field values for many records with a common expression. For example, you might need to increase or decrease the unit prices of all products or products within a particular category by a fixed percentage.

To see how an update query works, you perform some of the housekeeping chores discussed earlier in the chapter that are associated with using the tblShipAddresses table. To implement this example, you must have created the tblShipAddresses table, as described in the Creating New Tables with Make-Table Queries section earlier in this chapter. You also must modify the tblOrders and tblShipAddresses tables to include a field for the ShipID code, by following these steps:

Click the Tables tab in the Database window and open the tblOrders table in Design mode. If you didn't create the tblOrders table as a backup table for the example of the preceding section, do so now.

Select the OrderDate field by clicking the selection button, and press Insert to add a new field between EmployeeID and OrderDate. (Access inserts fields in tables above the selected field.)

Type ShipID as the field name, select Number as the field data type, and select Long Integer as the field's Field Size. Set the Required property's value to Yes. The table design pane appears as in Figure 11.18 (which shows the new ShipID field selected).

Figure 11.18. Adding the ShipID field to the tblOrders table.

Close the tblOrders table and save the changes to your design. You changed the domain integrity rules when you added the Required property, so the message box in Figure 11.19 appears. Choose No to avoid the test, which would fail because no values have been added to the ShipID field.

Open the tblShipAddresses table in Datasheet view.

Figure 11.19. Choosing whether to test changes to domain integrity rules.

Click the ShipName field header and choose Insert, Column to add a Field1 field between the CustomerID and the ShipName fields. The capability to add new columns (fields) in Table Datasheet view was a new feature of Access 95.

Type 1 in the Field1 cell for each record of the tblShipAddress table.

Change to Design mode, and change the name of Field1 to ShipID. Access 97 detects from your data entries that the field should be a Number field, and assigns Long Integer as the default Field Size property value. Change the value of the Required property to Yes.

Select both the CustomerID and the ShipID field by clicking and dragging the mouse.

Click the toolbar's Primary Key button to create a composite primary key on the CustomerID and ShipID fields, and then close the tblShipAddress table. This time, you test the changes that you made to the table.



Not a subscriber?

Start A Free Trial

  • Creative Edge
  • Create BookmarkCreate Bookmark
  • Create Note or TagCreate Note or Tag
  • PrintPrint