Find no matches on the many side

  • Thread starter Thread starter PizzaBoy
  • Start date Start date
P

PizzaBoy

Hello. Two tables: Table1, Table2. Table1 has a primary key named IDFIELD,
no duplicates. Table2 also has a field named IDFIELD with many duplicates.
Table 2 has a field name called TYPE with has one of the following items "A,
B, C, or D". The two tables are linked ONE TO MANY by the IDFIELD field.

Using a query (I don't know any scripting), I would like to create a query
that returns records of table1 where none of the records matched by IDFIELD
in table2 have the value of "B" or "D" in the TYPE field.

Example: The query should return the records for "3" and "5" from table1
because none of the records matched by the IDFIELD in Table2 have "B" or "D"
in the TYPE field. I have tried a couple of join types and query variations
and only succeeded in results which would include all 4 records since at
least one matched record in table2 linked by IDFIELD in table1 doesn't have
"B" or "D" in the TYPE field.
Table1
IDFIELD
2
3
4
Table 2
IDFIELD TYPE
2 A
2 B
2 C
2 D
3 A
3 A
4 C
4 D
4 C
5 C
5 C
5 A

I hope this makes sense. Thank you for any help.
 
This is a classic "frustrated outer join" problem. The trick is you must
filter the rows from Table2 *before* you do the join - like this:

SELECT Table1.*
FROM Table1 LEFT JOIN
(SELECT IDFIELD FROM Table2
WHERE TYPE IN ("B", "D")) AS T2
ON Table1.IDFIELD = T2.IDFIELD
WHERE T2.IDFIELD IS NULL;

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Thank you for the information John. As I am a newbie, I am not quite sure
what you wrote. Could you give it to me in layman's terms.

Thanks
 
If you gave me the correct table names and field names, you can copy start a
new query, switch to SQL View, and paste the SQL I gave you into the query
window. Switch to Datasheet view to see the result.

If you're doing this from scratch in the query design grid, it's easier to
build the inner query first. Start a new query on Table2. Drag IDFIELD and
TYPE to the Field row on the query grid. Under type in the Criteria line,
enter:

IN("B", "D")

This will find all rows where Type = "B" or Type = "D". Save the query and
name it qryTypeBD.

Start a new query on Table1. Add qryTypeBD to the query - Access should
draw a linking line betwee IDFIELD in Table1 and the query. (If it doesn't,
drag IDFIELD from Table1 and drop it on IDFIELD in the query.) Double-click
this linking line to open the Join Properties window. Select the option to
show "all from Table1 and any matching from qryTypeBD". Click OK, and
Access should show you an arrow pointing toward the query.

Drag the fields you want from Table1 to the design grid. Drag IDFIELD from
the query to the design grid, uncheck the Show box, and type in the Criteria
line: IS NULL.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Excellent-this I understand. Thank you very much.
John Viescas said:
If you gave me the correct table names and field names, you can copy start a
new query, switch to SQL View, and paste the SQL I gave you into the query
window. Switch to Datasheet view to see the result.

If you're doing this from scratch in the query design grid, it's easier to
build the inner query first. Start a new query on Table2. Drag IDFIELD and
TYPE to the Field row on the query grid. Under type in the Criteria line,
enter:

IN("B", "D")

This will find all rows where Type = "B" or Type = "D". Save the query and
name it qryTypeBD.

Start a new query on Table1. Add qryTypeBD to the query - Access should
draw a linking line betwee IDFIELD in Table1 and the query. (If it doesn't,
drag IDFIELD from Table1 and drop it on IDFIELD in the query.) Double-click
this linking line to open the Join Properties window. Select the option to
show "all from Table1 and any matching from qryTypeBD". Click OK, and
Access should show you an arrow pointing toward the query.

Drag the fields you want from Table1 to the design grid. Drag IDFIELD from
the query to the design grid, uncheck the Show box, and type in the Criteria
line: IS NULL.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
"B" since
Click to expand...
 
Back
Top