Exclude Record if Text Found in another Field

[screech]

Greetings all, I apologize in advance if this has been asked and answered before but my searching has not been successful.

I am attempting to create a query in Access. I have two fields in a table. Both fields contain text. I am trying to write a query that returns all the records for Field1 where the value for Field1 is not found anywhere in Field2.

For example:

Field1       Field2
Apples       Red
Apples       Green
Bananas      Yellow
Carrots      Orange
Fruit        Apples
Fruit        Bananas
Vegetable    Carrots

I would like the query to return the Field1 records:

Fruit
Vegetable

and exclude Apples, Bananas, and Carrots are found in Field2.

I thought that I can add a criteria to Field1 that says something like: Not [Field2] but I can't get the right criteria expression. Can you please help with the proper expression?

 

[Joe4]

That is an odd structure (I have a feeling it may not be a normalized table). You would normally expect them to be in different tables instead instead of different fields within the same table.

However, you could do what you want like this:

SELECT DISTINCT Field1
FROM TableName
WHERE Field1 Not In 
(SELECT Field2 
FROM TableName);

 

[screech]

That is an odd structure (I have a feeling it may not be a normalized table). You would normally expect them to be in different tables instead instead of different fields within the same table.

However, you could do what you want like this:

SELECT DISTINCT Field1
FROM TableName
WHERE Field1 Not In 
(SELECT Field2 
FROM TableName);

Yes, it's definitely an odd structure. Parent items (Field1) have Child items (Field2) but then some of those Child items are also in Field1 since they are parents of other Child items in Field2... not simple to work with. Thank you for your solution -- it appears to be working for me. I appreciate the help.