Peter, it may help to think backwards.
This query produces a list of the prerequisites a student has NOT completed
for a course. If no records are returned, the student is eligible.
SELECT tblCoursePrerequisite.PrerequisiteID
FROM tblCoursePrerequisite LEFT JOIN tblResult
ON tblCoursePrerequisite.PrerequisiteID = tblResult.CourseID
WHERE (tblCoursePrereqisite.ForCourseID = 99) AND NOT EXISTS
(SELECT CourseID FROM tblResult
WHERE (tblResult.StudentID = 999) AND
(tblResult.CourseID = tblCoursePrerequisite.PrerequisiteID) );
Structure assumed:
tblCourse (list of courses):
CourseID as primary key.
tblCoursePrerequisite:
ForCourseID CourseID that has a prerequisite.
PrerequisiteID CourseID that is a prerequisite.
Two one-to-many joins to tblCourse, so a course can have many prerequisites.
tblResult (student results in their courses):
StudentID foreign key to tblStudent.StudentID
CourseID foreign key to tblCourse.CourseID
Result what they achieved
ResultDate when achieved
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"Pieter Linden" <pietlinden@hotmail.com> wrote in message
news:bf31e41b.0311212100.5e80b3ed@posting.google.c om...[color=blue]
> Hi,
> I think the subject line pretty much says it all...
>
> Say I have a students-classes database and I add a twist. I want to
> filter what courses a student can take by comparing the courses he has
> taken to the list of prerequisites for the remaining courses.
> Everything is fine if a course only has one or no prerequisite. How
> do you express in SQL something like:
>
> "Course C" requires "Course A" and "Course B"
>
> and then leave out all the students who do not have both Course A and
> Course B?
>
> What "Course C" requires is easy:
>
> CREATE TABLE RequiresCourse(
> Course INT NOT NULL,
> Prereq INT NOT NULL,
> PRIMARY KEY (Course, Prereq));
>
> SELECT Prereq
> FROM RequiresCourse
> WHERE Prereq = 'Course C';
>
> but how do I determine who has *all* the different prerequisites for a
> course?
>
> No. it's not a homework assignment. Honest. Could be and should be,
> but it isn't. I'll read my books anyway... I'm dying to figure this
> stupid thing out!
>
> Thanks!
> Pieter[/color]
