Show
Use a Field in One Table to Update a Field in Another TableProblemYou’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? SolutionYou 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:
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 Figure 1-15. Many of the purchase values in tblAlbums are null Figure 1-16. tblAlbumsUpdated contains updated purchase prices for several albums in tblAlbums 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). Figure 1-17. The qryUpdateAlbumPrices update query in design view Figure 1-18. The updated purchase prices for albums in tblAlbums DiscussionYou 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. When a record from one table is related to several records in another table the table have a oneIn a one-to-many relationship, one record in a table can be associated with one or more records in another table. For example, each customer can have many sales orders. In this example the primary key field in the Customers table, Customer ID, is designed to contain unique values.
When there is a relationship between records in two different tables?A table relationship works by matching data in key fields — often a field with the same name in both tables. In most cases, these matching fields are the primary key from one table, which provides a unique identifier for each record, and a foreign key in the other table.
What relationship describes an association between two tables in which one record of either table is related to the many records of another table?In a one-to-many relationship, the table on the one side of the relationship is the primary table and the table on the many side is the related table. A one-to-many relationship is the most common relationship found between tables in a relational database.
What is a table that matches the records across two tables called?You do this by creating a third table, called a junction table or a relationship table, that has a primary key and a foreign key for each of the other tables.
|