Microsoft KB Archive/209728

= ACC2000: How to Transfer Data from One Table to Another =

Article ID: 209728

Article Last Modified on 6/29/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209728



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
CAUTION: If you follow the steps in this example, you modify the sample database Northwind.mdb. You may want to back up the Northwind.mdb file and follow these steps on a copy of the database.

This process can be illustrated using the sample database Northwind.mdb. 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: Typically, you do not duplicate the ProductName field because duplicate data does not indicate good database design.

To transfer data between two tables, follow these steps:

 Open the Order Details table in Design view. Add a ProductName field as a Text data type. Save and then close the table. 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.

 On the Query menu, click Update Query. 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.</li> In the Update To row, type:

[Products]![ProductName]

</li> On the Query menu, click Run. You receive a Microsoft Access message warning you that you are changing the data; click Yes.</li> Look at the Order Details table. Note that all records in the Order Details table are updated with the Product Name from the Products table.</li> Close the query without saving it.</li></ol>

<div class="references_section">