Creating a Hotel Booking System for College

[Chris_A1]

For my college project I am attempting to create a reservation system (in Access) which could be used in a hotel for booking rooms.

I have so far created the following tables:

Bookings:
BookingID
CustomerID
ArrivalDate
DepartureDate
RoomReserved
etc

Customers:
CustomerID
Surname
Address
etc

Rooms:
RoomID
RoomName

I have had no trouble creating the form/subform for entering the customer and booking information.

However, I am really struggling to find a way of stopping rooms from being double booked, or for being able to find vacant rooms on a specific day.

Does anyone know how I could go about doing this?

Thanks very much.

 

[Tiro Cupidus]

Is Rooms.RoomID related with Bookings.RoomReserved?

 

[Rich]

Try the search button at the top of the page

 

[Mile-O]

Try the search button at the top of the page

Hmmm. Why didn't I think of that?

 

[Chris_A1]

Yes, RoomReserved should be RoomID.

 

[Chris_A1]

Yeah the search feature really is quite good.

It seems like I've posted a fairly common problem and in future I'll use search before posting!

 

[Chris_A1]

I've created a query which will return room bookings that will clash with two given dates (the arrival date and departure date):

PARAMETERS AD DateTime, DD DateTime;
SELECT Bookings.Caravan, Bookings.[Arrival Date], Bookings.[Departure Date], Bookings.Customer
FROM Bookings
WHERE ((([AD])>=[Arrival Date] And ([AD])<[Departure Date]) AND (([DD])>[Arrival Date] And ([DD])>=[Departure Date])) OR ((([AD])<=[Arrival Date] And ([AD])<[Departure Date]) AND (([DD])>[Arrival Date] And ([DD])<=[Departure Date]));

From here I should be able to find which rooms AREN'T booked for that period but I don't know how just yet.

Can anyone help?

 

[Chris_A1]

I've now replaced that query with:

PARAMETERS AD DateTime, DD DateTime;
SELECT Bookings.Caravan, Bookings.ArrivalDate
FROM Caravans INNER JOIN Bookings ON Caravans.Pitch = Bookings.Caravan
WHERE ((([AD])>=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])>=[DepartureDate])) OR ((([AD])<=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])<=[DepartureDate])) OR ((([AD])>=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])<[DepartureDate] And ([DD])>[ArrivalDate])) OR ((([AD])<=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])>[DepartureDate]));

This shows any existing bookings that overlap with the new booking.

How can I reverse this though to show rooms that AREN'T booked for that period?

 

[mission2java_78]

I've now replaced that query with:

PARAMETERS AD DateTime, DD DateTime;
SELECT Bookings.Caravan, Bookings.ArrivalDate
FROM Caravans INNER JOIN Bookings ON Caravans.Pitch = Bookings.Caravan
WHERE ((([AD])>=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])>=[DepartureDate])) OR ((([AD])<=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])<=[DepartureDate])) OR ((([AD])>=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])<[DepartureDate] And ([DD])>[ArrivalDate])) OR ((([AD])<=[ArrivalDate] And ([AD])<[DepartureDate]) AND (([DD])>[ArrivalDate] And ([DD])>[DepartureDate]));

This shows any existing bookings that overlap with the new booking.

How can I reverse this though to show rooms that AREN'T booked for that period?

So this shows rooms that ARE booked for that period, right? If so than all you need is <> CurrentQuery right? The opposite...create a query which returns the opposite of what this query returns. Your criteria should be Is Null...or something to that effect..meaning use this query but use a criteria which pulls the opposite records.

 

[Chris_A1]

The problem with that is that all bookings are shown which do not match the criteria. And what about if a room has no booking which matches the criteria. That room would not be shown on the list so therfore you would be unable to book it.

 

[alexecooper]

i've done a similar hotel reservation database you can download it from my website: http://www.e-male.me.uk

 

[Penmore]

I am in the process of developing a booking system for self catering properties and am using an Availability chart (form) that displays the booking for each property for a chosen 35 day period. The users can then click on the individual day cells to see the existing booking or to make a new booking.

Double bookings are prevented by the user having to use the availability chart to make new bookings and the system checks the numer of days available from the Availability Chart.

If you are interested, I can post some screenshots for you to see.