Compare Two Tables for Discrepancies

  • Thread starter Thread starter adamsjw2
  • Start date Start date
A

adamsjw2

Greetings all,

We're changing over to a new data system and I need to compare a table
generated in the old system with a table generated by the new system.
I want to create two queries that:

1) Indicate the records in table A (old system) that don't appear in
table B (new system)

2) Indicate the records in Table B wherein the SSN (field 8) is equal,
but the one or more of the remaining 10 fields in the record aren't
the same.

TIA,
Jim
 
1) Do you have a primary key that would be the same in both tables? In other
words, how do you identify that a record in tablea is a match for a record in
TableB? If you do this by SSN, then something like the following.

SELECT TableA.*
FROM TableA LEFT Join TableB
ON TableA.SSN = TableB.SSN
WHERE TableB.SSN Is Null

2) SELECT TableB.*
FROM TableB Inner Join TableA
ON TableB.SSN = TableA.SSN
WHERE NZ(TableB.FieldA) <> NZ(TableA.FieldA) OR
NZ(TableB.FieldB) <> NZ(TableA.FieldB) OR
NZ(TableB.FieldC) <> NZ(TableA.FieldC) OR
...
 
I created a TableA and TableB with columns SSN, Name, Age

Here are the queries I came up with for your scenarios.

1. SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.SSN = TableB.SSN
WHERE (((TableB.SSN) Is Null));

2. SELECT TableA.SSN, TableA.Age, TableB.Age,
TableA.Name, TableB.Name
FROM TableA INNER JOIN TableB ON TableA.SSN = TableB.SSN
WHERE ((([tablea].[name]=[tableB].[name])=False)) OR
((([tablea].[age]=[tableb].[age])=False));

Thanks
Terri
 
Thanks so much for your help. This works great. Now if I can find an
explanation as to why my new system contains 1900 fewer records.
Jim
 
John,
Thanks so much for your help.
Jim

1) Do you have a primary key that would be the same in both tables? In other
words, how do you identify that a record in tablea is a match for a record in
TableB? If you do this by SSN, then something like the following.

SELECT TableA.*
FROM TableA LEFT Join TableB
ON TableA.SSN = TableB.SSN
WHERE TableB.SSN Is Null

2) SELECT TableB.*
FROM TableB Inner Join TableA
ON TableB.SSN = TableA.SSN
WHERE NZ(TableB.FieldA) <> NZ(TableA.FieldA) OR
NZ(TableB.FieldB) <> NZ(TableA.FieldB) OR
NZ(TableB.FieldC) <> NZ(TableA.FieldC) OR
...
Click to expand...
 
Terri,
Thanks so much for your help
Jim

I created a TableA and TableB with columns SSN, Name, Age

Here are the queries I came up with for your scenarios.

1. SELECT TableA.*
FROM TableA LEFT JOIN TableB ON TableA.SSN = TableB.SSN
WHERE (((TableB.SSN) Is Null));

2. SELECT TableA.SSN, TableA.Age, TableB.Age,
TableA.Name, TableB.Name
FROM TableA INNER JOIN TableB ON TableA.SSN = TableB.SSN
WHERE ((([tablea].[name]=[tableB].[name])=False)) OR
((([tablea].[age]=[tableb].[age])=False));

Thanks
Terri
-----Original Message-----
Greetings all,

We're changing over to a new data system and I need to compare a table
generated in the old system with a table generated by the new system.
I want to create two queries that:

1) Indicate the records in table A (old system) that don't appear in
table B (new system)

2) Indicate the records in Table B wherein the SSN (field 8) is equal,
but the one or more of the remaining 10 fields in the record aren't
the same.

TIA,
Jim
.
Click to expand...
Click to expand...
 
Back
Top