Register Students For Classes

[speakers_86]

I'm working on an assignement where students are registered for class. I am stumped on how to prevent students from registering for classes that occur at the same time. Any one want to lend a hand?

Tables:
tblCourses (Math, Enlish, etc.)
tblClass (courseID, InstructorID, ClasroomID, DayID, TimeID, etc)
tblInstructors
tblClassSubStudents (ClassID, StudentID)
tblDays (M for Monday, T for Tuesday, W, for Wendesday, R for Thursday, etc)

From the instructions, it seems as if the relationships should prevent enrolling in classes at the same time. I didn't see how to do that, so I am currently trying to prevent it at the form level. The quasi-solution I have now is that I combine the classes start date, day of class, and time of class. This gives me a day time group. I use that DTG and the query wizard to make a find unmatched query. That unmatched query is the basis of the combo box that registers the students for classes. This works in very simple examples if the day values in tblDays where only M T W R F S. So if a student takes a class at M 0900, then the unmatched query still allows the student to register for classes that are at MWF 0900. The student is now in two classes at once Mondays at 0900.


Edit- I'm not sure why I was set on doing this with queries, but it seems helluva easier to do with vba.

 

[JHB]

Do you choose courses, time, students from a combo box?
Do you have some sample data?

 

[Solo712]

Edit- I'm not sure why I was set on doing this with queries, but it seems helluva easier to do with vba.

It is trickier than it seems in that the query also has to deal with overlaps not just simple match (or no-match) of the day and starting time of a particular instance of a class. The algorithm must prevent both, scheduling conflict with a closest existing class preceding the time slot for the new class to register the student on a particular day and the one closest after it. The logic would look like this, in a metalanguage:

Enroll = Iif(StudentHasNoClassonDayX, Not(NearestPrecClassOnDayXwithSTudentInitEndTime >= ClasstotoEnrollStartTime or NearestSuccClassOnDayXwithSTudentInitStartTime <= ClasstotoEnrollEndTime))

Good luck with it !

Best,
Jiri