You need the date and time in the one field.
If you have 4 million calls, I suggest you sort this out first (with an
Update query.)
--
Allen Browne - Microsoft MVP. Perth, Western Australia
Reply to group, rather than allenbrowne at mvps dot org.
2 calls overlap when:
- A starts before B ends, AND
- B starts before A ends, AND
- A is not B.
For any call, you can therefore get the count of the other overlapping
calls.
One way to approach this would be as a subquery. Something like this:
(SELECT Count("*") AS HowMany
FROM Table1 AS Dupe
WHERE Table1.StartDateTime < Dupe.EndDateTime
AND Dupe.StartDateTime < Table1.EndDateTime
AND Dupe.ID <> Table1.ID)
If subqueries are new, here's a starting point:
http://allenbrowne.com/subquery-01.html
Another way to approach it would be to create a query with 2 copies of the
same table and no join (i.e. a Cartesian product.) You can then use the
criteria above in a similar way.
Once you have the count of concurrent calls for every call, you will be
able
to select the highest number of concurrent calls from that list.
(Warning: Access is going to take a long time to run this if you have lots
of calls!)
Hello All,
Forgive me if I don’t provide much information as this is my first
ever post, here goes.
I have and Access Database that records information regarding incoming
and outgoing calls for our company, I would like to be able to se how
many calls are happening at any one time and see the highest number of
concurrent call. Each record in my database has a call_start_time and
a cal_duration. Any ideas how I can get what I want?
Cheers
Allen
I have the following data in my table called table1, i can not seem to
get your query to work
LocalExt StartDateTime EndDateTime Date Duration DateRecorded
401 16:24:00 16:24:00 20/08/2008 00:00:00 20/08/2008 16:25:48
353 16:27:00 16:27:43 20/08/2008 00:00:43 20/08/2008 16:31:25
203 16:27:00 16:30:41 20/08/2008 00:03:41 20/08/2008 16:33:03
208 16:36:00 16:36:32 20/08/2008 00:00:32 20/08/2008 16:39:39
364 16:40:00 16:40:00 20/08/2008 00:00:00 20/08/2008 16:41:28
364 16:41:00 16:41:00 20/08/2008 00:00:00 20/08/2008 16:43:18
364 16:42:00 16:42:00 20/08/2008 00:00:00 20/08/2008 16:43:34
245 16:41:00 16:41:53 20/08/2008 00:00:53 20/08/2008 16:43:36
364 16:43:00 16:43:02 20/08/2008 00:00:02 20/08/2008 16:44:18
260 16:43:00 16:43:02 20/08/2008 00:00:02 20/08/2008 16:44:19
364 16:43:00 16:43:00 20/08/2008 00:00:00 20/08/2008 16:45:01
364 16:43:00 16:43:00 20/08/2008 00:00:00 20/08/2008 16:45:02
249 16:42:00 16:43:40 20/08/2008 00:01:40 20/08/2008 16:45:04
254 16:44:00 16:44:10 20/08/2008 00:00:10 20/08/2008 16:45:41
254 16:44:00 16:44:00 20/08/2008 00:00:00 20/08/2008 16:45:49
254 16:44:00 16:44:00 20/08/2008 00:00:00 20/08/2008 16:45:57
331 16:27:00 16:44:58 20/08/2008 00:17:58 20/08/2008 16:46:28
290 16:48:00 16:48:35 20/08/2008 00:00:35 20/08/2008 16:50:01
364 16:49:00 16:49:00 20/08/2008 00:00:00 20/08/2008 16:50:13
364 16:49:00 16:49:00 20/08/2008 00:00:00 20/08/2008 16:50:16
355 16:49:00 16:49:11 20/08/2008 00:00:11 20/08/2008 16:52:41
401 16:50:00 16:50:03 20/08/2008 00:00:03 20/08/2008 16:52:41
329 16:50:00 16:50:00 20/08/2008 00:00:00 20/08/2008 16:52:41
364 16:51:00 16:51:00 20/08/2008 00:00:00 20/08/2008 16:52:41
364 16:51:00 16:51:00 20/08/2008 00:00:00 20/08/2008 16:52:41
364 16:51:00 16:51:00 20/08/2008 00:00:00 20/08/2008 16:52:41
222 16:50:00 16:50:33 20/08/2008 00:00:33 20/08/2008 16:52:41
260 16:51:00 16:51:02 20/08/2008 00:00:02 20/08/2008 16:52:48
364 16:51:00 16:51:03 20/08/2008 00:00:03 20/08/2008 16:52:49
