Date/Time Problem

[Teknogirl]

Hi - I am creating a Reservations Database for Room Reservations. I want to
set my Primary Key to include the period of time that the room is used, and I
don't know how to make the length of time the room is used
([EndTime]-[StartTime]) be a "busy" time.

Right now, if I set the Primary key to be Room, Day and StartTime, then
there could be conflicting reservations for the same room (example: Room is
booked from 8AM to 12PM, and someone could also book the same room from
9AM-11AM).

I think there must be a way to show the total time the room is being used,
and include that in the Primary Key, but I am lost in knowing how to set it
up!! HELP!!
:-D

 

[Jeff Boyce]

Not sure I'd use a calculated field like that as a primary/concatenated
key...

Another approach might be to create a procedure that returns a True/False
value for the question "Is it available?"

That way, you could basically "feed" the procedure a [Room], a
[ProposedStartDate] and a [ProposedEndDate] and have the procedure evaluate
whether the room was in use within that time frame.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP