Query to find differences in a record

K

kayabob

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.
 
K

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;
 
J

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top