SQL Query, Clashing events

[marcellvries]

Hi there,

I'm designing a planning system in MS Access and currently I'm working on a query to identify events that overlap. Until now I have the following, which checks for the same employee (WerknemerID) assigned to multiple events at the same time.

SELECT tblPlanning.PlanningID, tblPlanning.WerknemerID, tblPlanning.Bweergavedatum, tblPlanning.Edatum, tblPlanning.Btijd, tblPlanning.Etijd, tblPlanning_1.PlanningID, tblPlanning_1.WerknemerID

FROM tblPlanning, tblPlanning AS tblPlanning_1

WHERE (((Nz(([tblPlanning_1].[Btijd]>=[tblPlanning].[Etijd]) Or ([tblPlanning_1].[Etijd]<=[tblPlanning].[Btijd]) Or ([tblPlanning].[WerknemerID]<>[tblPlanning_1].[WerknemerID]) Or ([tblPlanning].[PlanningID]=[tblPlanning_1].[PlanningID]),False))=False))

ORDER BY tblPlanning.PlanningID, tblPlanning_1.PlanningID;

The SQL statement above works, but doesn't take into account different dates. So when an employee is assigned to a task on saturday and a task on sunday, the query gives as a result that they overlap. What should I do next? I tried for hours but can't think of a solution. I'm quite a newbie when it comes to databases and queries..

For your information: WerknemerID = Employee, Btijd = Start time, Etijd = End time, PlanningID = Event ID, Bweergavedatum = Starting date, Edatum = End date.

Thank you in advance for your help!

With kind regards,

Marcell

 

[Golom]

Are these

Btijd           = Start time
Etijd           = End time
Bweergavedatum  = Starting date
Edatum          = End date

real DateTime fields?

If they are then you can combine them

Bweergavedatum + Btijd        = Start DateTime
Edatum + Etijd                = End DateTime

And your SQL might be something like

SELECT P1.PlanningID, P1.WerknemerID, P1.Bweergavedatum, 
       P1.Edatum, P1.Btijd, P1.Etijd, P2.PlanningID, P2.WerknemerID

FROM tblPlanning As P1, tblPlanning As P2

WHERE Nz(   P2.[Bweergavedatum] + P2.[Btijd]       >= P1.[Edatum] + P1.[Etijd]
         Or P2.[Edatum] + P2.[Etijd]               <= P1.[Bweergavedatum] + P1.[Btijd]
         Or P1.[WerknemerID]                       <> P2.[WerknemerID]
         Or P1.[PlanningID]                        =  P2.[PlanningID],False) =False

ORDER BY P1.PlanningID, P2.PlanningID

 

[marcellvries]

Hi Golem,

Thank you for your quick reply. These fields are indeed real DateTime fields. I just tried the following code and it seems to work!

SELECT tblPlanning.PlanningID, tblPlanning.WerknemerID, tblPlanning.Bweergavedatum, tblPlanning.Edatum, tblPlanning.Btijd, tblPlanning.Etijd, tblPlanning_1.PlanningID, tblPlanning_1.WerknemerID, [tblPlanning].[Bdatum]+[TblPlanning].[Btijd] AS VolDatum

FROM tblPlanning, tblPlanning AS tblPlanning_1

WHERE (((Nz((tblPlanning_1.Bweergavedatum+tblPlanning_1.Btijd>=tblPlanning.Edatum+tblPlanning.Etijd) Or (tblPlanning_1.Edatum+tblPlanning_1.Etijd<=tblPlanning.Bdatum+tblPlanning.Btijd) Or (tblPlanning.WerknemerID<>tblPlanning_1.WerknemerID) Or (tblPlanning.PlanningID=tblPlanning_1.PlanningID),False))=False))

ORDER BY tblPlanning.PlanningID, tblPlanning_1.PlanningID;

Thank you Golem, now I can go on to the next part

With kind regards,

Marcell