473,288 Members | 1,726 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,288 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 10276
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...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
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: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...
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: marcoviolo | last post by:
Dear all, I would like to implement on my worksheet an vlookup dynamic , that consider a change of pivot excel via win32com, from an external excel (without open it) and save the new file into a...
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...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
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)...

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.