How to prevent room and schedule conflicts ?

[yadnerocj]

Can you give me a good approach in programming a room scheduler in MS Access. The program tells the user if there is a conflict of schedule in a particular room. For example, the user wants to schedule a subject, let's say College Algebra to room 110 during Mondays, Wednesdays and Friday every 7:30-9:00 am. The program should be able to check if the specified schedule doesn't have any conflict with those schedules that are already stored in the database.

Or maybe you can give some links that discuss this kind of programming problem. Thanks! God Bless!

[MMcCarthy]

OK so you need to look at your table structures. My initial thoughts are the following...

tblSubject
SubjectID (PK)
Subject

tblRoom
RoomNum (PK)

tblTimes
TimeCode (PK)

tblSchedule

RoomNum (PK -FK referencing the tblRoom table)
TimeCode (PK - FK referencing the tblTimes table)
DateBooked (PK)
SubjectID (FK referencing the tblSubject table)

OK so the first three tables are just lookup lists essentially. So:

tblSubject would have values like ...

1    Algebra
2    English
3    Biology

tblRoom would just contain room numbers ...

110
121
145

tblTimes would contain a list of times. This would start with the time the rooms are available and end with the times the college closes. I would suggest the times should be on the half hour but you can add at 15 min intervals if necessary. If you keep to the 24 hour clock you don't need to worry about am and pm.

07:00
07:30
08:00
08:30
09:00
09:30

Now tblSchedule has a triple primary key made up of the time the date and the room number. So if as per your example we had algebra in room 110 from 07:30 to 09:00 you could have the following ...

RoomNum   TimeCode   DateBooked   SubjectID
 110        07:30    10/12/2010       1
 110        08:00    10/12/2010       1
 110        08:30    10/12/2010       1

Now because of the triple primary key if anyone tries to book room 110 at 08:30 on 10/12/2010 for example it will not allow this as it would be a duplicate entry.

Hope this makes sense.

Mary