How To Fill A Record With Data From A Previous Record Automatically In Access 2010

Use a Field in I Tabular array to Update a Field in Another Table

Problem

You've imported a table that contains updated prices for some of the records in a tabular array in your database. The information in all the other fields in the existing table is nevertheless correct. Is there whatsoever way—short of using a circuitous VBA procedure—to update the price information in the existing tabular array based on the updated prices from the imported table without overwriting whatever of the other fields in the existing tabular array?

Solution

You probably already know that you tin can utilize an update query to update the values of fields in a table, but did y'all know that y'all tin use an update query to update the values in one table with the values from another? This solution will show you how to exercise just that. If you can join the two tables on some mutual field or combination of fields, you can employ an update query to update a field in one table based on the values found in a 2nd tabular array.

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 volition refer to tblAlbumsUpdated equally the source table because information technology will supply the values to be used to update the other table; tblAlbums is the target table considering it will be the target of the updates. Access has automatically joined the two tables on AlbumID. If the proper noun of the mutual field is not the same, you will take to join the two tables by dragging the common field from ane table to the other.
Select Query → Update to alter the type of query to an update action query.

Drag the field to be updated in the target tabular array to the query grid. In the Update To cell for the field that will exist updated, specify the fully qualified name of the field in the source table that volition be the source of the updated values. This field proper noun should include the proper noun of the table surrounded by foursquare brackets, a period, and the name of the field surrounded past square brackets. For qryUpdateAlbumPrices, drag the AlbumID field from tblAlbums to the query grid. The field settings for AlbumID are shown in Tabular array 1-4.

Table 1-4. Field settings for qryUpdateAlbumPrices

Field	Table	Update To	Criteria
AlbumID	tblAlbums	[tblAlbumsUpdated].[PurchasePrice]	Is Null

Warning

Be conscientious when specifying the Update To value. If you misspell the source field proper name, you lot run the risk of irresolute the values to the misspelled string rather than to the values in the source field. If Access surrounds the Update To value with quotes or prompts yous for an unexpected parameter when yous endeavor to execute the update query, information technology'southward likely that you made a spelling mistake.

Optionally specify criteria to limit the rows to be updated. In the qryUpdateAlbumPrices case, we used criteria to limit the updated rows to those with null (missing) prices (run across Tabular array ane-iv). This prevents Access from overwriting any existing not-zilch values in tblAlbums.
Execute the query by selecting Query → Run or by clicking on the exclamation point icon.

Tip

You can preview the selected records in an action query by choosing View → Datasheet or by clicking on the Datasheet icon. The query will not be run, but y'all'll be able to encounter which records would be updated had you run the query.

For an instance of updating a field in a table based on the value of a field in another table, open up the tblAlbums table institute in the 01-05.MDB database. Note that about of the buy prices are null (encounter Figure 1-15). Open up tblAlbumsUpdated, and you lot'll see that many of the purchase prices for the aforementioned albums accept been entered (see Effigy 1-xvi).

Many of the purchase values in tblAlbums are null

Figure 1-15. Many of the purchase values in tblAlbums are null

tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums

Figure 1-xvi. tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums

At present run the qryUpdateAlbumPrices query found in the same database (see Figure 1-17). This action query volition take the PurchasePrice values from tblAlbumsUpdated and copy it into the Buy Cost field in tblAlbums for each record where the two AlbumID fields match and the toll value in tblAlbums is currently zippo. When the query is finished, open tblAlbums again—you should see that the Purchase Toll field in this table has been updated based on the values in tblAlbumsUpdated (see Figure 1-eighteen).

The qryUpdateAlbumPrices update query in design view

Figure 1-17. The qryUpdateAlbumPrices update query in blueprint view

The updated purchase prices for albums in tblAlbums

Figure ane-eighteen. The updated buy prices for albums in tblAlbums

Give-and-take

You can utilise update queries in Access to update the values in a target table, and you tin can use some other 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 jail cell.

You can update more one field at a fourth dimension in an update query. You lot tin also include boosted fields in the query filigree to further limit the rows to be updated. Drag these additional fields to the query grid and specify criteria for them. Every bit long as yous exit the Update To row bare for these columns, they volition exist used for their criteria merely and will not be updated. Update queries are the about efficient way to make bulk changes to data; they are much more efficient than using a recordset in a VBA procedure.