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.