Average Buying Cycle

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good morning,

I am stuck trying to create a query for working out a average buying cycle.
Very simply the task I have bene set is to work out how often customers buy a
certain product e.g. every 6 months and then produce a calling list of all
customers who ordered the average buying cycle amount ago. e.g. Customers who
ordered 6 months a go.

It would be easy if customers had only ordered twice becuase then I could
take the second date from the first and then work out the average. The
problem is a lot of customers have ordered more than twice and so I sm stuck
trying to work out an average buying cyle for these customers. I know in
theory it would be a case of

2nd date - 1st date...3rd date - 2nd date and so but I do not know how this
would work in a query.....

Please help.
 
SEAN DI''''ANNO said:
Good morning,

I am stuck trying to create a query for working out a average buying
cycle.
Very simply the task I have bene set is to work out how often customers
buy a
certain product e.g. every 6 months and then produce a calling list of all
customers who ordered the average buying cycle amount ago. e.g. Customers
who
ordered 6 months a go.

It would be easy if customers had only ordered twice becuase then I could
take the second date from the first and then work out the average. The
problem is a lot of customers have ordered more than twice and so I sm
stuck
trying to work out an average buying cyle for these customers. I know in
theory it would be a case of

2nd date - 1st date...3rd date - 2nd date and so but I do not know how
this
would work in a query.....
Click to expand...
Hi Sean,

Open up NorthWind db
and try this query

SELECT
O1.CustomerID,
OD1.ProductID,
O1.OrderDate,
(SELECT Max(O.OrderDate)
FROM
Orders As O
INNER JOIN
[Order Details] As OD
ON O.OrderID=OD.OrderID
WHERE
O.CustomerID = O1.CustomerID
AND
OD.ProductID = OD1.ProductID
AND
O.OrderDate<O1.OrderDate) AS LastOrderDate,
[O1].[OrderDate]-[LastOrderDate] AS BuyingCycle
FROM
Orders AS O1
INNER JOIN
[Order Details] AS OD1
ON O1.OrderID = OD1.OrderID
ORDER BY
O1.CustomerID,
OD1.ProductID,
O1.OrderDate;

look at the results

then in Design View,
under LastOrderDate column in "Criteria" row of grid, type

IS NOT NULL

look at results

save query as "qryBuyingCycle"

Then just save following query to get
your avg buying cycle for each product
(that has been purchased by a customer
more than once)

SELECT
qryBuyingCycle.ProductID,
Avg(qryBuyingCycle.BuyingCycle) AS AvgOfBuyingCycle
FROM qryBuyingCycle
GROUP BY
qryBuyingCycle.ProductID;
 
Thank you Gary for taking the time to give me the code for solving my
problem. The results are superb but...pushing my luck. Can you in explain
in layman terms why it works?


Gary Walter said:
SEAN DI''''ANNO said:
Good morning,

I am stuck trying to create a query for working out a average buying
cycle.
Very simply the task I have bene set is to work out how often customers
buy a
certain product e.g. every 6 months and then produce a calling list of all
customers who ordered the average buying cycle amount ago. e.g. Customers
who
ordered 6 months a go.

It would be easy if customers had only ordered twice becuase then I could
take the second date from the first and then work out the average. The
problem is a lot of customers have ordered more than twice and so I sm
stuck
trying to work out an average buying cyle for these customers. I know in
theory it would be a case of

2nd date - 1st date...3rd date - 2nd date and so but I do not know how
this
would work in a query.....
Click to expand...
Hi Sean,

Open up NorthWind db
and try this query

SELECT
O1.CustomerID,
OD1.ProductID,
O1.OrderDate,
(SELECT Max(O.OrderDate)
FROM
Orders As O
INNER JOIN
[Order Details] As OD
ON O.OrderID=OD.OrderID
WHERE
O.CustomerID = O1.CustomerID
AND
OD.ProductID = OD1.ProductID
AND
O.OrderDate<O1.OrderDate) AS LastOrderDate,
[O1].[OrderDate]-[LastOrderDate] AS BuyingCycle
FROM
Orders AS O1
INNER JOIN
[Order Details] AS OD1
ON O1.OrderID = OD1.OrderID
ORDER BY
O1.CustomerID,
OD1.ProductID,
O1.OrderDate;

look at the results

then in Design View,
under LastOrderDate column in "Criteria" row of grid, type

IS NOT NULL

look at results

save query as "qryBuyingCycle"

Then just save following query to get
your avg buying cycle for each product
(that has been purchased by a customer
more than once)

SELECT
qryBuyingCycle.ProductID,
Avg(qryBuyingCycle.BuyingCycle) AS AvgOfBuyingCycle
FROM qryBuyingCycle
GROUP BY
qryBuyingCycle.ProductID;
Click to expand...
 
Back
Top