Access constraint: overlapping dates

[robst]

Hi,

I have developed a small hotel booking system which inserts records into a reservations table. The table looks like this:
[ReservationNr][StartDate][EndDate][RoomNr]

I am now trying to add a constraint which prevents a room from beeing booked twice on the same day, hence the time period defined by StartDate and EndDate in a record must not overlap with another record.
Is Access capable of adding such a constraint?
It was easy to add a constraint that prevents a booking where the StartDate is before the EndDate, but this one needs to reference already existing records.
Any help would be much appreciated.

Thanks

 

[The_Doc_Man]

Is Access capable of adding such a constraint?

No and yes.

No, can't do it with a single constraint.

Yes, can do it with a compounded constraint.

There are a few cases to consider. It is up to you as to what constitutes an overlap.

Case 1. End date of a proposed new entry equals start date of an existing entry. (If you don't have times, it is up to you to decide whether the room is down for the day.)

Case 2. Start date of a proposed new entry equals end date of an existing entry.

Case 3. Start date of a new entry is between start & end dates of an existing entry.

Case 4. End date of a new entry is between start & end dates of an existing entry.

Case 5. (Complete subset case) Start date of a new entry follows start date of an existing entry AND end date of the same entry PRECEDES end date of an existing entry.

Case 6. (Complete subset case) Start date of a new entry precedes start date of an existing entry AND end date of the same entry FOLLOWS end date of an existing entry.

What you want is to write a UNION query (look it up in the HELP files) that contains the matching records according to the above criteria. Then take a count of records in the query. If a UNION query is run with the start date and end date as parameters, you can count (and view) any conflicting entries. If the count is zero, you can make the booking.

By the way, I'm not claiming that the above set of 6 comparisons is irreducible. It's just that such reductions would depend on whatever business rules you chose. Like is it legal to book the room starting on the same day that another booking ends? If you want 24 hours to clean & prep the room, the answer might well be "NO" - but if you have a fast turn-around on rooms, your answer could be "YES" and in that case, a couple of those tests don't make sense any more. Also, it might be possible to change = to <= or >= and collapse a couple of those. Play with it. See where it leads you.

 

[robst]

Yes, can do it with a compounded constraint.

Thanks for your reply.
I tried something like that before and now I tried the union statement, but my problem is, that the constraint seems to be unable to reference the to be inserted value. For instance in order to prevent an overlap of the start date of a new reservation and the end date of an existing I have tried:

ALTER TABLE Reservations
ADD CONSTRAINT Overlap
CHECK (not exists (SELECT old.RStart, old.REnd
FROM reservations old
WHERE RStart = old.REnd))

The problem seems to be, that the red RStart does not reference the new value which I want to insert, but instead references the old.RStart.
Any hint how I can use the new value that I want to insert?