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.

Note

If you didn't create the tblShipAddresses table and you've installed the sample files from the accompanying CD-ROM, you can import this table from the \Program Files\Seua11\Chaptr13\Action13.mdb database.

Adding a ShipToID Field to the tblOrders Table

You must modify the tblOrders and tblShipAddresses tables to include a field for the ShipToID code that relates the two tables. To add the ShipToID field to the tblOrders table, do this:

1. 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.

2. Select the ShipVia field by clicking the selection button and then press Insert to add a new field between ShippedDate and ShipVia. (Access inserts fields in tables above the selected field.)

3. Type ShipToID as the field name, select Number as the field data type, and accept the default Long Integer as the field's Field Size. Set the Required property value to Yes. Access automatically adds a Duplicates OK index to fields whose names end with "ID". You don't need an index on this field, so set the Indexed property value to No. The table design pane appears as in Figure 13.12, which shows the new ShipToID field selected.

   Figure 13.12. Add the ShipToID field as the foreign key for a relationship with the ShipToID field you add to the tblShipAddress table.

   

4. Close the tblOrders table and save the changes to your design. You changed the domain integrity rules when you added the Required property, so a message box asks whether you want to test domain integrity. Click No to avoid the test, which would fail because no values have been added to the ShipToID field.

Adding a ShipToID Field and Composite Primary Key to the tblShipAddresses Table

Now add the ShipToID field and establish a composite primary key for the tblShipAddresses table by doing the following:

1. Open the tblShipAddresses table in Datasheet view.

2. Click the ShipName field header and choose Insert, Column to add a Field1 field between the CustomerID and the ShipName fields.

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

4. Change to design mode and change the name of Field1 to ShipToID. Access 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.

5. Click the CustomerID field and Shift+click the ShipToID field to select both fields.

6. Click the toolbar's Primary Key button to create a composite primary key on the CustomerID and ShipToID fields. Your table design appears as shown in Figure 13.13.

   Figure 13.13. The ShipToID and CustomerID fields comprise a composite primary key of the tblShipAddresses table.

   

7. Close the tblShipAddresses table. This time you test the changes that you made to the table, so click Yes when the Data Integrity Rules message box opens.

Writing Update Queries to Add Foreign-Key Values to the tblOrders Table

To indicate where the orders were shipped, you must update the ShipToId field in tblShipAddresses. The value 1 indicates a shipping address other than the customer's address; the value 0 indicates the order is shipped to the customer's billing address. You can accomplish this by running an update query:

1. Create a new query and add the Customers and tblOrders tables to it. Relationships haven't been specified between the two tables, so the join line between the tables doesn't have one-to-many symbols.

2. Drag the tblOrders table's ShipName and ShipAddress fields to the first two columns of the Query Design grid.

3. Type <>[Customers].[CompanyName] in the first Criteria row of the ShipName column and <>[Customers].[Address] in the second Criteria row of the ShipAddress column. (Prior tests show that you don't need to test the City, PostalCode, and Country fields.) Your query design appears as shown in Figure 13.14.

   Figure 13.14. This query design, which is similar to qryMTShipAddresses, returns all orders for which the ship to name or ship to address differs from the billing data.

   

4. Run the query to verify that you have correctly selected the set of records to be updated. In this case, you don't specify Unique Values, because you must change every tblOrders record that meets the query criteria.

After ensuring that you've selected the appropriate records of the tblOrders table for updating, 64 rows for the sample query, you're ready to convert the select query to an update query by following these steps:

1. Return to Query design mode and drag the tblOrders table's ShipToID field to the query's first column.

2. Choose Query, Update Query. A new Update To row replaces the Sort and Show rows of the select Query Design grid.

3. In the ShipToID column's Update To cell, type 1 to set ShipToID's value to 1 for orders that require the use of a record from the tblShipAddresses table. The Update Query Design grid appears as shown in Figure 13.15. The Update To cells of the remaining fields are blank, indicating that Jet is not to update values in these fields.

   Figure 13.15. Type the value (1) for the update to the ShipToID field of records that require a join to a record in the tblShipAddresses table.

   

4. Run the update query. A message box indicates the number of records to be updated, 64 for this example. Click Yes to continue.

5. Click the Database Window button and open the tblOrders table. Check a few records to see that you correctly added the ShipToID value of 1.

6. Close and save the update query, if desired.

Jet SQL

Update queries substitute UPDATE for SELECT, and a SET list for the SELECT field list. An update query can set multiple field values by additional, comma-separated TableName.FieldName = Value statements. Following is the Jet SQL statement for the sample update query:

     UPDATE Customers       INNER JOIN tblOrders        ON Customers.CustomerID = tblOrders.CustomerID     SET tblOrders.ShipToID = 1   WHERE tblOrders.ShipName<>[Customers].[CompanyName]     OR tblOrders.ShipAddress<>[Customers].[Address]; 

The Jet SQL and T-SQL statements are identical.

Finally, you must add 0 values to the ShipToID cells of records that have the same shipping and billing by following these steps:

1. Create a new query, and add only the tblOrders table.

2. Drag the ShipToID field to the query's first column and choose Query, Update Query.

3. Type 0 in the Update To row and Is Null in the Criteria row. Before running the query, check it in Datasheet view; all fields should be empty.

4. When you're sure the query is correct, click Run to replace Null values in the ShipToID column with 0.

After you check the tblOrders table to verify the result of your second update query, you can change to Table Design view and safely delete the ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, and ShipCountry fields from the table.

Using the tblShipAddress Table in a Query

When you join the tblOrders and tblShipAddresses tables in a query to regenerate the appearance of the original Orders table, you must specify a LEFT OUTER JOIN on the CustomerID and ShipToID fields of the tables to return all tblOrders records, not just those with records in the tblShipAddresses table.

To create a query that returns all rows of the tblOrders table with empty Ship... fields for records with 0 ShipToID values, do the following:

1. Open a new query, and add the tblOrders and tblShipAddresses tables.

2. Double-click the caption of the tblOrders field list in the upper pane to select all fields, and drag the selected fields to the grid. Add an ascending sort to the OrderID column.

3. Click the ShipName field in the tblShipAddresses field list, Shift+click the ShipCountry field, and drag the selected fields to the right of the Freight field in the grid.

4. In the upper pane, drag the CustomerID field of tblOrders and drop it on the CustomerID field of tblShipAddresses to create an INNER JOIN. Do the same for the ShipToID fields. As with earlier examples of this chapter, the direction in which you drag the field symbol (the same direction as the other join, left-to-right) is important. Your query design appears as shown in Figure 13.16. The query grid is scrolled to the right to show the first two fields from the tblShipAddresses table.

   Figure 13.16. This query design has INNER JOINs between tblOrders and tblShipAddresses, so the query returns only rows for which records exist in the tblShipAddresses table.

   

5. Select and then double-click the join line between the CustomerID fields to open the Join Properties dialog. Select option 2, a LEFT OUTER JOIN, and click OK. Specifying this join adds a right-pointing arrow to the join line.

6. Repeat step 5 for the ShipToID field (see Figure 13.17). Both joins must be LEFT OUTER JOINs to return all tblOrders records.

   Figure 13.17. Specifying a LEFT (OUTER) JOIN (option 2) in the Join Properties dialog adds an arrow to the join line.

   

7. Run the query to verify that records for orders with and without ship addresses appear (see Figure 13.18). Save the query as qryLOJtblOrders or a similar name, but don't close it.

   Figure 13.18. This Datasheet view of the query result set from the design of Figure 13.17 returns all orders. The first two columns are frozen.

   

Using the tblShipAddress Table with UNION Queries

If you want to substitute "Same as Bill To" or the like as the Ship To address on invoices for those orders in which the value of the ShipToID field is 0, you can either write VBA code or a UNION query to accomplish this task; however, the latter approach is much simpler.

To review creating UNION queries, see "Using UNION Queries to Combine Multiple Result Sets," p. 452.

To quickly write the SQL statement for a UNION query that adds a text value Same as Bill To to the ShipName field for 0 ShipToID values, do this:

1. Open the SQL view of qryLOJtblOrders, select the entire SQL statement, and press Ctrl+C to copy it the Clipboard. Close the query.

2. Open a new query, close the Show Table dialog, and select SQL View from the View button to open the SQL window. Delete the SELECT; fragment.

3. Press Ctrl+V to paste the SQL statement to the window.

4. Replace LEFT in LEFT JOIN with INNER to return only the rows with values in the tblShipAddresses table.

5. Delete the trailing semicolon of the pasted text, press Enter twice, and type the following UNION SELECT statement as shown here:

       UNION SELECT tblOrders.OrderID, tblOrders.CustomerID,      tblOrders.EmployeeID, tblOrders.OrderDate, tblOrders.RequiredDate,      tblOrders.ShippedDate, tblOrders.ShipToID, tblOrders.ShipVia, tblOrders. Freight,     "Same as Bill To", " ", " ", " ", " ", " "    FROM tblOrders    WHERE tblOrders.ShipToID = 0; 

   Your SQL window appears as shown in Figure 13.19. The five space values (" ",) in the added statement are required because both components of the UNION query result set must have the same number of columns.

   Figure 13.19. This SQL statement consists of a copy of the SELECT query of the preceding example with an INNER instead of a LEFT JOIN and a UNION SELECT statement to add the rows with the 0 ShipToID values.

   

   Tip

   You can save some typing by copying the tblOrders.... elements of the field list after the UNION SELECT statement.

6. Run the query to verify that the result set contains the Same as Bill To values in the ShipName column (see Figure 13.20). Save the query as qryUQtblOrdersShipTo.

   Figure 13.20. The UNION query Jet SQL statement of Figure 13.18 returns the expected result set. (The first two columns are frozen).

   

The Query Datasheet view of a Jet query you generate from an SQL statement differs from queries you create in the Access query designer. Queries based on SQL statements that Access can't display in Query Design view don't inherit table properties, such as captions and lookup fields.

Jet SQL

You can regenerate an exact duplicate of the original Orders table that has ship to addresses for each order with the following lengthy SQL statement:

[View full width]
     SELECT tblOrders.OrderID, tblOrders.CustomerID, tblOrders. EmployeeID,        tblOrders.OrderDate, tblOrders.RequiredDate, tblOrders. ShippedDate,        tblOrders.ShipToID, tblOrders.ShipVia, tblOrders.Freight, tblShipAddresses.ShipName,        tblShipAddresses.ShipAddress,tblShipAddresses.ShipCity,        tblShipAddresses.ShipRegion, tblShipAddresses. ShipPostalCode,        tblShipAddresses.ShipCountry     FROM tblOrders         INNER JOIN tblShipAddresses           ON (tblOrders.ShipToID = tblShipAddresses.ShipToID)             AND (tblOrders.CustomerID = tblShipAddresses. CustomerID)     UNION SELECT tblOrders.OrderID, tblOrders.CustomerID,  tblOrders.EmployeeID,       tblOrders.OrderDate, tblOrders.RequiredDate, tblOrders. ShippedDate,       tblOrders.ShipToID, tblOrders.ShipVia, tblOrders.Freight,     Customers.CompanyName, Customers.Address, Customers.City,  Customers.Region,       Customers.PostalCode, Customers.Country     FROM tblOrders      INNER JOIN Customers        ON (Customers.CustomerID = tblOrders.CustomerID)     WHERE tblOrders.ShipToID = 0; 

You can save time by copying the basic structure of the first SELECT statement to the UNION SELECT statement, changing tblShipAddresses.... field names to corresponding Customers.... field names, and altering the INNER JOIN statement to join the Customers and tblOrders tables on the CustomerID field. (The name of this query is qryUQtblOrdersShipTo; Figure 13.21 shows its query result set.)

Figure 13.21. You can produce a query result set that's an exact duplicate of the original Orders table with a UNION query that returns Bill To addresses from the Customer table.