'Not in' Query

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hi, I have a few tables and want to run a query, but am
not sure how the sql will work out. I have a table that
has a list of students, and a results table that lists all
students and the corresponding classes they have taken
(also have a class table). What I want to do is run a
query that will show me all people who have NOT taken a
class. My results table has a list of people and classes
they have taken, and can't figure out how to run a query
that will ask the user for a class and then reference
everybody who hasn't taken that class....Any help would be
greatly appreciated.

Thanks a lot!!!
 
A quick thought that I once used for something similar..

Group on student, have a calculated field that says Expr1: Min(IIf(Class=[My Class],0,1)) and have a where clause on the field only where Expr1 =

I would say [My Class] would be a parameter from your form

Basi

----- John wrote: ----

Hi, I have a few tables and want to run a query, but am
not sure how the sql will work out. I have a table that
has a list of students, and a results table that lists all
students and the corresponding classes they have taken
(also have a class table). What I want to do is run a
query that will show me all people who have NOT taken a
class. My results table has a list of people and classes
they have taken, and can't figure out how to run a query
that will ask the user for a class and then reference
everybody who hasn't taken that class....Any help would be
greatly appreciated

Thanks a lot!!
 
Try something like: (not tested)

SELECT StudentList.StudentID
FROM StudentList LEFT JOIN StudentClasses ON StudentList.StudentID =
StudentClasses.StudentID
WHERE StudentClasses.StudentID Is Null

HTH
 
Back
Top