Table relationships

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

Guest

Hello,

I have an issue with my Access database. I am creating an application that
will schedules courses and add students to those courses. In order for
students to register for certain courses they must satisfy all prerequisites.
I have a Course, Course Schedule, Student, Student Course Grades, Class
Registration, and Course Prerequisite table. A course can be scheduled for 1
or more dates. Students can can be assigned to 1 or more courses. A course
may have up to 3 or no prerequisites. I want to select a course on a
scheduled date in a form. Then I want to be able to look up possible
students for that course by those that have satisfied the prerequisites. Is
there a way I can open a "table" of eligible students (those who have passed
the prerequisites) and then double-click the name of individuals I want to
add to the Registered Class? I think part of my trouble is I'm not sure how
I can link the courses students have passed with the Prerequisite table.
Meaning if a students has passed a particular course which is the only
prerequisite for another course how do I make him elgible? Do I need another
table?

I'm probably making more complicated than what it is. Any help is
appreciated.
 
Then I want to be
able to look up possible students for that course by those that have
satisfied the prerequisites. Is there a way I can open a "table" of
eligible students (those who have passed the prerequisites) and then
double-click the name of individuals I want to add to the Registered
Class?
Click to expand...

What you need is the SQL DIVIDE operation: this is the one that gives you
a result like

LookedFor
=========
Maths
English

Abilities
=========
Eric, Maths
Eric, English
Eric, French
Fred, Maths
Fred, English
Graham, Maths
Graham, French

and you want the result set (Eric, Fred) because they are the only ones
that have both Maths and English.

The bad news is that none of the available databases actually implement
the DIVIDE operation. The good news is that you can put it together quite
easily -- it's a bit non-intuitive with two double negatives, but here
goes:

SELECT DISTINCT b.PersonID FROM Abilities AS b
WHERE NOT EXISTS
{ SELECT l.* FROM LookedFor AS l
WHERE NOT EXISTS
( SELECT a.* FROM abilities AS a
WHERE a.skill = l.skill
AND a.personid = b.personid
));


You probably can replace the LookedFor table with a subselect from your
Prerequisites table.

Hope that helps


Tim F
 
Back
Top