×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

How to compare two MS Access tables?

How to compare two MS Access tables?

How to compare two MS Access tables?

(OP)
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

RE: How to compare two MS Access tables?


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.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close