Count repeating occurrences within time period <168 hours

[Number101]

If I have a large table (100000 + entries) which have call records. How can I count the instances of re-occurrence within a set number of hours?


My original data is:
1st column: call Date (date and time format) 2nd column: Caller ID; 3rd column person who answered.
My problem is that I cant count it in loop (if a person calls:
1. 2017.05.12 07:00 (Should count as reoccurrence, because after that client called again in168 hour period.)
2. 2017.05.18 17:00 (Should count as reoccurrence, because after that client called again in 168 hour period from the first call)
3. 2017.05.18 19:00 (Shouldn't count as reoccurrence, because client called again but it was after 168 hour period from first call)
4. 2017.05.25 10:15 (Should count as reoccurrence, because after that client called again in 168 hour period(start a new 168 hour cycle))
5. 2017.05.26 15:17 (Shouldn't count as reoccurrence, because client didn't called again))
I need all the help I can get

In some forum I found a code for sql server that looks about right but I cant adapt it to access:

declare @t table(Record_ID int, Customer_ID int, StartDateTime datetime, FinishDateTime datetime)

insert @t values(1 ,123456,'2010-04-24 16:49','2010-04-25 13:37')
insert @t values(3 ,654321,'2010-05-02 12:45','2010-05-03 18:48')
insert @t values(4 ,764352,'2010-03-24 21:36','2010-03-29 14:24')
insert @t values(9 ,123456,'2010-04-28 13:49','2010-04-30 09:45')
insert @t values(10,836472,'2010-03-19 19:05','2010-03-20 14:48')
insert @t values(11,123456,'2010-05-05 11:26','2010-05-06 16:23')

declare @days int
set @days = 7

;with a as (
select record_id, customer_id, startdatetime, finishdatetime,
rn = row_number() over (partition by customer_id order by startdatetime asc)
from @t),
b as (
select record_id, customer_id, startdatetime, finishdatetime, rn, 0 recurrence
from a
where rn = 1
union all
select a.record_id, a.customer_id, a.startdatetime, a.finishdatetime,
a.rn, case when a.startdatetime - @days < b.finishdatetime then recurrence + 1 else 0 end
from b join a
on b.rn = a.rn - 1 and b.customer_id = a.customer_id
)
select record_id, customer_id, startdatetime, recurrence from b
where recurrence > 0

[CJ_London]

not quite sure what result you want but something like

SELECT CallerID, count(*) AS reoccurence
FROM myTable
WHERE calldate between [Enter earlier date in format mm/dd/yyyy hh:mm] AND datediff("h",168,[Enter earlier date in format mm/dd/yyyy hh:mm])
GROUP BY CallerID

replace the two bits in square brackets with reference to a form control if required

don't understand your comment re record 5

and it always helps if you provide an example of the output you require from your sample data

[Number101]

Thank you but that code wouldn't work as I want, because it does not exclude the last call in 168 hours. Please explain what in my comment about record 5 is difficult to understand? Below is the output of the data I want, maybe I didn't called it properly, I don't need to count reoccurrences but to select them (Mark it as 1 if a client called within 168 hours from 1st call, don't mark it as 1 if it was the last call in 168 hour period from first call. If a client calls again after 168 hours from first call, it should start new marking and take that call as a firs call and from there should starts checking in 168 hours period)

Call time	Client_number	reoccurrence
2017.03.10	88873271	1
2017.03.16	88873271	1
2017.03.16	88873271	1
2017.03.17	88873271	0
2017.04.04	88873271	0
2017.04.14	88873271	1
2017.04.19	88873271	1
2017.04.19	88873271	1
2017.04.19	88873271	0