Complicated Query (Interleave rows?) (1 Viewer)

Hi everyone I need to create a query that does the following:

I have:

ITEM_NUMBER TRANSACTION_DATES
XYZ 01/02/2011
XYZ 02/02/2011
XYZ 05/02/2011
ZZZ 01/02/2010
ZZZ 01/08/2010
WWW 01/01/2011


etc...

For each product I want to create a query that will return the average time between transactions...

In this case, for item number XYZ there are three transactions. The interval between sales are: 1 day (difference between 02/02/2011 and 01/02/2011) and 3 days (difference between 05/02/2011 and 02/02/2011). The average in this case would be 2 days (3+1/2) for item XYZ.


I also need to calculate the standard deviation of the intervals between sales.

I think that this is done in two steps.
-1) Find a way to add a new column that will contain the difference between a given row and the row preceding it (maybe using some sort of inner join with a sequence?)
- 2) Once that column is created, use a simple group by to calculate avg and stdev

I haven't been able to do this, anyone has ideas?

Thanks!

crosspost http://www.accessforums.net/queries/complicated-query-interleave-rows-17223.html

http://www.mrexcel.com/forum/showthread.php?t=579616

http://www.excelguru.ca/node/7

Start with a self join and select the Min of date on the right side of the join where it is greater than the corresponding left record. Group by Product.

This will give you the sequential record pairs by product. The rest I think you have worked out already.