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.

[Allen Browne]

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.

[Roger]

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.

[hwangtw]

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.

[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.