Excluding a list of items in a query (1 Viewer)

[John liem]

I have a Tbl_Product (4000 items) and want to run a query which exclude a list of products (223 items), I put the list as a table Tbl_Excl.
How can I do this in an easy way?
I am using Access 2000 and quite familiar with SQL. Thanks in advance, John

 

[pbaldy]

Fairly simple, unless I've misunderstood:

SELECT ItemField FROM Tbl_Product
WHERE ItemField NOT IN (SELECT ItemField FROM Tbl_Excl)

 

[Pat Hartman]

Jet doesn't optimize subselects well and these queries can take excessive amounts of time. You can use a left join to accomplish the same results.

Select ....
From tblA Left Join tblB on tblA.key = tblB.key
Where tblB.key Is Null;

The left join returns all the rows from tblA and any matching rows from tblB (the exclude list). The Is Null criteria includes only tblA rows that did not find a match in tblB.

 

[John liem]

Hi Paul, Hi Pat,

Thanks for your great help .. it is easy (if you know it).
John.