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

 

[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