query to find difference in record sets

  • Thread starter Thread starter Sandy
  • Start date Start date
S

Sandy

I am trying to do the following:

I have one table with students registered in Fall [A], Winter [C] and Spring
classes. I am trying to find all students that are registered in Fall and
Spring, but not in Winter.

Query 1 - Find all students registered in the Fall
Query 2 - Find all students registered in the Spring
Query 3 - Find all students registered in both Fall and Spring
Query 4 - Find all students registered in the Winter
Query 5 - Find all students registered in the Fall and Spring but not in
Winter


I have the first 4 queries done. I need help with Query 5 to filter out the
records found in Query 4 from records found in Query 3

The criteria used is the same field each time
The queries results for Query 3 and 4 is the Student ID only
All queries are running on the same table

I know this must be simple but it is eluding me. Thanks for your help!

sandra
 
Have you tried the Wizard query about finding unmatched records? You want
records from query3 with no match in query4.


The SQL statement may look like:


------------------------------------
SELECT query3.studentID
FROM query3 LEFT JOIN query4
ON query3.studentID= query4.studentID
WHERE query4.studentID IS NULL
------------------------------------


Note the WHERE clause is applied to the result of the JOIN, NOT to query4
itself (which, by itself, has probably no studentID being null). Clearly, if
the join pumps a null under query4.StudentID, it is because it didn't find
any match, which is what we are looking for.



Vanderghast, Access MVP
 
This worked - thanks so much!

Michel Walsh said:
Have you tried the Wizard query about finding unmatched records? You want
records from query3 with no match in query4.


The SQL statement may look like:


------------------------------------
SELECT query3.studentID
FROM query3 LEFT JOIN query4
ON query3.studentID= query4.studentID
WHERE query4.studentID IS NULL
------------------------------------


Note the WHERE clause is applied to the result of the JOIN, NOT to query4
itself (which, by itself, has probably no studentID being null). Clearly, if
the join pumps a null under query4.StudentID, it is because it didn't find
any match, which is what we are looking for.



Vanderghast, Access MVP




Sandy said:
I am trying to do the following:

I have one table with students registered in Fall [A], Winter [C] and
Spring
classes. I am trying to find all students that are registered in Fall
and
Spring, but not in Winter.

Query 1 - Find all students registered in the Fall
Query 2 - Find all students registered in the Spring
Query 3 - Find all students registered in both Fall and Spring
Query 4 - Find all students registered in the Winter
Query 5 - Find all students registered in the Fall and Spring but not in
Winter


I have the first 4 queries done. I need help with Query 5 to filter out
the
records found in Query 4 from records found in Query 3

The criteria used is the same field each time
The queries results for Query 3 and 4 is the Student ID only
All queries are running on the same table

I know this must be simple but it is eluding me. Thanks for your help!

sandra
Click to expand...
Click to expand...
 
Back
Top