Finding Repeat Callers

[Inzzane]

I have a need to identify people that call in multiple times within a 3 hour period to different phone numbers within a company, but haven't figured out how to go about figuring out how to do the hours calculation/code for the recordset.


Table Fields (Smple Data):

ID	SourceName	Session Id	From Name	From Number	To Name	To Number	Result	Call Length	Handle Time	Call Start Time	Call Direction	Queue
387449	Daily_Calls_07_16_2021_07_17_28_AM.xlsx	618204818008		(201) 444-2233	Ryan	(813) 555-6135	Answered	02:19	02:38	7/16/2021 13:39	Inbound
387565	Daily_Calls_07_16_2021_07_17_28_AM.xlsx	618176746018		(201) 444-2233	Ryan	(813) 555-6135	Answered	01:15	01:30	7/16/2021 13:19	Inbound
377407	Daily_Calls_07_16_2021_07_43_29_AM.xlsx	363986948009		(201) 444-2309	John	(813) 555-0555	Answered	01:33	01:26	7/15/2021 11:36	Inbound
381065	Daily_Calls_07_16_2021_11_01_27_AM.xlsx	363986948012		(201) 444-2309	Ryan	(813) 555-6135	Answered	01:53	01:44	7/15/2021 11:41	Inbound
384723	Daily_Calls_07_16_2021_12_20_13_PM.xlsx	363986948019		(201) 444-2309	Bill	(813) 555-0777	Answered	04:18	04:02	7/15/2021 11:55	Inbound
360079	Daily_Calls_07_16_2021_07_02_52_AM.xlsx	361126784009		(201) 444-6824	Anna	(813) 555-1234	Answered	03:36	03:26	7/7/2021 12:54	Inbound

So for the attached example, I'm looking at the 'From Number' and identifying which ones are repeat callers, and placing them in the order they called. I then need to identify if they called multiple people, or just one contact. I'm only looking for phone numbers that called multiple people within a 3 hour period.

Where I'm geting stuck is the conversion from the query results above, to the query below. I know I need to loop through the recordsets, but not sure how to go about calculating the 3 hour time limit necessary.

For the example above, the results should be:

ID	SourceName	Session Id	From Name	From Number	To Name	To Number	Result	Call Length	Handle Time	Call Start Time	Call Direction	Queue
377407	Daily_Calls_07_16_2021_07_43_29_AM.xlsx	363986948009		(201) 444-2309	John	(813) 555-0555	Answered	01:41	01:26	7/15/2021 11:36	Inbound
381065	Daily_Calls_07_16_2021_11_01_27_AM.xlsx	363986948012		(201) 444-2309	Ryan	(813) 555-6135	Answered	01:53	01:44	7/15/2021 12:41	Inbound
384723	Daily_Calls_07_16_2021_12_20_13_PM.xlsx	363986948019		(201) 444-2309	Bill	(813) 555-0777	Answered	04:18	04:02	7/15/2021 13:55	Inbound

I would like to see rows 3,4, and 5 as the result, as this caller had called 3 people in less than 3 hour period. This caller (201) 444-2309, called into 3 seperate people within the 3 hour period.

Any advice would be appreciated!

-Ryan

Answered

[kd2017]

What three hour window? Within the three hours from the time you run the query?

[Micron]

IMO you're going to need a subquery to get the next TOP 1 record where start time of the sub is <= (start time of the outer query + 3) . However, there seems to be something wrong with your input query above as the first number is sorted descending and the next number ascending. Perhaps review subquery basics.

I would like to see rows 3,4, and 5 as the result

Why not rows 1 and 2? Did they not call 2x within 20 minutes?

[Inzzane]

The 3 hour time limit is any calls within a 3 hour period of the first, or last call of the day (any multiple calls within a 3 hour window). Basically the company sells product based on negotiated rates. I would like to determine if people are calling in the get an idea of the rate, then calling back other sales people and re-negotiating with different people to try to get a better deal.

[Inzzane]

For rows 1 & 2, I do not need to see them although they did called repeatedly, it was to the same phone number (Sales Person).

If they are calling back the same sales person, we're good with that. When we see one phone number calling a bunch of different sales people in a row, then we know they are trying to call repeatedly to "work the system" and can flag them for review later.

Edited: The first part of your description looks correct. I did do some edits to the sample data after pasting to remove real data. Originally I sort the 'From Number' either ascending or descending (just to group like phone numbers), And sort the time either ascending or descending to view the time difference. They can be filtered to whatever way works best as either can way can produce the results.

[kd2017]

Maybe you could start with something like this? :

DCount("ID","[YOUR TABLE NAME]","[Call Start Time]<#" & [Call Start Time] & "# AND [From Number]='" & [From Number] & "' AND [To Number]<>'" & [To Number] & "'")

I'll check out now and leave it to the pros.

[Micron]

Using a logical description, it seems to me you want to sort From numbers ASC 1st then by Start ASC in the first query. Then subquery WHERE (sub Start <= outer Start+3) AND sub To <> outer To AND sub From number = outer From number. Unfortunately this sort of thing is not my strong point so you might get better ideas soon.
EDIT - I now don't think you need the TOP predicate.

[Inzzane]

Using a logical description, it seems to me you want to sort From numbers ASC 1st then by Start ASC in the first query. Then subquery WHERE (sub Start <= outer Start+3) AND sub To <> outer To AND sub From number = outer From number. Unfortunately this sort of thing is not my strong point so you might get better ideas soon.
EDIT - I now don't think you need the TOP predicate.

Thnaks Micron, I'll definitely be researching more on Subqueries today, as well as the DCOUNT method kd2017 mentioned. This definitely seems like the right path to go. I'll keep you posted