Calc max concurrent connected calls in a halfhour intervals frm strt & end time

[HyperDreamz]

Hi All

Need a little help in calculating the maximum concurrent connected calls in a
half hour based on a dump of start time and end time of 4000000 call records
in ACCESS.

I have been supplied a raw database in ACCESS.

For your reference I have attached a sample EXCEL file to represent the data
I have in ACCESS.

http://www.4shared.com/file/59874296/66b6cafa/TEST.html


I need to populate columns “E†to “AZ†with the count.

For example

Start Time 05/08/2008 1:57 AM
Stop Time 05/08/2008 2:37 AM

Would have a count in the following intervals

01:30 to 02:00 = 1
02:00 to 02:30 = 1
02:30 to 03:00 = 1


Any help or suggestion would be highly appreciated.

Hyper Dreamz

 

[Michel Walsh]

I will use VBA and a recordset opened on the ordered date and time
(increasing). After having opened it, I will loop over all its records,
maintaining a running counter to be increased by one if the record is a
start time, and decreasing the counter by one if it is a stop time. After
having handled the said running counter, I will check if its actual value is
the maximum one for the appropriate 30 minutes the actual record belongs to
and if so, will store that maximum appropriately.

Vanderghast, Access MVP

 

[HyperDreamz via AccessMonster.com]

Solved

 

[John Spencer]

Good.

Would you care to post the solution so others may benefit?

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County