Access Database BookingSystem

[JamesOliver]

As part of my computing coursework i am making a booking system for a youth hostel. The only problem i have now is that you can double book beds.

Can anyone suggest a way that I can, once I have allocated a bed ( with start date and end date fields) to one customer, I can prevent you from then continuing to allocate the same bed to a custoemr for any dates within the start and end date for teh first customer.

This would be a great help! Cheers!

James

 

[sfreeman@co.mer]

Make all three fields a combined primary key.

 

[snoko]

Or create a unique index containing all three fields.

 

[Pat Hartman]

To make a multi-field primary key:
Select the first column to highlight it, then while holding the cntl key, select the additional columns. Jet supports up to 10 columns in a key or index. When all the columns are highlighted, press the key icon on the toolbar.

 

[JamesOliver]

Help again!

I tried that idea a while a go, that's when i found the problem.

You see, when I do that all that stops you from doing is booking the same bed twice for teh same start date and the same end date. it doesn't stop you booking it again for dates in the middle. It's a real pain, and my knowledge of access is not great enough to work out how to stop this!

Plz help!

 

[Pat Hartman]

The only way to get Jet to handle this for you is to create separate records for each day of a stay. So, if a room is booked for 5 days, create 5 booking records. If you leave the record as from-through, you need to do some editing yourself to prevent the overlaps.

The selection criteria becomes more complex since you need to check both the start and end dates of the requested period against the allready booked dates. You also need to consider the specific room or room type in the criteria.

If this criteria returns anything, the requested date is already booked.
Where ReqStartDate >= Date() AND ReqStartDate <= BookedStart and ReqStartDate >= BookedEnd
AND
ReqEndDate > ReqStartDate AND ReqEndDate <= BookedStart And ReqEndDate >= BookedEnd;

In the long run, I think that it might be easier for you to create a record for each day.

 

[Jon K]

I have attached a sample database, which shows how you can find the beds available for booking.

You can open the search form, enter a start date and an end date, and click on the command button to view the beds booked as well as the beds available for booking for the period entered.

The command button runs two queries. The second query returns the bed numbers available for booking.

qryBedsBooked:
SELECT DISTINCT [BedNumber]
FROM Booking
WHERE [StartDate]<=[forms]![SearchForm]![txtEndDate] And [EndDate]>=[forms]![SearchForm]![txtStartDate];

qryBedsAvailable:
SELECT [Beds].[BedNumber]
FROM Beds LEFT JOIN qryBedsBooked ON [Beds].[BedNumber]=[qryBedsBooked].[BedNumber]
WHERE [qryBedsBooked].[BedNumber] Is Null;


Edit:-
Uploaded a version with the Column Heads property of the "Records in Booking Table" listbox set to Yes for better viewing.
.

 

[brow]

Say a booking is made from 21/06/2005 - 22/06/2005 and you search for available dates 22/06/2005 then the room booked for the previous night shows as booked for the following night. Does anyone know how to go about fixing this problem?

I hope my question makes sense?!!

 

[EMP]

I added a record in the Booking table for Bed #101 for 21/6/2005 to 22/6/2005 in the database.

When I searched for the date 22/6/2005 on the form, Bed #101 was displayed in the Beds Booked listbox while all the other beds were displayed in the Beds Available listbox.

It seems Jon's database can correctly return the beds.

 

[Jon K]

Originally posted by JamesOliver
Can anyone suggest a way that I can, once I have allocated a bed ( with start date and end date fields) to one customer, I can prevent you from then continuing to allocate the same bed to a custoemr for any dates within the start and end date for teh first customer.

The sample database was created to address the poster's question of preventing a bed from being allocated to another customer within the start date and end date for the first customer. So a bed booked for 21/6/2005 - 22/6/2005 could be booked for 20/6/2005 and before or 23/6/2005 and after.

.

Originally posted by brow
Say a booking is made from 21/06/2005 - 22/06/2005 and you search for available dates 22/06/2005 then the room booked for the previous night shows as booked for the following night.

Not sure if you are referring to a scenario where the first day of the booking period can be the checkout day for another reservation, and the last day of the booking period can be the arrival day of another reservation.

If you are, you can simply change the <= and >= to < and > in the first query. That is

qryBedsBooked:
SELECT DISTINCT [BedNumber]
FROM Booking
WHERE [StartDate]<[forms]![SearchForm]![txtEndDate] And [EndDate]>[forms]![SearchForm]![txtStartDate];
.