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 developing a database for room bookings at work.
The point I am at is to put in a query to check for double bookings. So far this is fine, if a room does not overlap on the time, it says there isn't a clash, and if there is an overlap, it says there is an overlap. But, what I'd like is for the query to return an overlap if two bookings are made within 30 minutes of each other. Example, if a meeting in room 1 ends at 13:00 and another starts at 13:15 in the same room, an overlap would be returned. This is needed as sometimes the layouts of rooms need adjusting and so staff need time to check the rooms. The part fo the query that deals with times is as follows (I am using the same table twice in the query to compare bookings). NoClash: (TblMeetingAppoint_1.MeetingStart=TblMeetingAppoi nt.MeetingEnd) Or (TblMeetingAppoint_1.MeetingEnd=TblMeetingAppoint .MeetingStart) I've tried to use DateAdd to modify times, but whenever I do, the "n" part of DateAdd to tell DateAdd I want to modify the minutes, keeps on being changed to ["n"] when I save the query. When the query is run, the query then asks for the value of n. Should I be using DateAdd or something else? Any help would be greatly appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice Performance Testing .NET Web Apps [MSPRESS] http://www.eggheadcafe.com/tutorials...sting-net.aspx |
#2
|
|||
|
|||
Double Booking Query
(TblMeetingAppoint_1.MeetingStart=DateAdd("n",30, TblMeetingAppoint.MeetingEnd))
Or (TblMeetingAppoint_1.MeetingEnd=DateAdd("n",-30,TblMeetingAppoint.MeetingStart)) That might be overkill since you are adding 30 minutes to the End time and subtracting 30 minutes from the begin time. Which has the effect of expanding the meeting time by 60 minutes. Perhaps you only need to increase the meeting end time. If that does not work, please post the entire SQL of the query that is not working. (View: SQL and then copy and paste). John Spencer Access MVP 2002-2005, 2007-2009 The Hilltop Institute University of Maryland Baltimore County Andy Day wrote: At present I am developing a database for room bookings at work. The point I am at is to put in a query to check for double bookings. So far this is fine, if a room does not overlap on the time, it says there isn't a clash, and if there is an overlap, it says there is an overlap. But, what I'd like is for the query to return an overlap if two bookings are made within 30 minutes of each other. Example, if a meeting in room 1 ends at 13:00 and another starts at 13:15 in the same room, an overlap would be returned. This is needed as sometimes the layouts of rooms need adjusting and so staff need time to check the rooms. The part fo the query that deals with times is as follows (I am using the same table twice in the query to compare bookings). NoClash: (TblMeetingAppoint_1.MeetingStart=TblMeetingAppoi nt.MeetingEnd) Or (TblMeetingAppoint_1.MeetingEnd=TblMeetingAppoint .MeetingStart) I've tried to use DateAdd to modify times, but whenever I do, the "n" part of DateAdd to tell DateAdd I want to modify the minutes, keeps on being changed to ["n"] when I save the query. When the query is run, the query then asks for the value of n. Should I be using DateAdd or something else? Any help would be greatly appreciated! Submitted via EggHeadCafe - Software Developer Portal of Choice Performance Testing .NET Web Apps [MSPRESS] http://www.eggheadcafe.com/tutorials...sting-net.aspx |
Thread Tools | |
Display Modes | |
|
|