Time Between Query

[Fazee]

i have a DB which allows users to allocate trainers to course
sessions, they fill out date, start time , end time and other fields
and when they click on the trainer field I have a pop up which shows
the AVAILABLE trainers, i.e. it checks the existing session times and
only shows the ones who are not allocated to other sessions for the
same day and within the start time and end time entered.

I have one problem, the query doesn't pick up any unavailable trainers
where the time entered is before an existing time.
for example,

I have an EXISTING SESSION for Liam Cullen who is teaching at 09:00
(starttime) to 14:30 (endtime),

now if i enter a new session with starttime 10:00 and endtime 14:00,
Liam should not show up as he is UNAVAILABLE however this doesn't
work.

BUT for a new session if i have starttime as 09:00 and endtime of
14:00 liam is not showing up, hence unavailable which is correct.

I have found on many occasions that if the starttime i enter is after
an existing starttime, the trainers do not show up as unavailable when
they should.

I am using the BETWEEN function and everything else works fine except
for this one issue.

Thanks in advance.

 

[lespaul]

instead of using between use:

starttime >= sessionstarttime and endtime <= sessionendtime

(you may need to switch the < and >, I really can't tell if I've got it set correctly for you!)



Leslie

 

[PHV]

Say you have s1,e1 as starting,ending time of existing session and s2,e2 for new session.
You may consider testing like this:
s2 Not Between s1 and e1 And e2 Not Between s1 and e1 And s1 Not Between s2 and e2 And e1 Not Between s2 and e2

Hope This Help, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Fazee]

lespaul i tried the greater than less than method but this doesn't work, i use the query grid but in sql view ur solution looked like this:
WHERE (((course_session1.startdate)=[Forms]![new_session_only_Courses]![course_session1Subform]![startdate]) AND ((course_session1.trainerid) Is Not Null) AND ((course_session1.starttime)>=[Forms]![new_session_only_Courses]![course_session1Subform]![starttime]) AND ((course_session1.endtime)<=[Forms]![new_session_only_Courses]![course_session1Subform]![endtime]))

 

[OhioSteve]

Let me take a stab on this at a conceptual level. Here's a timeline. X represents an existing session. A and B represent proposed sessions.

12:00 start A
1:00 end A
2:00 start X
3:00 end X
4:00 start B
5:00 end B

Both A and B would be valid choices for new sessions. I suspect that any valid new session would fit one of two descriptions.

start time > existing start time
AND
start time > existing end time
AND
end time > existing start time
AND
end time > existing end time

___________________________*OR*___________

start time < existing start time
AND
start time < existing end time
AND
end time < existing start time
AND
end time < existing end time

 

[OhioSteve]

Okay, I have reconsidered this. On a conceptual level, you need two tests of the proposed session.

1) The proposed start time must precede the proposed end time.

2) The proposed start time must follow the existing session's end time OR the proposed end time must precede the existing session's start time.

When you evaluate a proposed session, you only need to do #1 once. However, you must do #2 for EACH of the existing sessions for that instructor for that day.

You could implement #2 using an iif(). Here is a rough draft of the iif():

iif(starttime > endtime or endtime < starttime,"okay","not okay")

 

[OhioSteve]

I continued to think about this thread last night.
Now I have a more detailed solution. In my test,
I had two tables, teacher and session.

Here is teacher~
name ID
John Smith 1
Jane Doe 2
Dan Jones 3
Sally Smith 4

Here is session~
sDate sStart sEnd sTeacherID
2/10/2004 8:05AM 9:00AM 2
2/10/2004 9:05AM 10:00AM 3
2/10/2004 1:05PM 2:00PM 2
2/10/2004 10:05AM 11:00AM 1
2/11/2004 9:05AM 10:00Am 3

In the test I assumed we were trying to find
which teachers were available on 2/10
from 10:05 to 11:00. You can see that
the answer should be Jane Doe, Dan Jones,
and Sally Smith.

 

[OhioSteve]

I created a query called "conflict". This query finds all of the conflicts for a given date and time.

SELECT session.sTeacherID, session.sDate, session.sStart, session.sEnd
FROM [session]
WHERE (((session.sDate)=#2/10/2004#) AND ((session.sStart)>=#12/30/1899 10:5:0# And (session.sStart)<=#12/30/1899 11:0:0#)) OR (((session.sDate)=#2/10/2004#) AND ((session.sEnd)>=#12/30/1899 10:5:0# And (session.sEnd)<=#12/30/1899 11:0:0#)) OR (((session.sDate)=#2/10/2004#) AND ((session.sStart)<=#12/30/1899 10:5:0#) AND ((session.sEnd)>=#12/30/1899 11:0:0#));


MS Access added the "1899" dates to my sql. I just had ">=#10:05:00 AM# And <=#11:00:00 AM#"as the parameters for sStart and sEnd. The query worked in spite of the "1899" dates. It selected teacher ID 1.

The thinking behind this query is that any proposed session that conflicts with an existing session will follow one of three patterns:

proposed start
existing start
proposed end
existing end

OR

existing start
proposed start
existing end
proposed end

OR

existing start
proposed start
proposted end
existing end

 

[OhioSteve]

Okay, the last step is easy. You create a query of the teachers table that picks everything EXCEPT what appears in the conflict query:

SELECT teacher.name
FROM teacher LEFT JOIN conflict ON teacher.ID = conflict.sTeacherID
WHERE (((conflict.sTeacherID) Is Null));