record finding query help needed 1

[irethedo]

I have a reference table with two fields,
one field is a category and the other field is an item that fits within that category.

For Example my reference table may appear as follows:

Category Item
-------- -----
Color Red
Shape circle
Color Green
Shape square

Is there a way to create a query to check another table full of data and return a list of any records in that table that may have the category as listed in the reference table but containing an item that is not in the reference table listed for that category?


The table that I want to check may appear as follows:

Category Item
-------- -----
Color Red
Shape circle
Color Yellow
Shape Triangle

and the query would display the two records:


Category Item
-------- -----
Color Yellow
Shape Triangle

What is the easiest way to do this and can it be done with a query without writing code to check the data table?

Thanks in advance

 

[Remou]

Perhaps:
[tt]SELECT Table4.Category, Table4.Item
FROM Table3 RIGHT JOIN Table4 ON (Table3.Item = Table4.Item) AND (Table3.Category = Table4.Category)
WHERE (((Table3.Category) Is Null) AND ((Table3.Item) Is Null));[/tt]

Where table3 is the reference table and table4 is the table to be compared.

 

[irethedo]

Thanks Remou-

That works great!