Records that aren't in the union relationship

  • Thread starter Thread starter Derek Richardson
  • Start date Start date
D

Derek Richardson

Table 1 contains column A which is a global list of part
numbers.

Table 2 contains column B which is a subset list of part
numbers.

I want to view all the records in table 1 that have part
numbers that are not in the subset defined in table 2.

How can I create a query to do that?

Derek
 
Table 1 contains column A which is a global list of part
numbers.

Table 2 contains column B which is a subset list of part
numbers.

I want to view all the records in table 1 that have part
numbers that are not in the subset defined in table 2.

How can I create a query to do that?
Click to expand...

The "Unmatched Query Wizard" will do this, or you can roll your own
query using what I call a "frustrated outer join".

Create a query joining Table1 to Table2, joining ColumnA to ColumnB.
This will show all those which *do* match.

Select the Join line and change its join type to Left Outer Join -
"show all records in Table1 and matching records in Table2". This will
show all the parts, whether they're in the subset or not.

Finally put a criterion on Column B of

IS NULL

to restrict the list to those which do NOT match.
 
Back
Top