Complicated Query

[crozfader]

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!

 

[Norie]

It would be far easier to do this with VBA.

One reason is because Access doesn't actually store records sequentially.

This sort of analysis is much easier to do in Excel.

 

[alansidman]

crosspost
http://www.access-programmers.co.uk/forums/showthread.php?p=1096900&posted=1#post1096900

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

 

[xenou]

crozfader, be sure to read the link alan gave you in access world forums, so you understand why cross posting is a poor way to treat those who might be helping you:
http://www.excelguru.ca/node/7