You have to have some way to identify which records should match which
records - that can be multiple fields and does not have to include a primary
key.
For example the following would work.
SELECT A.*
FROM SomeTable as A LEFT JOIN AnotherTable As B
ON A.LastName = B.LastName
AND A.FirstName = B.FirstName
AND A.ZipCode = B.ZipCode
WHERE B.LastName is Null
There might be problems in records being returned where there is a match if
any of the fields can be null. For instance if ZIPCode is null in both
records and the name fields match you will still get the A.Record returned
as a non-match. That can be handled by changing the join to force values in
the fields where nulls are present. Something like the following:
SELECT A.*
FROM SomeTable as A LEFT JOIN AnotherTable As B
ON A.LastName & "" = B.LastName & ""
AND A.FirstName & "" = B.FirstName & ""
AND A.ZipCode & "" = B.ZipCode & ""
WHERE B.LastName is Null
--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
