How Do I compare differences between the data in two tables?

[Guest]

I am trying to create a Diff (or Delta) query that will return only data that
is different between two tables. All the fields are identical between the
two tables and I am using a field such as SSN as the primary key. I want a
query to return a line if Joe Schmoe's address is different in one table from
the next, or if Jane Doe exists in the new table but not the old (added), or
if John Doe exists in the old table but not the new (deleted).

 

[Rick B]

If the structure is identical, why do you have two tables? I'd recommend
normalizing your design before you go any further.

 

[SusanC]

I have a similar problems where the structure isn't identical. Then
how do you do it?

 

[Rick B]

If the structure is different, then what are you comparing?

You must have some similar fields in the tables that you wish to compare.

Details please...

 

[Vincent Johns]

I am trying to create a Diff (or Delta) query that will return only data that
is different between two tables. All the fields are identical between the
two tables and I am using a field such as SSN as the primary key. I want a
query to return a line if Joe Schmoe's address is different in one table from
the next, or if Jane Doe exists in the new table but not the old (added), or
if John Doe exists in the old table but not the new (deleted).

The "Find Unmatched Query Wizard" will give you the (added) and
(deleted) lists, though you'll have to use it twice, once for each
Table. For differences between records that are present in both copies,
use something like this:

SELECT Copy1.Address, Copy2.Address
FROM Copy1 INNER JOIN Copy2
ON Copy1.SSAN = Copy2.SSAN
WHERE (((Copy2.Address)<>[Copy1].[Address]))
ORDER BY Copy1.Address;


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Guest]

Vincent:

Thanks. It has been a while since I posted my question and have since found
a workaround. Very similary to what you suggested: 3 queries that are then
joined together by a 4th (make table query).

Matt

I am trying to create a Diff (or Delta) query that will return only data that
is different between two tables. All the fields are identical between the
two tables and I am using a field such as SSN as the primary key. I want a
query to return a line if Joe Schmoe's address is different in one table from
the next, or if Jane Doe exists in the new table but not the old (added), or
if John Doe exists in the old table but not the new (deleted).

The "Find Unmatched Query Wizard" will give you the (added) and
(deleted) lists, though you'll have to use it twice, once for each
Table. For differences between records that are present in both copies,
use something like this:

SELECT Copy1.Address, Copy2.Address
FROM Copy1 INNER JOIN Copy2
ON Copy1.SSAN = Copy2.SSAN
WHERE (((Copy2.Address)<>[Copy1].[Address]))
ORDER BY Copy1.Address;


-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.

 

[Vincent Johns]

Vincent:

Thanks. It has been a while since I posted my question and have since found
a workaround. Very similary to what you suggested: 3 queries that are then
joined together by a 4th (make table query).

Matt

Well, I hope I helped. I'm happy you found a solution. It may have
taken some time, but my guess is that, now that you've solved it, this
kind of problem won't give you much trouble in the future.

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.