Querying & Counting records that have time-ranges that overlap.

[reynoldsmhr]

I desire to set up a query that will count the number of times that records within a range of dates also have time ranges that overlap. Let me explain in more detail:

At my fire department we log in an Access database each alarm to which we respond. I have built a query that pulls the Record #, the assigned Incident #, the date of the alarm, the year of the alarm, the month of the alarm, the time the ambulance or fire engine went enroute to the call, and the time the ambulance or fire engine was back in service. (The year and month data are produced by the year() and month() expressions in the query).

I want to be able to query and count the number of times each day, or each week, or each month, or each year in which we had 2 or more responses to alarms during the same time interval. In other words, if the enroute time/in service time interval of two or more alarm records overlap on any given day during a selected date range, I want to count them.

In short, I want to be able to count the number of times we had 2 or more ambulances or fire engines out at the same time.

I hope this makes sense to someone who can assist.

Thanks in advance,
Mike Reynolds, Fire Chief
Carlsbad, NM Fire Department

 

[spikepl]

For how to find whether two time periods overlap, have a look here: http://www.baldyweb.com/OverLap.htm

Your basic unit of overlap testing seems one day (although call-outs 11:45PM to 12:15 AM are overlapped too - and then which day do they belong to ?)

The tricky part is testing all the alarm calls against one another.

I would focus on finding overlaps between one alarm and the remainder within a given period.Then step up to all alarms against one another.

Or perhaps rather find time periods where there was more than one vehicle in play. In this way the count is not ambiguous.

Once that tough part is done, the counting should be easy.