Help with double booking

[syth_2002]

Please could someone give me help on this topic, I need to slove it for coursework in Access but im having the following problem.

Im creating a database for a hotel which rents out its room obviously. The problem i have is if a guest books a room for say march 3rd until march 6th, theres currently no way in which to stop another guest booking the room for any of these dates.

I think i know how to stop double booking for the start day, but not the days inbetween if you understand.
The database has 3 tables:
Guest Info
Room info
Bookings

If anyone can help it would be greatly appreciated.

P.S the method i hope to use, is the hotel will enter the start date of a guests stay and then enter the number of nights the guest is staying for e.g. 3rd-6th of march would be input as: Begin date: 03/03/08 for:'3' nights.


Thanks for any help

 

[gemma-the-husky]

METHOD A
i suppose for each room, have a full 365 day calendar, indicating whether occupied that evening, and if it is occupied for 3 days, then set a flag in the occupation table for each day of the stay (or index it to the occupant record)

METHOD B
altrnatively you have almost as you have, a start and finish date, and then you need to check for any new proposed occupancy that

a) the start date does not fall between an occupied period, and
b) the end date does not fall between an occupied period, and

i think Method A is possibly the easier, although technically not perhaps normalized - i think where you will have a high occupancy %age, the nulls/blanks are an acceptable compromise. I know this is quite a bit different to what you have though.

 

[syth_2002]

Ive decided to use your method B as A seems to waste to much space which i would lose marks for. So ive decided to make some sort of query that makes use of a start and end date.

I hate to ask for more help when i have nothing to offer back but if possible could anyone give the following help.
-How can i get access to recognise that i want to include all the dates between two dates e.g 11-17 of november but include 12,13,14,15 and 16 of that month.

-How excaltly do i stop it from double booking the room i.e make it realize that these dates are all uique and cannot be used twice.

-Will solving these two problems allow me to query the system for available rooms?

Hopefully this makes sense, if anyone has any help please post it, and if more than one person can help it would be great to build up a range of views.

Thanks again

 

[gemma-the-husky]

if you are trying to enter a new booking from say the 18th to the 21st

then for a given room, the 18th must be greater than or equal to the highest end booking date, which is less than 21st,

and also the the 21st must be less than or equal to the lowest start booking date, which is greater than 18th,


ie theres no existing booking which ends between the 18th and the 21st, or any existing booking which starts between those dates.

this is quite complex to do.

as i say, i'm more inclined to think that a reversation system would have a daily check list for each room - similar to a theatre ticket booking system, or airplane ticket system.

if you need to record a booking from 18th to 21st, you simply check the register for 18th,19th 20th and 21st. but its still awkward writing a mechanism to check room availability, whichever system you use

 

[pbaldy]

It's actually not hard to find conflicts with method B design. The trick is to compare the test beginning date to the table end date, and vice versa. Here's the WHERE clause of a query that finds people on vacation for any portion of a chosen date range:

WHERE tblSickVac.StartDate<=[Forms]![frmCriteria]![txtToDate] AND tblSickVac.EndDate>=[Forms]![frmCriteria]![txtFromDate]

Add room to the criteria and you find if there are any conflicts with a proposed reservation for a given room.

 

[syth_2002]

Equations

Im having so much trouble understanding what your all telling me so I took from it all what i can to create this incomplete solution.

Basically my 3 tables are fine for this solution. Btw Im trying to stop bookign the same room twice (aka double booking)
Now im going to create a from to input data but make it so that i cant enter a date for a specific room thats already in use. Heres an equation I devised, i highly doubt it works because i dont understand database queries so ill explain each part.

code:
=[StartDate]![Enddate]for[RoomNo]

Im sure you see what im going for, The '=' needs to be some kind of 'doesnt equal' then '[startdate]![Enddate]' Means between these two dates, including them. Finally all rooms have different dates so I need the equation to check the inserted Room Number so i wrote 'for[RoomNo].

I think i migth have had this equation told to me but i couldnt understand it. If anyone could for the final time help me to crreate this equation and explain it then ill finally be done.

 

[pbaldy]

Can you post a sample db? The SQL I gave you is the direction you want to go, whether it's using a query, recordset or DCount.

 

[syth_2002]

Yeh sorry

Sorry i dont understand much abotu SQL or even what i means. Ill send you this database and if you would could you insert the correct data or even show me where it goes? Thanks so much if you can help Ive only got 2 days to get this working and written up before Hand in!

 

[pbaldy]

Here's the appropriate SQL

SELECT [Tbl_Bookings].[CustomerID], [Tbl_Bookings].[BookingStartDate], [Tbl_Bookings].[BookingEndDate], [Tbl_Bookings].[RoomNo]
FROM Tbl_Bookings
WHERE [Tbl_Bookings].[BookingStartDate]<=[Forms]![Form1]![txtEndDate] And [Tbl_Bookings].[BookingEndDate]>=[Forms]![Form1]![txtStartDate] And [Tbl_Bookings].[RoomNo]=[Forms]![Form1]![txtRoom]

If it returns records, there's a conflict.

 

[Jon K]

Im creating a database for a hotel which rents out its room ....

In hotel-room booking where the BookingEndDate of a room can be the BookingStartDate of a new booking, you can use < and > rather than <= and >=.
.