How to compare two MS Access tables?

[Guest_imported]

I have a MS Access database with two tables.
I need to update some data from first table using data from second table. How can I do that?

Thank you very much,
Gabe

 

[tlbroadbent]

Assuming that there are one or more columns that establish the relationship between the tables, you can easily compare tables and update columns in one table with data from the other.

Example:
Table1: Has employee data
EmpID
EmpName
EmpAddress
EmpSSN
etc.

Table2: Contains updates for the employee data
EmpID
EmpName
EmpAddress
etc.

To update table1 with table2 data, open the query designer and add both tables to the designer window. Now drag the EmpID from table1 and drop it on the EmpID in table2. It may already be there depending on how your database is designed. Double click the line between table1 and table2, joining the tables on EmpID.

You'll see the Join Properties. Select #2 - Include all records from 'table1' and only those records in 'table2' where the joined fields are equal.

Select Query | Update query from the menu.

Add the fields in table1 that you want to update to the query grid. Type in the corresponding fields from table2 in the Update To: box below each column in table1. Include the table name such as table2.EmpName

That should do it. If you look at the query in SQL view, it will look something like this.

UPDATE Table1 LEFT JOIN Table2
ON Table1.EmpID = Table2.EmpID
SET Table1.EmpName = [table2].[EmpName],
Table1.EmpAddress = [table2].[EmpAddress]
Terry L. Broadbent
faq183-874 contains some tips and ideas for posting questions in these forums. Please review it and comment if you have time.
NOTE: Reference to the FAQ is part of my signature and is not directed at any individual.