table a not in table b

[iren]

In other software it is quite simple to identify rows in table A not in table
B in the sense of n variables (n, an integer). But it is difficult in Access
database when n>1 because left/right join with many variables of join would
not work. What is your idea?

iren

 

[John Spencer]

How many fields are needed to identify rows that are in table a and not
in table b.

You can use multiple fields in the join and it is pretty simple to do in
SQL view

SELECT TableA.*
FROM TableA as A LEFT JOIN TableB as B
On A.Field1 = B.Field1
AND A.Field2 = B.Field2
AND A.Field3 = B.Field3
AND A.Field4 = B.Field4
WHERE B.Field1 is Null

Now the problem does occur if field1 in both tables is null and the
other three fields have matching values. In that case the record in
TableA would be returned erroneously. That can be handled in the join
in a couple of ways.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
'====================================================