By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,098 Members | 1,918 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,098 IT Pros & Developers. It's quick & easy.

Query returning too many lines - IIF / IN statement?

P: 41
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

Mar 17 '08 #1
Share this Question
Share on Google+
5 Replies

P: 83
SELECT IIf([table1]![ID]=[table2]![ID],"Yes","No")
FROM table1, table2;
Mar 17 '08 #2

Expert 2.5K+
P: 2,653
Expand|Select|Wrap|Line Numbers
  1. SELECT Table1.[ID], IIf((IsNull(Table2.[ID]), "NO", "YES") FROM Table1 LEFT JOIN Table2 ON Table1.[ID]=Table2.[ID];
Mar 17 '08 #3

P: 41
Expand|Select|Wrap|Line Numbers
  1.  SELECT qryUnmatch_BT_Z.[Primary Circuit ID], IIf((IsNull(tblLIVE.[Circuit Id])),"NO","YES") AS Expr1
  2. FROM qryUnmatch_BT_Z LEFT JOIN tblLIVE ON qryUnmatch_BT_Z.[Primary Circuit ID] = tblLIVE.[Circuit Id]; 

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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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?


Nat :)
Mar 18 '08 #4

P: 41
oooh got it!

Expand|Select|Wrap|Line Numbers
  1. 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
  2. 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
Mar 18 '08 #5

Expert 2.5K+
P: 2,653
You are welcome.
Good luck.
Mar 18 '08 #6

Post your reply

Sign in to post your reply or Sign up for a free account.