Date Difference between consecutive rows

Question

I have a table with following structure

ID     Account Number     Date 1      1001               10/9/2011 (dd/mm/yyyy) 2      2001               1/9/2011 (dd/mm/yyyy) 3      2001               3/9/2011 (dd/mm/yyyy) 4      1001               12/9/2011 (dd/mm/yyyy) 5      3001               18/9/2011 (dd/mm/yyyy) 6      1001               20/9/2011 (dd/mm/yyyy) 

Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number The expected result would be !!

1001      10/9/2011 - 12/9/2011     2 days 1001      12/9/2011 - 20/9/2011     8 days 1001      20/9/2011                 NA 

Basically what i would like to do is have an access query that calculates the date difference for consecutive records but for the same account number , in the above example would be 1001. (the dates don't have to be shown in the result)

I use access 2003.

Answer 1

SELECT  T1.ID,          T1.AccountNumber,          T1.Date,          MIN(T2.Date) AS Date2,          DATEDIFF("D", T1.Date, MIN(T2.Date)) AS DaysDiff FROM    YourTable T1         LEFT JOIN YourTable T2             ON T1.AccountNumber = T2.Accountnumber             AND T2.Date > T1.Date GROUP BY T1.ID, T1.AccountNumber, T1.Date; 

or

SELECT  ID,         AccountNumber,         Date,         NextDate,         DATEDIFF("D", Date, NextDate) FROM    (   SELECT  ID,                      AccountNumber,                     Date,                     (   SELECT  MIN(Date)                          FROM    YourTable T2                         WHERE   T2.Accountnumber = T1.AccountNumber                         AND     T2.Date > T1.Date                     ) AS NextDate             FROM    YourTable T1         ) AS T