Trying To Convert Some Code Given To Me In A Previous Answer * HEL

[SEAN DI''''ANNO]

Please help someone...I posed the following question which was answered very
promptly by Gary...The orginal question is entitled Average Buying Cycle. He
answered the question but I am stuggling to convert his answer into the
fields which I use in my database. Would some one mind looking through the
below code which I have titled and give me a final piece of code....All will
be come clear if you read it but despite trying my best...Im stupId.

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.....

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;



BELOW IS THE CODE WHICH i WOULD USE TO GET THE BASIC FIELDS. THE ONLY
ADDITION IS A FIELD CALLED MAJOR WHICH i NEED. IN OTHER WORDS...I AM TRYING
TO WORK OUT THE AVERAGE BUYING CYCLE FOR ANYONE ORDERING FROM THE ASSET
MANAGEMENT PRODUCT RANGE


SELECT dbo_vwfOrders.ContactID, dbo_vwfOrderLines.OrderIdentity,
dbo_vwfOrderLines.ItemID, dbo_vwfOrders.OrderDate
FROM (dbo_vwfOrderLines INNER JOIN dbo_vwfOrders ON
dbo_vwfOrderLines.OrderIdentity = dbo_vwfOrders.OrderIdentity) INNER JOIN
dbo_vwfItems ON dbo_vwfOrderLines.ItemID = dbo_vwfItems.ItemID
WHERE (((dbo_vwfItems.Major)="Asset Managment"));

THIS IS MY ATTEMPT AT TRYING TO CONVERT GARYS CODE. I DID NOT KNOW HOW TO
ADD THE EXTRA FIELD (MAJOR) AND ALSO IN MY QUERY..THE JOIN BETWEEN O1 AND OD1
IS DIFFERENT IN DESIGN VIEW TO THE JOIN IN GARYS ANSWER SO I MHAVE MADE A
MISTAKE SOMEWHERE



SELECT O1.ContactID, OD1.ItemID, O1.OrderDate, (SELECT Max(O.OrderDate)
FROM DBO_VwfOrders As O INNER JOIN [DBO_VwfOrderLines] As OD ON
O.OrderIdentity=OD.OrderIdentity WHERE O.ContactID = O1.ContactID AND
OD.ItemID = OD1.ItemID AND O.OrderDate<O1.OrderDate) AS LastOrderDate,
O1.OrderDate-[LastOrderDate] AS BuyingCycle
FROM DBO_VwfOrders AS O1 INNER JOIN DBO_VwfOrderLines AS OD1 ON
O1.OrderIdentity = OD1.OrderIdentity
WHERE ((((SELECT Max(O.OrderDate) FROM DBO_VwfOrders As O INNER JOIN
[DBO_VwfOrderLines] As OD ON O.OrderIdentity=OD.OrderIdentity WHERE
O.ContactID = O1.ContactID AND OD.ItemID = OD1.ItemID AND
O.OrderDate<O1.OrderDate)) Is Not Null))
ORDER BY O1.ContactID, OD1.ItemID, O1.OrderDate;

 

[Gary Walter]

Hi Sean,

Sorry for delay...
had to do work that pays.

Correlated subqeries were also not easy
for me to understand at first, but I am
going to try to explain them in a way
that they make sense to me (and hopefully
it will help you).

First, let's imagine some simple data for
your 3 views

dbo_vwfOrders

ContactID OrderDate OrderIdentity
1 1/1/2006 A
2 2/1/2006 B
1 2/1/2006 C
2 3/1/2006 D

so I will assume ContactID's point to
specific customers, OrderIdentity is
a specific order that may have 1 or
more products that were ordered on
the OrderDate.

dbo_vwfOrderLines

OrderIdentity ItemID
A 100
A 200
B 100
C 100
D 300

I will assume ItemID's point to
specific products.

So, on 1/1/2006, "customer" 1
ordered "product" 100 and "product" 200
and we recognise that order as A.
On 2/1/2006 that same "customer"
ordered "product" 100 again.
It was the only product ordered on
the order we recognise as C.

dbo_vwfItems

ItemID Major
100 Asset Management
200 Asset Management
300 something else
400 another distinct else

I will assume each distinct "product"
can be part of a major group.

At this point we only care about
those "products" that are a part of the
major group "Asset Management"

so.... if we open the following query
in Query Designer

SELECT
dbo_vwfOrders.ContactID,
dbo_vwfOrders.OrderDate,
dbo_vwfOrderLines.OrderIdentity,
dbo_vwfOrderLines.ItemID
FROM
(dbo_vwfOrders
INNER JOIN
dbo_vwfOrderLines
ON
dbo_vwfOrders.OrderIdentity = dbo_vwfOrderLines.OrderIdentity)
INNER JOIN
dbo_vwfItems
ON
dbo_vwfOrderLines.ItemID = dbo_vwfItems.ItemID
WHERE
dbo_vwfItems.Major = "Asset Managment";

we should see 3 tables in Design View.

Right-mouse click on dbo_vwfOrders
and choose properties.
Change its Alias to O

Right-mouse click on dbo_vwfOrderLines
and choose Properties
Change its Alias to OL

Right-mouse click on dbo_vwfItems
and choose Properties
Change Alias to I

Set the Sort rows to Ascending

If we look at the query in SQL View,
we should see something like:

SELECT
O.ContactId,
O.OrderDate,
OL.OrderIdentity,
OL.ItemID
FROM
(dbo_vwfOrders AS O
INNER JOIN
dbo_vwfOrderLines AS OL
ON O.
OrderIdentity = OL.OrderIdentity)
INNER JOIN
dbo_vwfItems AS I
ON
OL.ItemID = I.ItemID
WHERE (((I.Major)="Asset Management"))
ORDER BY
O.ContactId,
O.OrderDate,
OL.OrderIdentity,
OL.ItemID;


If we save and run the query with our
sample data, we should see

ContactId OrderDate OrderIdentity ItemID
1 1/1/2006 A 100
1 1/1/2006 A 200
1 2/1/2006 C 100
2 2/1/2006 B 100

Okay...here's the first thing that may help...

Think about the "step-by-step" process
that Access goes through to determine
what to return for each record in the result.
Slow it all down.

-look at the first record in O
return it and the first record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
return it and the next record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
{continue until no more matches}
-look at the next record in O
return it and the first record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
return it and the next record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
{continue until no more matches}
-look at the next record in O
return it and the first record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
return it and the next record in OL where [OrderIdentity] match
(if Major in I where [ItemID] match = "Asset Management")
{continue until no more matches}
-{continue until no more O records}

At one of those "steps", we are going to come to the record
where customer 1 ordered product 100 *again* (order C
on 2/1/2006).

At that "step" we'd like to know the last time customer 1
ordered that product 100. Let's figure it out in another
separate query.

We'd like to look in the same set of data,
** but before 2/1/2006 **,
and find the latest (Max) order date that
customer 1 ordered product 1.

SELECT
Max(O1.OrderDate)
FROM
(dbo_vwfOrders AS O1
INNER JOIN
dbo_vwfOrderLines AS OL1
ON
O1.OrderIdentity = OL1.OrderIdentity)
INNER JOIN
dbo_vwfItems AS I1
ON
OL1.ItemID = I1.ItemID
WHERE
I1.Major = "Asset Management"
AND
O1.ContactID = 1
AND
OL1.ItemID = 100
AND
O1.OrderDate < #2/1/2006#

Sure enough, when we run the query,
LastOrderDate = 1/1/2006

We just created our *uncorrelated* query
for the specific "step" in our previous query
where
customer = 1
product = 100
orderdate = 2/1/2006

We can stick this in as a subquery in
our previous query, but, at each step,
we'd like it to *correlate* back to the
values for customer, product, orderdate
in the outer query at that step.

Well, we've used different aliases for
our tables, so we can *correlate* back to
the "step-by-step" outer query by replacing
the *actual values* in our subquery WHERE
clause with the *outer aliases*.

WHERE
I1.Major = "Asset Management"
AND
O1.ContactID = O.ContactID
AND
OL1.ItemID = OL.ItemID
AND
O1.OrderDate < O.OrderDate

In SQL View, temporarily make these changes.

Copy this new subquery's SQL (no ending semicolon),
then go back to the original "step-by-step" query in
Design View,

in a field row of a new column in the grid, type

LastOrderDate: (

then paste in the subquery SQL

then end it with closing parenthesis.

Save and look at results.

here would be the monster SQL that earlier
was so hard to figure out (remember those
days.... ):

SELECT
O.ContactId,
O.OrderDate,
OL.OrderIdentity,
OL.ItemID,
(SELECT
Max(O1.OrderDate) AS LastOrderDate
FROM
(dbo_vwfOrders AS O1
INNER JOIN
dbo_vwfOrderLines AS OL1
ON
O1.OrderIdentity = OL1.OrderIdentity)
INNER JOIN
dbo_vwfItems AS I1
ON OL1.ItemID = I1.ItemID
WHERE
I1.Major = "Asset Management"
AND
O1.ContactID = O.ContactID
AND
OL1.ItemID = OL.ItemID
AND
O1.OrderDate < O.OrderDate) As LastOrderDate
FROM
(dbo_vwfOrders AS O
INNER JOIN
dbo_vwfOrderLines AS OL
ON
O.OrderIdentity = OL.OrderIdentity)
INNER JOIN
dbo_vwfItems AS I
ON
OL.ItemID = I.ItemID
WHERE (((I.Major)="Asset Management"))
ORDER BY
O.ContactId,
O.OrderDate,
OL.OrderIdentity,
OL.ItemID;

and here be results for our simple data:

ContactId OrderDate OrderIdentity
ItemID LastOrderDate
1 1/1/2006 A 100
1 1/1/2006 A 200
1 2/1/2006 C 100 1/1/2006
2 2/1/2006 B 100

All that's left is to compute difference
between OrderDate and LastOrderDate
(filtering for LastOrderDate IS NOT NULL)

I think that might help?

good luck,

gary