Un matched query on more than one field

Gary, Thank you so much, it worked beautifully. I had
fiddled around in SQL but didn't really know what I was
doing. Your reply helped me understand a bit about basic
SQL syntax. Thanks again for taking the time to reply.

Mary

-----Original Message-----

"
I came to this board for assistance with essentially the
same issue. Two tables each with three fields LN FN
and ID

ID is a primary key in both tables.

There are records that are common to both tables, some
records in both tables that are not in the other.

I only want to work with the subset of records that is
common to both tables based in the ID field.

Once I have those records I only want to see the records
where the ID's are the same but the LN field is
different.

In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do
not.

Hi m,

Did you try bringing both tables into a query,
join on the 2 fields, go into SQL view where
you might have something like:

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN = tbl2.LN;

and change "=" to ""

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN tbl2.LN;

Please respond back if I have misunderstood.

Good luck,

Gary Walter


.

In english, what I want to do is compare two tables and
find those records where the ID's match but the LN's do
not.

Hi Mary,

There was one aspect to the solution below
that I had not thought about until I just
read another post from Michel.

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
AND
tbl1.LN tbl2.LN;

It has to do with the final condition.

If either (or both) LN could be Null,
the above SQL would not return those
records.

LN Null = Null
Null LN = Null
Null Null = Null

Michel's solution was to move this
condition to the WHERE clause and
use NZ:

SELECT
tbl1.*,
tbl2.*
FROM
tbl1 INNER JOIN tbl2
ON
tbl1.ID = tbl2.ID
WHERE
NZ(tbl1.LN tbl2.LN, -1);

It will include where "Null=Null" though,
but will catch where one is null, so they
"do not match."

If you do not want to return "Null=Null"
case, I guess you could include second
test in WHERE clause

WHERE
NZ(tbl1.LN tbl2.LN, -1)
AND NOT
(tbl1.LN IS NULL
AND
tbl2.LN IS NULL);

One more thing learned.

Thank you Michel.


Gary Walter