How to exclude items from one table to other

[Manuel Indacochea via AccessMonster.com]

Table-1 lists Plant Codes, Buyer Codes, Material Codes and other
information about items purchased in one month, Table-2 contains Plant
Codes, Plants Names, Buyer Code and Material Codes. What I need to do is: a)
Match Plant Codes and assign Plant names from Table-2 to Table-1 (this is
the easy part - already done) and b) exclude Material Codes listed in Table-
2 from Table-1, but also matching Plant Codes and Buyer Codes. (this is the
difficult part that I need help with)

Question is:
Can this be done with one single query or do I need 2 separates queries for
each step?...

I will appreciate any help regarding this.

 

[JohnFol]

Without reproducing the data on my machine I can't see if the " match Plant
Codes and Buyer Codes" is part of the criteria for the exclusion, or the
criteria for the insert. Eitherway, have a look at sub-selects:

ie Where Table1.MaterialCodes Not in (Select MaterialCodes from Table2)

To see this work, add a new employee to NWind and run this to exclude all
employees in another table.

SELECT Employees.EmployeeID, Employees.*
FROM Employees
WHERE (((Employees.EmployeeID) Not In (Select employeeid from orders)));

 

[Manuel Indacochea via AccessMonster.com]

Thanks for the advise, though, please check the following example:

FROM (table-1)
Location Plant Code Employee Material
A 123 1X K1251
B 456 5Y L6822
A 456 2Z K1251
A 789 3Z M3211
B 123 1Z K2180

EXCLUDE IF (table-2)
Location Plant Code Employee Material
A 123 1X K1251
B 123 1Z K2180
A 789 3Z M3211

In order to have the result, need to match Location, Plant Code and
Employee (on both tables) and exclude Materials listed on table-2. This is
because a material can repeat in another Plant or location.

 

[JohnFol]

This is then a simple query with both tables, linked on the 3 fields.