Duplicate Problem

  • Thread starter Thread starter John
  • Start date Start date
J

John

Hey,

I've run into what seems to be a rather large problem, but
hopefully have a relatively easy solution (that I can't
think of).

I am tracking coursework in a database that contains
tables: Profile(Student Information), Training(General
Course information, course name, course number, etc...),
Course (Specific course information, including course
number, section number, instructors etc...) and Results
(Student results).

My primary key throughout the database is the Employee ID
# and have foreign keys including the Course Number. The
problem I am having is if a student takes one course with
one section number and fails the class, my database wont
allow them to retake the same course (same course number)
but with a different section number. Any thoughts would
be greatly appreciated. Thanks a million in advance!!!!
..
 
I'm a bit unclear as to what an Employee ID has to do with a student taking
classes.
But here's my guess as to your problem.
Your Results table may not be indexed correctly.
If you have a unique index on your StudentID - CourseID combination, no
student can ever be entered in two sections of the same class. Since you
have a situation (a failed class) where this is too restrictive, you could
expand your index to include your SectionID. Now the
StudentID-CourseID-SectionID combination must be unique.

Too broad, you say? You only want to be able to enter a student into a
second section if he failed all sections for which he is already registered?
This can no longer be done simply with indexes. At this point you'll need
some code behind a form, specifically behind the form where you enter the
CourseID and SectionID. When you enter the CourseID, you can check whether
there are any entries in Results for this student and this course, for which
there is no failing grade. If so, disallow the entry. The BeforeUpdate
event is a convenient place to make a check like this, because its Cancel
argument lets you keep your user in the same control until it is satisfied.
The DLookup() function is a convenient way to check for values in a table.

HTH
- Turtle
 
Back
Top