Query based on 2+ conditions between 2 tables

[Runawaygeek]

Hi Guys,

How would i go about the following Statement as a Query.

IF tbl1.ID = tbl2.ID AND tbl.1.Customer=tbl2.Customer THEN "y" ELSE "NO"
END

I tried the following in Expression Builder

iff(tbl1.ID=tbl2.ID,"y","n") just to see if it would flag the rows from table 1 as being present in table 2, but it duplicated everything and returned 600K rows.
Table 1 is 70 rows and table 2 is 95K. This would be a very simple Vlookup in excel??

Thanks,

 

[Minty]

Just join tbl1.ID and tb2.ID with a outer / left join (Double click the join arrow) and put Is Null in the criteria underneath tbl2.ID this will only return records in tbl1 where there is no matching record in tbl2.

 

[Runawaygeek]

Just join tbl1.ID and tb2.ID with a outer / left join (Double click the join arrow) and put Is Null in the criteria underneath tbl2.ID this will only return records in tbl1 where there is no matching record in tbl2.

Hi

thanks for getting back to me, what if tbl1.ID IS on tbl.2, but the tbl.1Customer is NOT on tbl.2. I would not see this as the first filter would have removed the ID and i wont know that i need to supply to that customer, it seems queries in Access are limited to 1 task at a time.

 

[MarkK]

This is a totally valid boolean expression as is...

tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer

In the query design grid just add the field name you want to use in front . . .

IsMatched: tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer

... or ...

IsMatched: Format(tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer, "Yes/No")

 

[Runawaygeek]

This is a totally valid boolean expression as is...

tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer

In the query design grid just add the field name you want to use in front . . .

IsMatched: tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer

... or ...

IsMatched: Format(tbl1.ID = tbl2.ID AND tbl1.Customer=tbl2.Customer, "Yes/No")

Thank you for this, i was clearly over complicating things, using the iif
the format does not work, keep thinking its days of the year but thats ok, i can work with its native -1 or null output.

Thanks.