Calculate average date difference between records in MS Access

Question

I have a list on when items have been handed out. The table has the following structure:

• primary key - autonumber itemname
• itemid - number
• datehandedout - date/time

I want to calculate the average length of time between when one object is given out and the next one is given out. There will be a number of different items for which the average time between handouts needs to be listed for.

So something like (pseudocode):

average( [thisrecord]![datehandedout] - [lastrecord]![datehandedout] )

Any help will be much appreciated.

Answer 1

This is a very slow query:

SELECT Avg(DateDiff("h",[datehandedout],(
    SELECT TOP 1 datehandedout 
    FROM tbl tx 
    WHERE tx.datehandedout > t.datehandedout))) AS Difference
FROM tbl AS t

Add another Where statement to limit the number of records returned when you test, for example:

WHERE Year([datehandedout])=2010