Double Booking Query

[Andy Day]

At present I am trying to make a database for room bookings at work. I'm at the point where I need the database to check for double bookings on the rooms.

I have this query to detect where double bookings occur:

NoClash: (TblMeetingAppoint_1.MeetingStart>=TblMeetingAppoi nt.MeetingEnd) Or (TblMeetingAppoint_1.MeetingEnd<=TblMeetingAppoint .MeetingStart) Or (TblMeetingAppoint.MeetingDate<>TblMeetingAppoint_ 1.MeetingDate) Or (TblMeetingAppoint.MeetingID=TblMeetingAppoint_1.M eetingID) Or (TblMeetingAppoint.RoomID<> TblMeetingAppoint_1.RoomID)=False

In the form I have the rooms linked to a table with RoomID as the primary key and Roomname for the name of the rooms. You select the room from a combo box on the booking form.

the problem I am facing is that RoomID 3 is actually RoomID 1 and RoomID 2 with a partition removed. So I need to modify the above so that RoomID 1 and 2 will always class with RoomID 3. Whatever way I have tried so far results in no rooms ever clashing or every booking clashes, even if RoomID 4-7 are booked.

EggHeadCafe - Software Developer Portal of Choice
Pass Classes in ASP.NET with LosFormatter
http://www.eggheadcafe.com/tutorial...03-4135280f8c3a/pass-classes-in-aspnet-w.aspx

 

[Dale_Fye via AccessMonster.com]

First off, I would never allow the record to be written to my table until I
had confirmed the rooms availability.

1. Create a table that contains mappings for "rooms" that actually contain
other "rooms" (3 contains 1 and 2). This would give me the ability to check
for the overlaps. This type of situation happens all the time, especially in
situations like hotels and business centers. You might have one very large
room, that can be divided a variety of ways, and each arrangement has a
specific designation. So my Rooms table would probably contain specifics
like the available seating, and specific capabilities that are available in
that room. Tbl_Room_Mapping would contain two fields (RoomID_Primary,
RoomID_Secondary) and might look like:

RoomID_Primary RoomID_Secondary
Room1 Room1
Room2 Room2
Room3 Room1
Room3 Room2

2. Then I would create a query (qryApptRooms)that identifies the physical
rooms that are in use at any specific time. It might look like:

SELECT tblMeetingAppoint.*, tbl_Room_Mapping.RoomID_Secondary
FROM tblMeetingAppoint INNER JOIN tbl_Room_Mapping
ON tblMeetingAppoint.RoomID = tbl_RoomMapping.RoomID_Primary

This will give you both the "logical room" (in your case Room3) and the
physical rooms (RoomID_Secondary) associated with each appointment.

3. Then, to identify the conflicts, I would join two copies of this query.
Because this query uses non-equi joins, it can only be viewed properly in the
SQL view. The design view will not display the joins that are not defined by
and "=".

SELECT AR1.*, AR2.*
FROM qryApptRooms as AR1 INNER JOIN qryApptRooms as AR2
ON AR1.ID <> AR2.ID
AND AR1.MeetingDate = AR2.MeetingDate
AND AR1.RoomID_Secondary = AR2.RoomIDSecondary
AND AR1.MeetingEnd > AR2.MeetingStart
AND AR1.MeetingStart < AR2.MeetingEnd

This will give you a list of the room booking conflicts.

HTH
Dale

 

[KARL DEWEY]

Add another field - ID_Room so table looks like this --
RoomID ID_Room
1 3
2 3
3 3
4 4
5 5
6 6
7 7

Then try this --
NoClash: (TblMeetingAppoint_1.MeetingStart>=TblMeetingAppoi nt.MeetingEnd)
Or (TblMeetingAppoint_1.MeetingEnd<=TblMeetingAppoint .MeetingStart) Or
(TblMeetingAppoint.MeetingDate<>TblMeetingAppoint_ 1.MeetingDate) Or
(TblMeetingAppoint.MeetingID=TblMeetingAppoint_1.MeetingID) Or
(TblMeetingAppoint.RoomID<> TblMeetingAppoint_1.RoomID)=False Or
(TblMeetingAppoint.ID_Room<> TblMeetingAppoint_1.RoomID)=False

 

[Andy Day]

Hi Karl,

And thanks for the solution, very simple!

I still have once problem:

On the booking form, When you select the room this is stored under RoomID using the ID number, but the combo box is designed to show both the ID number and the name, with the ID number column's width set to 0 wide to 'hide' visually.

I'm not sure how I can get ID_Room to then auto populate once a selection is made from a combo box. Any tips?



KARL DEWEY wrote:

Add another field - ID_Room so table looks like this --RoomID ID_Room1
24-Nov-09

Add another field - ID_Room so table looks like this -
RoomID ID_Roo
1
2
3
4
5
6
7

Then try this -
NoClash: (TblMeetingAppoint_1.MeetingStart>=TblMeetingAppoi nt.MeetingEnd
Or (TblMeetingAppoint_1.MeetingEnd<=TblMeetingAppoint .MeetingStart) O
(TblMeetingAppoint.MeetingDate<>TblMeetingAppoint_ 1.MeetingDate) O
(TblMeetingAppoint.MeetingID=TblMeetingAppoint_1.MeetingID) O
(TblMeetingAppoint.RoomID<> TblMeetingAppoint_1.RoomID)=False O
(TblMeetingAppoint.ID_Room<> TblMeetingAppoint_1.RoomID)=Fals

-
Build a little, test a little

:

Previous Posts In This Thread:

EggHeadCafe - Software Developer Portal of Choice
Caching Pages and Application Data with Database Dependencies
http://www.eggheadcafe.com/tutorial...5-4a8530bde25f/caching-pages-and-applica.aspx