473,322 Members | 1,690 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.

Multiple Sub queries in a SELECT statement

Ben
I believe I am missunderstanding 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 10280
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.google.c om...
I believe I am missunderstanding 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.google. com>...
I believe I am missunderstanding 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> ¼¶¼g©ó¶l¥ó·s»D
:bd**************************@posting.google.com.. .
I believe I am missunderstanding 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.net.tw> wrote in message news:<cc**********@news.seed.net.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> ¼¶¼g©ó¶l¥ó·s»D
:bd**************************@posting.google.com.. .
I believe I am missunderstanding 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
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...
4
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...
5
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...
3
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 ...
11
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...
2
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...
7
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...
2
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...
7
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,...
1
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...
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
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
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: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
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
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.