Query "Has Not Had" Course

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I've got a DB to track courses taken and completed towards a certification
program. In order to see which classes I should offer in the upcoming year,
I want to query "who has not had [fill in the blank]. I have done this in
another DB I created using check boxes, but I inherited this one, and can't
easily modify it. Also, I know enough to get me in trouble, so sample code
will help greatly.
 
Dear JP:

It sounds as though you have a table of students and another of courses,
with a table of details keyed by both student and course.

To do what you say under these conditions, use a portion of the WHERE clause
that looks something like this:

SELECT *
FROM Student S
WHERE NOT EXISTS (SELECT * FROM StudentCourse SC
WHERE SC.StudentID = S.StudentID
AND SC.CourseID = [fill in the blank])

In the above, [fill in the blank] could be a query parameter or, better yet,
a reference to a combo box on your form.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison
 
Thanks. This helps a lot.

Tom Ellison said:
Dear JP:

It sounds as though you have a table of students and another of courses,
with a table of details keyed by both student and course.

To do what you say under these conditions, use a portion of the WHERE clause
that looks something like this:

SELECT *
FROM Student S
WHERE NOT EXISTS (SELECT * FROM StudentCourse SC
WHERE SC.StudentID = S.StudentID
AND SC.CourseID = [fill in the blank])

In the above, [fill in the blank] could be a query parameter or, better yet,
a reference to a combo box on your form.

Please let me know if this helped, and if I can be of any other assistance.

Tom Ellison


J Parrish said:
I've got a DB to track courses taken and completed towards a certification
program. In order to see which classes I should offer in the upcoming
year,
I want to query "who has not had [fill in the blank]. I have done this in
another DB I created using check boxes, but I inherited this one, and
can't
easily modify it. Also, I know enough to get me in trouble, so sample
code
will help greatly.
Click to expand...
Click to expand...
 
Back
Top