query to select data from 3 tabes using <> parameter 2

[MrPiccolo]

Hi folks,
i'm having a mental-block day. I have a query which holds 3 tables [from an education database].
Table 1. [courses]
ClassID
Classname

Table 2. [course_dates]
CourseDatesID ('uniqueid')
ClassID ('linked to classID in table 1.')
DateOfCourse

Table 3. [students_and_courses]
studentclassID ('unique ID']
studentID ('linked to another external table [students]')
CourseDateID ('linked to table 2.')

I'm trying to run a query that finds all students that have NOT attended a particular course on ANY occasion.
Obviously a course may be run 3 or 4 times a year, so how do i list all students who have never attended a course?
Example of data; courseID = "63" (Classname = "critcare3")?
Any suggestions would be helpful please.
MrPiccolo

 

[PHV]

SELECT studentID
FROM students
WHERE studentID NOT IN (SELECT studentID FROM course_dates WHERE ClassID=63)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrPiccolo]

we're nearly there, but still having problems!!;

I've amended the SQL to use the classNAME (rather than the ClassID) as the identifier, but the query still finds 3 students who have attended the course "critcare3". Is the studentID being pulled from the correct table I wonder?
Also could the query be structured to allow the user to type in the name of the course that they want to use as a selection criteria? Eg "coursename" = 'critcare3' or coursename = 'APLS' or coursename" = "moving"..etc
(how do you achieve this??!)

the current SQL is now;

SELECT students.StudentID, [firstname]+" "+[lastname] AS Stdname
FROM students INNER JOIN (Courses INNER JOIN (coursedates INNER JOIN [Students and Courses] ON coursedates.CourseDatesID = [Students and Courses].CourseDateID) ON Courses.ClassID = coursedates.ClassID) ON students.StudentID = [Students and Courses].StudentID
WHERE (((students.StudentID) Not In (SELECT studentID FROM students WHERE [courses]!Classname="critcare3")));

mrP.

 

[PHV]

SELECT StudentID, [firstname] & ' ' & [lastname] AS Stdname
FROM students
WHERE StudentID Not In (SELECT studentID FROM Courses
INNER JOIN (coursedates INNER JOIN [Students and Courses]
ON coursedates.CourseDatesID = [Students and Courses].CourseDateID
) ON Courses.ClassID = coursedates.ClassID WHERE courses.Classname='critcare3')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrPiccolo]

I still have a problem!
Thre are 75 courses listed in my course table; It would save a lot of time if I can prompt the user to type the course [criteria] name (into the query) instead of having 75 separate queries; so far my SQL code looks like this but this doesn't quite work, please could anyone suggest a solution?
Many thanks
MrP

SELECT students.StudentID, [firstname]+" "+[lastname] AS StdName, Courses.ClassName AS class_select
FROM students INNER JOIN (Courses INNER JOIN (coursedates INNER JOIN [Students and Courses] ON coursedates.CourseDatesID = [Students and Courses].CourseDateID) ON Courses.ClassID = coursedates.ClassID) ON students.StudentID = [Students and Courses].StudentID
WHERE (((students.StudentID) Not In (SELECT studentID FROM Courses INNER JOIN (coursedates INNER JOIN [Students and Courses] ON coursedates.CourseDatesID = [Students and Courses].CourseDateID ) ON Courses.ClassID = coursedates.ClassID WHERE class_select="")) AND ((Courses.ClassName)=[Me].[classname]));

 

[PHV]

SELECT StudentID, [firstname] & ' ' & [lastname] AS Stdname
FROM students
WHERE StudentID Not In (SELECT studentID FROM Courses
INNER JOIN (coursedates INNER JOIN [Students and Courses]
ON coursedates.CourseDatesID = [Students and Courses].CourseDateID
) ON Courses.ClassID = coursedates.ClassID WHERE courses.Classname=Forms![name of form]![classname])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[MrPiccolo]

thanks PHV, you've cracked it!! ;-)
mrP.