473,322 Members | 1,241 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,322 software developers and data experts.

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
Mar 17 '08 #1
5 1993
cori25
83
SELECT IIf([table1]![ID]=[table2]![ID],"Yes","No")
FROM table1, table2;
Mar 17 '08 #2
FishVal
2,653 Expert 2GB
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];
  2.  
Regards,
Fish
Mar 17 '08 #3
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]; 
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:

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?

THANKS SO MUCH!!!

Nat :)
Mar 18 '08 #4
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];
  3.  
Thanks again Fish
Mar 18 '08 #5
FishVal
2,653 Expert 2GB
You are welcome.
Good luck.
Mar 18 '08 #6

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

Similar topics

8
by: Rigga | last post by:
Hi, I am new to mysql and need help on how to join tables. I have a database which contains 4 tables, the main table contains information by date order and the other 3 contain data also in date...
6
by: pointBoarder | last post by:
Thanks in advance to all who read this. I've got 3 tables which were created from a txt file dumped from some old system. Header ID -- autonumber, primary OrderNum -- field I want Line
12
by: Bookreader | last post by:
I tried Googling this but I get a whole lot of replies about running the SQL statement in VB via ADO. All I want to do is run an existing ACCESS 2000 query from VB with no information returned...
3
by: Sim Zacks | last post by:
I am using 8.0 beta 1 on an RH 8 Linux server. I have a union query that I am converting from access (where it worked) and it is returning duplicates. The only difference between the two rows is...
6
by: jjturon | last post by:
Can anyone help me?? I am trying to pass a Select Query variable to a table using Dlookup and return the value to same select query but to another field. Ex. SalesManID ...
4
by: Macroman | last post by:
MS Access XP, running on Win XP, Processor 2.4Ghz , 512Mb RAM, 40Gb Hard drive Table 1 has 167,000 records and contains the following fields tblone_custID tblone_easting tblone_northing ...
0
southoz
by: southoz | last post by:
Good ay all , I'm fairly new to access(a little over 5 weeks now). Since I'v started I have picked up a lot of useful information from forums such as this and in doing so will share that information...
6
markmcgookin
by: markmcgookin | last post by:
Hi Folks, I am running a simple query using VB (This isnt a VB Question, dont worry!) on SQL Server Compact. I have the query below being created, and then added to if a flower location doesn't...
0
by: RCapps | last post by:
When running the below SQL Query I keep getting the following error: Server: Msg 4924, Level 16, State 1, Line 1 ALTER TABLE DROP COLUMN failed because column 'ContractDef' does not exist in table...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.