Records that aren't in the union relationship

[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

 

[Les]

Sounds like you need an unmatched query. There is a query
wizard to accomplish this.

 

[John Vinson]

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?

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.