Calculating the number of days between orders

[INKEYPR]

Question: In my Access table ORDERS, I have two objects, [CustomerID] and [OrderDate]. Each customer has many order dates and I am interested in doing analysis to determine the actual number of days between orders. I am looking for a solution that I can do in the QBE grid and would consider a procedure I can type in a module and use in the QBE.

So far I have used the suggestion to bring the same table in twice into my query and join on CustomerID. In my QBE, I have CustomerID; OrderDate from ORDERS; OrderDate from ORDERS_1; and DaysBetweenOrders: DateDiff("d",([ORDERS]![OrderDate]),([ORDERS_1]![OrderDate]))

Sample Table:
CustomerID OrderDate
1 3/24/04
1 5/3/04
1 9/20/04

My goal is to calculate the days between OrderDate 3/24/04 and 5/3/04 as well as 5/3/04 and 9/20/04. Some Customers have up to 10 order dates in one year.

Here is a sample of my results:
CustomerID Date1 Date2 Diff
1 3/23/04 5/3/04 41
1 3/23/04 9/20/04 181
1 5/3/04 9/20/04 140

It seems as though I have a extra calculation I do not need (the difference between 3/23/04 and 9/20/04). I'd appreciate any ideas and help.

Thank you in advance.







Thanks,

 

[dhookom]

Try this SQL:

SELECT Orders.CustomerID, Orders.OrderDate as Date1, Orders_1.OrderDate as Date2, 
DateDiff("d",[Orders]![OrderDate],[Orders_1].[OrderDate]) AS Diff
FROM Orders AS Orders_1 INNER JOIN Orders ON Orders_1.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)<[orders_1]![OrderDate]));

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[PHV]

Something like this ?
SELECT A.CustomerID, A.OrderDate AS Date1, Min(B.OrderDate) AS Date2, Int(Min(B.OrderDate)-A.OrderDate) AS Diff
FROM Orders AS A INNER JOIN Orders AS B ON A.CustomerID = B.CustomerID
WHERE A.OrderDate < B.OrderDate
GROUP BY A.CustomerID, A.OrderDate

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[INKEYPR]

Hi dhookom.

I appreciate your quick response. Even with the great expression, I am still getting results that calculates the min([OrderDate]) from the max([OrderDate])-Refer to my first email on the record that calculated the diff of 181 days. This is OK if I have only two order dates, but is unnecessary when I have 3 or more and in fact gets confusing.

Thank you again,

 

[INKEYPR]

Hi PHV,

Thank you for your clarification. I get it now. Everything works great.

Take Care,