Hotel Reservations System - Vacancy

[Melanie S. via AccessMonster.com]

any ideas on how to look for
room availability? I have already created forms for adding reservations for
both existing and new guest, and I would like to add a button on those forms
to check for vacancy between dates entered.. I have thought about a sort of
calendar maybe but I have absolutely no idea how to make one, any help please?


Plus, before looking for rooms availability, I think I have to delete all
unconfirmed reservations - I have made a delete query where criteria for:
confirmation = false
datein < date()
But this deletes only the records in the tblGuests and how to use this on a
form anyway? Or maybe I'm using the wrong method? Plz help

tblGuests
------------------
guestID (PK) (autonumber)
name
address
country
phone

tblBookings
------------------
guestID
roomno
datein
dateout
confirmation (yes/no)

tblRooms

 

[Bill Edwards]

How about something like:

SELECT tblRooms.RoomNo
FROM tblRooms
WHERE tblRooms.RoomNo
NOT IN
(SELECT RoomNo FROM tblBookings
WHERE tblBookings.DateIn BETWEEN (dtmStart AND dtmEnd)
OR tblBookings.DateOut BETWEEN (dtmStart AND dtmEnd))

.... or the following if you want to exclude non-confirmed bookings.

SELECT tblRooms.RoomNo
FROM tblRooms
WHERE tblRooms.RoomNo
NOT IN
(SELECT RoomNo FROM tblBookings
WHERE (tblBookings.DateIn BETWEEN (dtmStart AND dtmEnd)
OR tblBookings.DateOut BETWEEN (dtmStart AND dtmEnd)) AND
tblBookings.Confirmation)

 

[PC Datasheet]

Melanie,

I have a reservations system module. It contains a graphical user interface
to visually display room availability. Send me an email and I will send you
a screen shot.