Query to find differences in a record

[Guest]

I want to make a query to find the differences in various fields for
matching records. For example, I have two tables with SSN as the PK. I know
that the SSN's make a one to one match from table 1 to table 2, but what I
want to find are differences for a single SSN where on table 1 the FirstName
is Chuck, but on table 2 the first name is Charles. I have to check for the
differences for 45 fields (addresses, phones, etc etc). I tried something
like this:
SELECT tbl1.[field 1]
FROM tbl1, tbl2
WHERE (((tbl1.[field 1])<>[tbl2]![field 2]));

but I cant get it to work for multiple fields combining with OR. Plus this
seems too hard to do for 45 fields. Any suggestions of any easy way to do
this would be appreciated.

Thanks.

 

[Ken Snell \(MVP\)]

Something like this, perhaps:


SELECT tbl1.SSN, tbl1.FirstName,
tbl2.FirstName
FROM tbl1 INNER JOIN tbl2
ON tbl1.SSN = tbl2.SSN
WHERE tbl1.FirstName<>tbl2.FirstName;

 

[John Spencer]

There is no easy way that I know of. You have to compare each set of fields.
You are probably better off doing this in small increments unless you have a
report with vba code to highlight the differences.

SELECT Tbl1.SSN, Tbl1.FirstName, Tbl2.FirstName, ...
FROM Tbl1 INNER JOIN Tbl2
ON Tbl1.SSN = Tbl2.SSN
WHERE tbl1.FirstName <> tbl2.FirstName OR
tbl1.LastName <> Tbl2.LastName OR
....

In addition, you have to worry about Nulls in your fields. So if
tbl1.MiddleName is blank and tbl2.MiddleName is "Phil", you won't get a record
returned. Unless you modify the Where clause to tbl1.MiddleName & "" <> tbl2.Middlename