Query returning too many lines - IIF / IN statement?

[natalie99]

Hi everyone

I have a small problem, which is making it impossible to acheive my db aims!

I would like to know how to write a query that will tell me if a field item is in IN another table, without returning the value. i.e. returning a YES or NO instead of the matching record.

The second data set will have multiple recurrences of each record if it IS present in the data, so if I use an outer join I get extra data returned which I dont want!!

All i want to do is something along the lines of

SELECT Table1.[ID] FROM Table1
IIf((Table1.[ID] In Table2.[ID]), "YES", "NO");


This will need to be done for 8 secondary tables, resulting in a list with ID, and then 8 yes or nos showing which other tables it is present in.

So far, this isn't working, and I need to resolve this fast!

Please someone point out my mistake :)

thanks everyone

Nat

[cori25]

SELECT IIf([table1]![ID]=[table2]![ID],"Yes","No")
FROM table1, table2;

[FishVal]

[code=sql]
SELECT Table1.[ID], IIf((IsNull(Tab le2.[ID]), "NO", "YES") FROM Table1 LEFT JOIN Table2 ON Table1.[ID]=Table2.[ID];
[/code]

Regards,
Fish

[natalie99]

 SELECT qryUnmatch_BT_Z.[Primary Circuit ID], IIf((IsNull(tblLIVE.[Circuit Id])),"NO","YES") AS Expr1
FROM qryUnmatch_BT_Z LEFT JOIN tblLIVE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblLIVE.[Circuit Id];

FISH!!

Thank you, yours is the only query to not return duplicates!!

Cori, thanks for your suggestion, however, I'm not sure of the reason but Access crashes each time I try to run your code.

Fish I have one question about your code, how do I add more expressions in to test the other tables in the same query? i tried this:

SELECT qryUnmatch_BT_Z.[Primary Circuit ID], IIf((IsNull(tblLIVE.[Circuit Id])),"NO","YES") AS LIVE, IIf((IsNull(tblBASE.[Circuit Id])),"NO","YES") AS BASE
FROM qryUnmatch_BT_Z LEFT JOIN tblLIVE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblLIVE.[Circuit Id], qryUnmatch_BT_Z LEFT JOIN tblBASE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblBASE.[Circuit Id];

But it doesn't work, any idea why?

THANKS SO MUCH!!!

Nat :)

[natalie99]

oooh got it!

SELECT qryUnmatch_BT_Z.[Primary Circuit ID], IIf((IsNull(tblLIVE.[Circuit Id])),"NO","YES") AS LIVE, IIf((IsNull([tblBASE].[Circuit Id])),"NO","YES") AS BASE
FROM (qryUnmatch_BT_Z LEFT JOIN tblLIVE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblLIVE.[Circuit Id]) LEFT JOIN tblBASE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblBASE.[Circuit ID];

Thanks again Fish

[FishVal]

You are welcome.
Good luck.