Using dates and room numbers in hotel database

[Mike]

Hi
Im 1 month old to access.

I have a list of room numbers say 1-10 and type of bed in a table
called rooms

Then I have another table called check in which requires the checkin
date the ceck out date and the room number. How could I go about
having only available rooms between these dates show up in a drop down
list? A webpage or advice would be greatly appreciated.

I have been to create a dropdown list. Would a query be the way to
go?
Does what Im describing require VBA knowledge
Thanks
Mike

 

[KARL DEWEY]

Create a table named CountNumber with field CountNUM containing numbers from
0 (zero) through your maximum date spread.
RoomDates --
SELECT Rooms.Room, Rooms.Bed, DateAdd("d",[CountNUM],Date()) AS AvailDate
FROM Rooms, CountNumber
WHERE (((DateAdd("d",[CountNUM],Date()))<=Date()+14));

RoomDatesOccup --
SELECT CheckIn.Room, DateAdd("d",[CountNUM],[CheckIn]) AS OccupStart,
CheckIn.CheckOut
FROM CheckIn, CountNumber
WHERE (((DateAdd("d",[CountNUM],[CheckIn]))<=[CheckOut]) AND
((CheckIn.CheckOut)>=Date()))
ORDER BY CheckIn.Room, DateAdd("d",[CountNUM],[CheckIn]);

RoomDatesAvail ---
SELECT RoomDates.AvailDate, RoomDates.Room, RoomDates.Bed
FROM RoomDates LEFT JOIN RoomDatesOccup ON (RoomDates.Room =
RoomDatesOccup.Room) AND (RoomDates.[AvailDate] = RoomDatesOccup.[OccupStart])
WHERE (((RoomDatesOccup.Room) Is Null))
ORDER BY RoomDates.AvailDate, RoomDates.Room;

 

[ken]

Mike:

Firstly create a table of all dates over a range, say the next 10
years. You can do this easily by serially filling down a column in
Excel with dates and then importing it into Access as a table. Call
the table Calendar and its one column (field) calDate, which should of
course be a date/time data type and the table's primary key.

On your data entry form add a combo box, cboRoom, to list the
available rooms. Set its ControlSource property to [Room Number].
For its RowSource property use a query which, assuming you want to
list the rooms vacant on ALL dates in the range would be:

PARAMETERS
Forms![YourFormName]![Check In] DATETIME,
Forms![YourFormName]![Check Out] DATETIME;
SELECT [Room Number]
FROM Rooms
WHERE NOT EXISTS
(SELECT *
FROM [Check In], Calendar
WHERE CalDate BETWEEN Forms![YourFormName]![Check In]
AND Forms![YourFormName]![Check Out]
AND CalDate >= [Check In]
AND CalDate <= [Check Out]
AND [Check In].[Room Number] = Rooms.[Room Number])
ORDER BY [Room Number];

You'll need to substitute the real name of your form of course and
make sure that the table and column names in the query exactly match
your own. If you are not familiar with creating a query in SQL then
what you do is open the query designer in the usual way; don't add any
tables; from the View menu select SQL View; type or paste in the SQL
as above, and amend it to use your real form, table and column names.

In the AfterUpdate event procedures of both the check in and check out
controls on your form put the following code:

If Not IsNull(Me.[Check in]) And Not IsNull(Me.[Check Out]) Then
Me.cboRoom = Null
Me.cboRoom.Requery
End If

To do this select the control in form design view and open its
properties sheet if its not already open. Then select the AfterUpdate
event property in the properties sheet. Click on the 'build' button;
that's the one on the right with 3 dots. Select 'Code Builder' in the
dialogue, and click OK. The VBA window will open at the event
procedure with the first and last lines already in place. Enter the
lines of code between these two existing lines.

When dates are entered in the check in and check out controls the
combo box will be requeried to show only those rooms vacant on ALL
dates in the range defined by the check in and check out dates.

Ken Sheridan
Stafford, England