Checking Availability

[Question Boy]

I am having a hard time writing a query to check for availability (time wise).

I have a table 'tblUsr_Avail' which has 3 fields 'sDate','sTime','eTime',
where sDate is the date the usr has booked off, sTime is the start time they
have booked off and eTime is the end time they booked off. basically the
sTime and eTime define a window when the user is not available.

Now for the problem. i am trying to write a query to check if a booking
window of time conflicts with the time booked off by the user and am getting
nowehere. The logic is eluding me because there are so many possibilities.
can someone offer any guidance or an example that I might learn how to do
this.

A concrete example:
Suppose the user booked off today (sDate='2008-10-23') between 11:00 (sTime)
and 13:30 (eTime) and we are trying to validate with a query that an
appointment scheduled today from 10:30 until 12:00 does not conflict, How
would I structure my SQL to check?

Thank you so very much for your help!!!

QB

 

[Dale Fye]

Do you have multiple people that might provide service during the period?
Are your sDate and sTime/eTime fields date/time fields, or are they text?

In my mind, the best way to accomplish this is to check and see if the
individual (or any individuals are already booked for that period.

To determine this, you can write a query to identify those individuals that
are book for any period that involves the new scheduled appointment.
Assuming that you have the newApptStart and End fields that are date/time
fields, determining which individuals are booked during that period would be
easy with:

SELECT DISTINCT ID
FROM yourTable
WHERE sDate + sTime <= forms!yourForm.newApptEnd
AND sDate + eTime >= forms!yourForm.newApptStart

The following query first (subquery) identifies those individuals who are
busy at any point during the new appointment time. It then joins the result
of that query to the Employees table and identifies Mechanics (you will have
to modify this part to identify the employees that meet the requirements for
the appointment) that are not already busy.

SELECT ID, EmployeeName
FROM Employees
LEFT JOIN
(SELECT DISTINCT ID
FROM yourTable
WHERE sDate + sTime <= forms!yourForm.newApptEnd
AND sDate + eTime >= forms!yourForm.newApptStart) as Busy
on Employees.ID = Busy.ID
WHERE EmployeeType = "Mechanic"
AND Busy.ID IS NULL
--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.

 

[Allen Browne]

Could I encourage you to combine the starting date-and-time into one field,
and store the ending date-and-time (or the duration in minutes) in another
field?
Advantages:
- Simplifies the searches (saves adding dates and times every time you want
to check for clashes);
- Makes them more efficient to execute (JET can use indexes, which it cannot
with summed values);
- Much more flexible (copes with things that end after midnight, or last for
days.)

The core concept to identify clashes is that:
A starts before B ends, AND
B starts before A end,
A is not the same event as B.
Draw example of pairs of events on a piece of paper, and you will see that
covers it, e.g.:
A: --------------
B: -----
or this pair:
A: --------------
B: ------
and so on.

You can therefore test for a clash in the BeforeUpdate event procedure of
your form. This kind of thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strWhere As String
Dim varResult As Variant
Dim strMsg As String
Const strcJetDateTime = "\#mm\/dd\/yyyy hh\:nn\:ss\#"

If Not (IsNull(Me.sDateTime) Or IsNull(Me.eDateTime) Or IsNull(Me.ID)
Then
strWhere = "(sDateTime < " & Format(Me.eDateTime, strcJetDateTime) _
& ") AND (" & Format(Me.sDateTime, strcJetDateTime) _
& " < eDateTime) AND (ID <> " & Me.ID & ")"
varResult = DLookup("ID", "Table1", strWhere)
If Not IsNull(varResult) Then
strMsg = "Overlaps with event ID " & varResult & "." & _
"Continue anyway?"
If MsgBox(strMsg, vbYesNo+vbDefaultButton2, "Clash") = vbNo Then
Cancel = True
'Me.Undo
End If
End If
End If
End Sub