Comparing data in tables...

[Jay Bukstein]

Is there a way I can compare the data in 2 indentical
tables?

My problem is some of the users of this database were
using the wrong database and the previous programmer put
a last modified date/time in the table but never set it
or never placed any auto-fill on the field. SO I have 2
tables with indentical record counts but the data within
maybe different.

Any Ideas..

 

[Allen Browne]

Join the two tables, and ask for the records that are different.

For example, if the tables are named tblClient1 and tblClient2, and they
have a ClientID primary key:

1. Create a query, containing both tables.

2. Drag tblClient1.ClientID onto tblClient2.ClientID. Access creates a join
line.

3. Double-click the join line, and choose the option:
All records from tblClient1, and any matches from tblClient2.

4. Drag the Surname field from both tables into the output grid. Enter this
into the Criteria row under Surname from tblClient2:
Is Null OR <> [tblClient1].[Surname]
The query now shows any records from tblClient1 that don't match the Surname
in tblClient2.

1. After dealing with those mismatches, go back to step 3, and change it to:
All records from tblClient2, and any matches from tblClient1.

2. Place this Criteria under tblClient1.Surname:
Is Null OR <> [tblClient2].[Surname]
This query gives you the mismatches the other direction.