If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
'Not in' Query
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!!! |
#2
|
|||
|
|||
'Not in' Query
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 = 1 I would say [My Class] would be a parameter from your form. Basil ----- 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!!! |
#3
|
|||
|
|||
'Not in' Query
Try something like: (not tested)
SELECT StudentList.StudentID FROM StudentList LEFT JOIN StudentClasses ON StudentList.StudentID = StudentClasses.StudentID WHERE StudentClasses.StudentID Is Null HTH "John" wrote in message ... 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!!! |
Thread Tools | |
Display Modes | |
|
|