If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Double Booking Query
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.MeetingDateTblMeetingAppoint_ 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/tutorials...-aspnet-w.aspx |
#2
|
|||
|
|||
Double Booking Query
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 Andy Day wrote: 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.MeetingDateTblMeetingAppoint_ 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/tutorials...-aspnet-w.aspx -- HTH Dale Fye Message posted via AccessMonster.com http://www.accessmonster.com/Uwe/For...eries/200911/1 |
#3
|
|||
|
|||
Double Booking Query
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.MeetingDateTblMeetingAppoint_ 1.MeetingDate) Or (TblMeetingAppoint.MeetingID=TblMeetingAppoint_1.M eetingID) Or (TblMeetingAppoint.RoomID TblMeetingAppoint_1.RoomID)=False Or (TblMeetingAppoint.ID_Room TblMeetingAppoint_1.RoomID)=False -- Build a little, test a little. "Andy Day" wrote: 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.MeetingDateTblMeetingAppoint_ 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/tutorials...-aspnet-w.aspx . |
#4
|
|||
|
|||
How to autopopulate ID_Room?
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_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.MeetingDateTblMeetingAppoint_ 1.MeetingDate) Or (TblMeetingAppoint.MeetingID=TblMeetingAppoint_1.M eetingID) Or (TblMeetingAppoint.RoomID TblMeetingAppoint_1.RoomID)=False Or (TblMeetingAppoint.ID_Room TblMeetingAppoint_1.RoomID)=False -- Build a little, test a little. "Andy Day" wrote: Previous Posts In This Thread: EggHeadCafe - Software Developer Portal of Choice Caching Pages and Application Data with Database Dependencies http://www.eggheadcafe.com/tutorials...d-applica.aspx |
Thread Tools | |
Display Modes | |
|
|