datediff for different records

G

gmann

I am trying to determine the amount of time between calls from various
customers.
I capture why and when they call, however I would like to know which
customers had to call back for the same reason on a different day. Is there a
way to determine the time difference as they are in 2 different records?
 
A

Allen Browne

If you don't mind a read-only, result, use a subquery to get the time of the
previous call for the same reason.

This example assumes a table named tblCall, with a date/time field named
CallTime, and a ReasonId that is assumed to be the same only if is the same
caller and the same reason. It calculates the number of hours back to the
previous call. Type the expression into the Field row in query design view:

PriorCall: DateDiff("h", [CallTime],
(SELECT CallTime FROM tblCall AS Dupe
WHERE (Dupe.ReasonId = tblCall.ReasonId)
AND (Dupe.CallTime < tblCall.CallTime))

If subqueries are new, see Microsoft's introduction:
How to Create and Use Subqueries
at:
http://support.microsoft.com/?id=209066
 
Top