query to find difference in record sets

[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

 

[Michel Walsh]

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]

This worked - thanks so much!

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

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