Calculate the time interval

[Guest]

Hello:
I have a data set like the following
PersonID EncounterDate
1 Sep 1, 04
1 Sep 4, 04
2 Aug 2, 03
2 Jan 1, 03
2 Feb 9, 04
....
I want to calculate the time interval between each two encounters for each
person.
Some people only has one interval, but some may have several intervals. Is
that possible one query can handle it or I should other methods?
Thanks in advance!

 

[Ken Snell [MVP]]

You can use a subquery within a query to return the previous value.
Something like this should get you started (not tested) -- note that this
assumes that EncounterDate is a date/time field:

SELECT T.PersonID, T.EncounterDate, (DateDiff("d", (SELECT
Max(X.EncounterDate) AS PrevDate FROM TableName AS X WHERE X.PersonID =
T.PersonID AND X.EncounterDate < T.EncounterDate), T.EncounterDate)) AS
DateInterval FROM TableName AS T ORDER BY T.PersonID, T.EncounterDate DESC;

 

[Guest]

This really helps me a lot.
Thank you very much!
Vincent