Calculating Reorder Cycles

[Guest]

I have a database of order history for all customers for a client. The
client wishes to know their average reorder cycle time. Account holders can
have as few as one order in the 7 year measurement period and some have as
many as 60. How do I setup a query or visual basic function to extract all
orders for an account, measure the time in days in between each order,
average the days for the account and then average the results across all
accounts for the client?

 

[Guest]

Try the following (untested)...

save
Select
Client,
Customer,
Min(OrderDate) FirstDate,
max(orderdate) LastDate,
Count(*) as Orders
From
tblOrders
Group By
Client,
Customer
Having
Count(*) > 1

as qryTemp
and then execute

Select
Client,
Customer,
(DateDiff('d',[LastDate],[FirstDate]))/Orders AvDays
From
QryTemp

 

[Guest]

Oops, sorry, in the last post the second query should read

Select
Client,
Customer,
(DateDiff('d',[LastDate],[FirstDate]))/(Orders-1) AvDays
From
QryTemp

 

[Guest]

Thanks for the suggestion. I'll give it a shot and see if it works!