473,835 Members | 1,778 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Multiple Sub queries in a SELECT statement

Ben
I believe I am missunderstandi ng how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.

Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3

Table B
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
4 D Y 4
5 E W 5
6 F V 6
7 G U 7
8 A Z 8

When I run the query I should get the following results:
First Last Other
D Y 4
E W 5
F V 6
G U 7
A Z 8

But I get
First Last Other
E W 5
F V 6
G U 7

In other words I want to select the information from Table B that do
not match all 3 fields in Table A. 1 or 2 fields may match but not all
3. When there is only 1 matching field it won't select it.

SELECT [First], [Last], [Other] FROM [b] WHERE [First] NOT IN
(SELECT [First] FROM [A]) AND [Last] NOT IN (SELECT [Last] FROM [A])
AND [Other] NOT IN (SELECT [Other] FROM [A]);

I will be using this in VBA. Also I was wondering if this is
variation of looking for duplicates in two tables but instead of
hiding the duplicates I want them to be selected.
Nov 13 '05 #1
4 10331
You want the records from table B that do not have a match in table A, based
on a combination of all 3 fields.

The most efficient approach might be an outer join query, joined on 3
fields.

1. Create a query into table B and table A.

2. In the upper pane of the query design window, drag:
- B.First to A.First
- B.Last to A.Last
- B.Other to A.Other
You now see 3 join lines between the tables.

3. Double-click the first join line.
Access offers a dialog with 3 choices.
Choose:
All records from B, and any matches form A.
Access puts an arrow-head on the join line.

4. Repeat step 3 for the other two fields.
All 3 join lines now have arrow heads pointing the same direction.

5. Drag the primary key from table A into the grid.
In the Criteria row under this field, enter:
Is Null

You have asked for:
- All records from table B (the outer join);
- Join to table A to get the matches on the 3-field combination;
- Return only the records where table A doesn't have a match.

From the View menu, choose SQL View.
You now have an example SQL statement to copy into your VBA code.

It would be possible to do it with 3 subqueries, but much less efficient.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ben" <be****@hotmail .com> wrote in message
news:bd******** *************** ***@posting.goo gle.com...
I believe I am missunderstandi ng how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.

Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3

Table B
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
4 D Y 4
5 E W 5
6 F V 6
7 G U 7
8 A Z 8

When I run the query I should get the following results:
First Last Other
D Y 4
E W 5
F V 6
G U 7
A Z 8

But I get
First Last Other
E W 5
F V 6
G U 7

In other words I want to select the information from Table B that do
not match all 3 fields in Table A. 1 or 2 fields may match but not all
3. When there is only 1 matching field it won't select it.

SELECT [First], [Last], [Other] FROM [b] WHERE [First] NOT IN
(SELECT [First] FROM [A]) AND [Last] NOT IN (SELECT [Last] FROM [A])
AND [Other] NOT IN (SELECT [Other] FROM [A]);

I will be using this in VBA. Also I was wondering if this is
variation of looking for duplicates in two tables but instead of
hiding the duplicates I want them to be selected.

Nov 13 '05 #2
the problem is that the WHERE clause is checking one field at a time
(first, then last then other)

you want to compare the row as a set of 3 fields, so something like
would work
assuming you have no dashes in your data

SELECT first, last, other
FROM tblB
WHERE ((([first] & "-" & [last] & "-" & [other]) Not In (select
[first] & "-" & [last] & "-" & [other] from tblA)));
be****@hotmail. com (Ben) wrote in message news:<bd******* *************** ****@posting.go ogle.com>...
I believe I am missunderstandi ng how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.

Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3

Table B
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
4 D Y 4
5 E W 5
6 F V 6
7 G U 7
8 A Z 8

When I run the query I should get the following results:
First Last Other
D Y 4
E W 5
F V 6
G U 7
A Z 8

But I get
First Last Other
E W 5
F V 6
G U 7

In other words I want to select the information from Table B that do
not match all 3 fields in Table A. 1 or 2 fields may match but not all
3. When there is only 1 matching field it won't select it.

SELECT [First], [Last], [Other] FROM [b] WHERE [First] NOT IN
(SELECT [First] FROM [A]) AND [Last] NOT IN (SELECT [Last] FROM [A])
AND [Other] NOT IN (SELECT [Other] FROM [A]);

I will be using this in VBA. Also I was wondering if this is
variation of looking for duplicates in two tables but instead of
hiding the duplicates I want them to be selected.

Nov 13 '05 #3
when ID=4(in Table B) AND ID=2(in Table A),Their Last are the same as "Y" ,
So be excluded
when ID=8(in Table B) AND ID=1(in Table A),Their First are the same as "A"
, So be excluded
The result is Ok.

"Ben" <be****@hotmail .com> 撰寫於郵件新聞
:bd************ **************@ posting.google. com...
I believe I am missunderstandi ng how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.

Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3

Table B
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
4 D Y 4
5 E W 5
6 F V 6
7 G U 7
8 A Z 8

When I run the query I should get the following results:
First Last Other
D Y 4
E W 5
F V 6
G U 7
A Z 8

But I get
First Last Other
E W 5
F V 6
G U 7

In other words I want to select the information from Table B that do
not match all 3 fields in Table A. 1 or 2 fields may match but not all
3. When there is only 1 matching field it won't select it.

SELECT [First], [Last], [Other] FROM [b] WHERE [First] NOT IN
(SELECT [First] FROM [A]) AND [Last] NOT IN (SELECT [Last] FROM [A])
AND [Other] NOT IN (SELECT [Other] FROM [A]);

I will be using this in VBA. Also I was wondering if this is
variation of looking for duplicates in two tables but instead of
hiding the duplicates I want them to be selected.



Nov 13 '05 #4
Ben
"hwangtw" <hw*****@seed.n et.tw> wrote in message news:<cc******* ***@news.seed.n et.tw>...
when ID=4(in Table B) AND ID=2(in Table A),Their Last are the same as "Y" ,
So be excluded
when ID=8(in Table B) AND ID=1(in Table A),Their First are the same as "A"
, So be excluded
The result is Ok.

"Ben" <be****@hotmail .com> 撰寫於郵件新聞
:bd************ **************@ posting.google. com...
I believe I am missunderstandi ng how subqueries work. I simple
subquery works fine but when I wish do compare 2 or more fields at
once I don't get the results I wish.

Table A
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3

Table B
ID First Last Other
1 A Z 1
2 B Y 2
3 C Z 3
4 D Y 4
5 E W 5
6 F V 6
7 G U 7
8 A Z 8

When I run the query I should get the following results:
First Last Other
D Y 4
E W 5
F V 6
G U 7
A Z 8

But I get
First Last Other
E W 5
F V 6
G U 7

In other words I want to select the information from Table B that do
not match all 3 fields in Table A. 1 or 2 fields may match but not all
3. When there is only 1 matching field it won't select it.

SELECT [First], [Last], [Other] FROM [b] WHERE [First] NOT IN
(SELECT [First] FROM [A]) AND [Last] NOT IN (SELECT [Last] FROM [A])
AND [Other] NOT IN (SELECT [Other] FROM [A]);

I will be using this in VBA. Also I was wondering if this is
variation of looking for duplicates in two tables but instead of
hiding the duplicates I want them to be selected.


Thanks guys

At the moment I will go with this:
strSQL = "INSERT INTO [_A] ([First], [Last], [Other]) " _
& "SELECT [First], [Last], [Other] " _
& "FROM [_B] " _
& "WHERE ((([First] & ' ~ ' & [Last] & ' ~ ' & [Other]) " _
& "NOT IN (SELECT [First] & ' ~ ' & [Last] & ' ~ ' & [Other] from
[_A])));"

Does exactly what I want. I just have to put the production names and
such in and I am good to go. Changed the hyphens to tildi because of
some hyphens in the database. Works fine.

Allen I plan to work with your suggestion also. Just having a problem
with it. Missing something simple due to being over tired. If I
continue to have problems I'll post later.

Thanks again.
Nov 13 '05 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
6221
by: Rick Caborn | last post by:
Does anyone know of a way to execute sql code from a dynamically built text field? Before beginning, let me state that I know this db architecture is built solely for frustration and I hope to make it better soon. Unfortunately, there is never a non-crucial time in which we can do an upgrade, so we are stuck for now. Point 1: There are multiple tables: students, courses, cross-reference
4
16761
by: DG | last post by:
Hi, Can anyone advise how to execute multiple statements in a single query batch. For example- update customers set customer_name = 'Smith' where customer_name = 'Smyth'; select * from customers; I can execute each statement individually but get the 'you have an error in
5
4928
by: Barn Yard | last post by:
good morning, im still kind of new to VBA but I have learned some interesting things so far. Right now I am normalizing my survey database so I'm having to run an append query for each question(300 in some surveys). WARNING: I've very new at this so this may not look right. i hope it gives the idea of what I'm trying to accomplish. Before I was just chaning the 1's to 2's then to 3's in the Design view of my query. hope this can save...
3
3481
by: tesc | last post by:
I am so aggravated and need any help I can get. I am using Access 2000 and am trying to sort multiple fields in a select query. My query is set up as follows: FIELD 1 FIELD 2 FIELD 3 FIELD4 FIELD 5 FIELD 6 MSA03 MSA 02 % change ICU 03 ICU 02 % change calc. calc. This format continues and in some queries I have up to 13 percentages to sort. I need to sort each percentage to show over 20%...
11
4541
by: dskillingstad | last post by:
I've been struggling with this problem for some time and have tried multiple solutions with no luck. Let me start with, I'm a novice at Access and I'm not looking for someones help to design my database,just help in getting me pointed in the right direction. I have a database with 8 tables, which from what I have read, cannot be linked on a single form, and be updatable. I have created a query which includes all 8 tables, and then...
2
2990
by: deko | last post by:
Can I select from multiple tables/queries in a single SQL statement? For example: SELECT , , FROM qryThis, qryThat, tblOtherThing Does this work only with Jet, or does SQL just work like this? The specific problem I'm trying to solve is compiling a RecordSource for a
7
2739
by: Daz | last post by:
Hi. I am trying to select data from two separate MySQL tables, where I cannot use join, but when I put the two select queries into a single query, I get an error telling me to check my syntax. Both of the queries work fine when I use them to query the MySQL server directly. My guess is that the MySQL extension only expects a single resource back from the database, but get's several, or that it just checks the statement first, and decides...
2
13564
by: chrisale | last post by:
Hi All, I've been racking my brain trying to figure out some sort of Sub-Select mySQL statement that will create a result with multiple rows of averaged values over a years time. What I have is weather data. There is a new record every 5 minutes, every day. So. What I want to do with one SQL statement is figure out the Average of those 5 minute records over each day, for every day of the year.
7
20339
by: =?Utf-8?B?QVRT?= | last post by:
HOWTO Run multiple SQL statements from ASP/ADO to an Oracle 10g. Please help, I'm trying to write an ASP page to use ADO to run a long query against an Oracle 10g database, to create tables, if they do not already exist. In terms of ASP/ADO, that would be fine in a SQL Server Sense by a simply ASP/Server-Side JavaScript as such: var cnTemp = Server.CreateObject("ADODB.Connection");
1
2647
by: ll | last post by:
Hi all, I've inherited a site and am currently looking to redesign a page that displays a table of course curriculum, with each row representing a "Topic" of the curriculum. There is a courseID that exists throughout the site, identifying each course. Within each row of the aforementioned table, there are 4 columns, consisting of MainTopics, their associated SubTopics, uploaded LectureNotes for that MainTopic, and finally a DeleteRow...
0
10815
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10524
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10562
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10236
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7768
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 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 a new presenter, Adolph Dupr who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
6968
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5639
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
2
3997
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3092
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

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.