Finding Date Difference in same field

[Firasath]

Hi,

My requirement is to check if customer has called back within last 3 days with the same number. In excel, it is very simple I just need to put a formula "=AND(A3=A2,(B3-B2)<=3)" to check if it is True or not.

But as my data increases and went up to 1.5 Million records, I need to now depend on Access and I'm very new to it.

Kindly advise if is possible in Access.

Clip	Date	RepeatCall
24030637	03/04/2014 10:28	AND(A2=A1,(B2-B1)<=3)
24066005	02/04/2014 12:26	FALSE
24089702	01/04/2014 11:45	FALSE
24107732	03/04/2014 10:42	FALSE
24155138	03/04/2014 12:37	FALSE
24164129	01/04/2014 08:09	FALSE
24164129	01/04/2014 13:46	TRUE
24164129	03/04/2014 06:44	TRUE
24174000	02/04/2014 10:55	FALSE
24174000	03/04/2014 10:26	TRUE
24411477	01/04/2014 17:52	FALSE
24411477	02/04/2014 08:54	TRUE
24411477	03/04/2014 11:44	TRUE
24443366	03/04/2014 14:12	FALSE
24447228	02/04/2014 10:11	FALSE
24452332	03/04/2014 13:26	FALSE
24452332	03/04/2014 13:34	TRUE
24453098	01/04/2014 11:34	FALSE
24456651	02/04/2014 11:33	FALSE
24456651	02/04/2014 11:51	TRUE

<tbody>
</tbody>

 

[alansidman]

I would do this with two queries.
The first one (named: Query2) would filter for only the date range you want to determine if there were multiple calls. You can change the date range to whatever you wish. I used April 1 through April 2.

SELECT tblCalls.Clip, tblCalls.Date
FROM tblCalls
WHERE (((tblCalls.Date) Between #4/1/2014# And #4/2/2014#));

The second one would be an aggregate query that would count the number of calls within that date range by clip

SELECT Query2.Clip, Count(Query2.Date) AS CountOfDate
FROM Query2
GROUP BY Query2.Clip;

 

[Firasath]

Thanks. But I would like to know how many calls has been made more than one time.

Secondly, here in your excample, we need to specify the duration, but my requirement is to calculate on a given data for more than 3 days, say 1 month data, and see if any call is getting repeated within 3 days from the same Clip.

 

[alansidman]

But I would like to know how many calls has been made more than one time.

Did you test my solution? That is exactly what the second query returns.

I am not sure how you will achieve the second request. You can select a date range and test within that range as I have done. I think that if you want something more detailed, then you will have to try a VBA solution using recordsets and multiple if--then expressions.