You've imported a table that contains updated prices for some of the records in a table in your database. The data in all the other fields in the existing table is still correct. Is there any way?short of using a complex VBA procedure?to update the price data in the existing table based on the updated prices from the imported table without overwriting any of the other fields in the existing table?
You probably already know that you can use an Update query to update the values of fields in a table, but did you know that you can use an Update query to update the values in one table with the values from another? This solution will show you how to do just that. If you can join the two tables on some common field or combination of fields, you can use an Update query to update a field in one table based on the values found in a second table.
Here are the steps to create an Update query that updates values across tables:
Create a standard Select query. Add the two tables to the query and join them on the common field or fields. In the sample database, we added the tblAlbums and tblAlbumsUpdated tables to the query. We will refer to tblAlbumsUpdated as the source table because it will supply the values to be used to update the other table; tblAlbums is the target table because it will be the target of the updates. Access has automatically joined the two tables on AlbumID. If the name of the common field is not the same, you will have to join the two tables by dragging the common field from one table to the other.
Select Query Update to change the type of query to an update action query.
Drag the field to be updated in the target table to the query grid. In the Update To cell for the field that will be updated, specify the fully qualified name of the field in the source table that will be the source of the updated values. This field name should include the name of the table surrounded by square brackets, a period, and the name of the field surrounded by square brackets. For qryUpdateAlbumPrices, drag the PurchasePrice field from tblAlbums to the query grid. The field settings for PurchasePrice are shown in Table 1-4.
Field |
Table |
Update To |
Criteria |
---|---|---|---|
PurchasePrice |
tblAlbums |
[tblAlbumsUpdated].[PurchasePrice] |
Is Null |
|
Optionally specify criteria to limit the rows to be updated. In the qryUpdateAlbumPrices example, we used criteria to limit the updated rows to those with null (missing) prices (see Table 1-4). This prevents Access from overwriting any existing non-null values in tblAlbums.
Execute the query by selecting Query Run or by clicking on the exclamation point icon.
|
For an example of updating a field in a table based on the value of a field in another table, open the tblAlbums table found in the 01-06.MDB database. Note that most of the purchase prices are null (see Figure 1-15). Open tblAlbumsUpdated, and you'll see that many of the purchase prices for the same albums have been entered (see Figure 1-16).
Now run the qryUpdateAlbumPrices query found in the same database (see Figure 1-17). This action query will take the PurchasePrice values from tblAlbumsUpdated and copy it into the Purchase Price field in tblAlbums for each record where the two AlbumID fields match and the price value in tblAlbums is currently null. When the query is finished, open tblAlbums again?you should see that the Purchase Price field in this table has been updated based on the values in tblAlbumsUpdated (see Figure 1-18).
You can use update queries in Access to update the values in a target table, and you can use another table to supply the values for the update. The trick is to join the two tables using a common field and to properly specify the name of the field from the source table in the Update To cell.
You can update more than one field at a time in an update query. You can also include additional fields in the query grid to further limit the rows to be updated. Drag these additional fields to the query grid and specify criteria for them. As long as you leave the Update To row blank for these columns, they will be used for their criteria only and will not be updated. Update queries are the most efficient way to make bulk changes to data; they are much more efficient than using a recordset in a VBA procedure.