Microsoft KB Archive/93690

From BetaArchive Wiki
Knowledge Base


ACC: How to Update a Table Based on Values in a Second Table

Article ID: 93690

Article Last Modified on 1/18/2007



APPLIES TO

  • Microsoft Access 1.0 Standard Edition
  • Microsoft Access 1.1 Standard Edition
  • Microsoft Access 2.0 Standard Edition
  • Microsoft Access 95 Standard Edition
  • Microsoft Access 97 Standard Edition



This article was previously published under Q93690

Novice: Requires knowledge of the user interface on single-user computers.


SUMMARY

To transfer data between two tables, you can create an additional field in one of the tables to store the common data, and then create an update query to accomplish the transfer.

MORE INFORMATION

This process can be illustrated using the sample database Northwind.mdb (or NWIND.MDB in Microsoft Access 1.x and 2.0). The following example adds a ProductName field to the Order Details table, and then copies the ProductName field from the Products table based on the ProductID field.

NOTE: In versions 1.x and 2.0, there is a space in the Product Name and Product ID field names.

NOTE: Typically, you would not duplicate the ProductName field because duplicate data is not good database design.

To transfer data between two tables, follow these steps:

  1. Open the Order Details table in Design view.
  2. Add a ProductName field as a Text data type.
  3. Save and then close the table.
  4. Create a new query based on the Order Details and Products tables.


NOTE: These tables are automatically joined on the ProductID field because a relationship has already been defined. If your tables are not joined, join them on the original linked field.

  1. On the Query menu, click Update Query (or Update in Microsoft Access 7.0 and earlier).
  2. On the View menu, click Table Names. Drag the ProductName field from the Order Details table to the first cell in the Field row of the query grid.
  3. In the Update To row, type:


[Products]![ProductName]

  1. On the Query menu, click Run. Note that all records in the Order Details table update with the Product Name from the Products table.
  2. Close the query without saving it.


REFERENCES

For more information about transferring data between two tables, search for "update queries, joined tables" using the Microsoft Access 97 Help Index.


Additional query words: link join transfer cascade multiple another upon

Keywords: kbfaq kbhowto kbusage KB93690