Complicated Query (Interleave rows?)

[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!

[June7]

This query will create a field that returns the previous date for each record. Then you can use it as the source for a query that calculates the difference of the two fields and then the statistical calcs.

SELECT ID, Item_Number, Transaction_Dates, (SELECT TOP 1 Dupe.Transaction_Dates FROM Table1 As Dupe WHERE Dupe.Item_Number=Table1.Item_Number AND Dupe.Transaction_Dates < Table1.Transaction_Dates ORDER BY Item_Number, Transaction_Dates) AS PriorValue FROM Table1;

[alansidman]

crossspost

http://www.access-programmers.co.uk/...=1#post1096900

http://www.mrexcel.com/forum/showthr...=1#post2867392

[crozfader]

Thank you very much for your help!

Your solution makes perfect sense, but I'm having an error when I execute the following query:

SELECT Item_Number, product, Trans_Date, (SELECT TOP 1 Dupe.Trans_Date FROM item_transactions As Dupe where Dupe.Item_Number=table1.Item_Number AND Dupe.Trans_Date < table1.Trans_Date ORDER by Item_Number, Trans_Date) AS PriorValue FROM item_transactions as table1;

I get a "At most one record can be returned from this subquery" if I use the alias table1 in the where statement (table1.item_number etc.). If I remove the table1 alias i.e.
where Dupe.Item_Number=Item_Number AND Dupe.Trans_Date < Trans_Date

then the query executes but priorvalue is simply empty.

Any thoughts?

Thx in advance

[June7]

Go back to my original query example. Every place you see Table1, replace with name of your table. Take out the ID field and include Product.

[crozfader]

Tried your suggestion...

doesn't work get that same error message. Any thoughts?


SELECT ITEM_NUMBER, PRODUCT, (SELECT TOP 1 DUPE.TRANS_DATE FROM ITEM_TRANSACTIONS AS DUPE WHERE DUPE.ITEM_NUMBER = ITEM_tRANSACTIONS.ITEM_NUMBER AND DUPE.PRODUCT = ITEM_TRANSACTIONS.PRODUCT AND DUPE.TRANS_DATE < ITEM_TRANSACTIONS.TRANS_dATE ORDER BY ITEM_NUMBER, TRANS_DATE) AS PRIORVALUE
FROM ITEM_TRANSACTIONS;

[June7]

I did not include Product in the WHERE clause. I said you could remove ID and include Product. This works with the sample data you posted.

SELECT Item_Number, Product, Trans_Date, (SELECT Top 1 Dupe.Trans_Date FROM Item_Transactions As Dupe WHERE Dupe.Item_Number = Item_Transactions.Item_Number AND Dupe.Trans_Date < Item_Transactions.Trans_Date ORDER BY Item_Number, Trans_Date) As PriorValue FROM Item_Transactions;

[crozfader]

I get the same error again. The error doesn't pop up immediately, it comes up after a couple of minutes of processing.

I think I know what is happening. The problem is probably that the query returns two records or more for a given line causing the error.

I'm not too familiar with what TOP 1 does, but can it return more than 1 record if it finds more than one match that satisfies all the criteria?

Is there a way to make sure it returns just one record?

Thanks for your help.

[June7]

Yes, it will try to do that. I built the query based on the sample data you provided. If that is not representative of the dataset, then provide for analysis. Attach Access file with just the needed table(s)