How to find if data is in a column or another?

[saharka]

Hello people i'm very new to access.


This is the thing: I have to know if data is contained in a column of one of three queries. In excel I would do this by a combination of ifs, iserror and match or ifs and vlookups, but I have failed miserably on access.

This is an example of what I want:



This is how I would do it in excel:

Any ideas on how to do this inside a query?

thanks

[aytee111]

Could you tell us in more detail what the no's and yes's represent - 1 8 5 no means that 1 doesn't exist anywhere else, 8 doesn't exist anywhere else, 5 ?

[saharka]

I just noticed that the text editor did not respect my spaces I will try to fix this.

No means that the number in the column "Code (main)" does not exist in the columns "Query 1" or "Query 2"
Yes means that the number in the column "Code (main)" exists in the columns "Query 1" or "Query 2"

For example 1 is a no because 1 it does not exist in the columns Query 1 or Query 2, 4 is a yes because it exists in Query 1 even if it does not exist in Query 2 and 3 is a yes because it exists in both Query 1 and 2.

[aytee111]

Create a new query that brings in all three queries. Left join the main query to the same field in the other two (right-click on the join > join properties > select all from main table). Then add a new field in the query for your yes/no (change Field1 to the name of your field):

IIf(IsNull([query2].[Field1]),IIf(IsNull([query3].[Field1]),"no","yes"),"yes")

[saharka]

Ohh great, I will try it out thanks!