Finding Concurrent Events 1

[BrianLe]

I tried to solve this in Excel, but I didn't have much luck, so I'm going to try with Access which is probably better anyway.

Using Access 2007, I have the following tblEvents:

ID SiteNo EventNo EventStart EventEnd EventDesc
1 1 1 1/1/2011 1/5/2011 a
2 1 2 1/11/2011 1/15/2011 b
3 2 1 1/9/2011 1/10/2011 c
4 2 2 1/14/2011 1/17/2011 d
5 3 1 1/13/2011 1/16/2011 e
6 3 2 1/18/2011 1/21/2011 f

I would like to find the dates of concurrent events, i.e. the dates when there are events happening at all three sites simultaneously.

In this example, it would be 1/14 - 1/15/2011.

I've tried various forms of the following without success.

SELECT tblEvents.SiteNo, tblEvents.EventNo, tblEvents.EventStart, tblEvents.EventEnd, tblEvents.[EventStart]
FROM tblEvents
WHERE (((tblEvents.[EventStart]) Between [EventStart] And nz([EventEnd],[EventStart])));

It just gives me all the events.

Thanks,

Brian

 

[Golom]

Here's one approach. You need to create a query that will give you a record for each date in the range. To do that, create a table called "Integers" with one field called "Num". Populate it with the digits from zero to nine. Then

SELECT E.Event, E.Site, E.DateStart+I.Num AS Dt
FROM AEvents AS E, Integers AS I
Where E.DateStart + I.Num <= E.DateEnd
ORDER BY 1,2, 3

I called this query "EventsByDate". Then

SELECT ED.Dt, count(*) As Occurs
FROM EventsByDate AS ED
Group By ED.Dt

Which gives you a display like

Dt	     Occurs
1/1/2011	1
2/1/2011	1
3/1/2011	1
4/1/2011	1
5/1/2011	1
9/1/2011	1
10/1/2011	1
11/1/2011	1
12/1/2011	1
13/1/2011	2
14/1/2011	3
15/1/2011	3
16/1/2011	2
17/1/2011	1
18/1/2011	1
19/1/2011	1
20/1/2011	1
21/1/2011	1

You can of course do some filtering on that to show only the dates meeting your criteria (14th and 15th in this case.)

 

[BrianLe]

Golom,

That's just what I was looking for. I'll fine tune it as you noted.

This is what I ended up with using my table and field names.

SELECT E.EventDesc, E.SiteNo, E.EventNo, E.EventStart+I.Num AS Dt
FROM tblEvents AS E, tblIntegers AS I
WHERE ((([E].[EventStart]+[I].[Num])<=[E].[EventEnd]))
ORDER BY 1, 2, 3;

SELECT ED.Dt, Count(*) AS Occurs
FROM EventsByDate AS ED
GROUP BY ED.Dt;

Thanks,

Brian