excluding records based on 2 exclusion tables - subtracting queries?

[BeanDog]

I have a table that lists the UPCs that a user does not wish to see displayed. I also have a table that lists the UPCs' first 6 digits they do not want displayed, to exclude whole groups at once. Queries to list each one would be as follows:

SELECT * FROM tblExcludedUPC WHERE agentID=3
SELECT * FROM tblExcludedUPCGroup WHERE agentID=3

Another table lists those upcs. For instance,

SELECT * FROM tblItemByUPC

How would I build a query that would exclude the first queries from the latter one? Like:

SELECT * FROM tblItemByUPC
INNER NEGATIVE JOIN tblExcludedUPC.upc=tblItemByUPC.upc INNER NEGATIVE JOIN tblExcludedUPCGroup.upcprefix=LEFT(tblItemByUPC.upc, 6)
WHERE (tblExcludedUPC.agentID=3 AND tblExcludedUPCGroup.agentID=3)

Do you know what I mean? How would I pull this off? [sig][/sig]

 

[mvanbro]

select * from tblitembyupc where upccode not in
(select * from tblexcludedupc where agentid=3)
and not in
(select * from tblexcludedupcgroup where agentid = 3)

this syntax may not be precise but it is close to what will work


[sig][/sig]