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
5 1993
SELECT IIf([table1]![ID]=[table2]![ID],"Yes","No")
FROM table1, table2;
-
SELECT Table1.[ID], IIf((IsNull(Table2.[ID]), "NO", "YES") FROM Table1 LEFT JOIN Table2 ON Table1.[ID]=Table2.[ID];
-
Regards,
Fish
- 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 :)
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
You are welcome.
Good luck.
Sign in to post your reply or Sign up for a free account.
Similar topics
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...
|
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
|
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...
|
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...
|
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 ...
|
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
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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)...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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
|
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...
|
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...
| |