Access Transform statement and Pivot statement

[Mister_joe]

Greetings to everyone on this forum. I was reading an Access VBA book and I came upon the following block of code:


TRANSFORM Sum(CCur([Order Details].[Unit Price]*[Quantity]*(1-[Discount])/100)*100) AS [Product Amount]
SELECT Products.[Product Name], Orders.[Customer ID], Year([Order Date]) AS [Order Year]
FROM Products INNER JOIN (Orders INNER JOIN [Order Details] ON Orders.[Order ID] = [Order Details].[Order ID]) ON Products.ID = [Order Details].[Product ID]
WHERE (((Orders.[Order Date]) Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.[Product Name], Orders.[Customer ID], Year([Order Date])
PIVOT "Qtr " & DatePart("q",[Order Date],1,0) IN ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");

I am simply confused about the ordering of the keywords. I am equally confused about the relationship between the TRANSFORM statement and the PIVOT statement. Please, people, between the TRANSFORM statement, the SELECT statement, the GROUP BY clause and the PIVOT statement, what is the order of execution?

Also, what exactly does the TRANSFORM statement do?
You won't believe how many days I have spent trying to understand this block of codes. I will really appreciate it if someone can give me a link to a resource that explains the inner working of the PIVOT statement and the TRANSFORM statement.

Thanks for your assistance.

Joe

[jonh]

http://www.paragoncorporation.com/ArticleDetail.aspx?ArticleID=25

[Mister_joe]

Thanks Mr. John. I recreated the table in the linked page. I tried to run the query, but it would not run. It looks like the Transform statement or the Pivot statement is throwing error. This is exactly why I am looking to understand how the Transform statement and the Pivot statement work together.

[jonh]

It's not possible to say why your crosstab query isn't working without seeing a copy of the table and your sql.

[Mister_joe]

Here is the query:

TRANSFORM Sum(Purchases.Amount) AS SumOfamount
SELECT Sum(Purchases.Amount) AS Total
FROM Purchases
WHERE Purchase_Date Between #1/8/2004# And #31/10/2004#
PIVOT "m" & Format([Purchase_Date],"yyyy_mm") In ("m2004_08","m2004_09","m2004_10")

The table, named Purchases, is shown in the image insert
18212

[jonh]

A crosstab query in Access needs row, header and value fields. That example only uses Amount and Date fields, so it's not right.

Instead of this

TRANSFORM Sum(Purchases.Amount) AS SumOfamount
SELECT Sum([Amount]) AS Total
FROM Purchases
WHERE Purchase_Date Between #1/8/2004# And #31/10/2004#
PIVOT "m" & Format([Purchase_Date],"yyyy_mm") In ("m2004_08","m2004_09","m2004_10")

I think it should be this


TRANSFORM Sum(Purchases.Amount) AS SumOfamount
SELECT Purchases.Purchase_Id
FROM Purchases
WHERE Purchase_Date Between #1/8/2004# And #31/10/2004#
GROUP BY Purchases.Purchase_Id
PIVOT "m" & Format([Purchase_Date],"yyyy_mm") In ("m2004_08","m2004_09","m2004_10")

[Mister_joe]

Many thanks Mr. John. So now, looking at the correction you made, in which order are the statements executed?

[jonh]

I have no idea.
I would assume SELECT > WHERE > GROUP > SUM > TRANSFORM/PIVOT


Why do you care?

[Mister_joe]

Thanks again, Mr. John.
I care because knowing what happens at each stage makes it easier to get to the desired result.
SELECT > WHERE > GROUP BY > SUM > TRANSFORM/PIVOT
The SELECT > WHERE > GROUP BY > SUM should produce a table as shown below:
18221
Now, how does the TRANSFORM/PIVOT statement breakdown the SumOfAmount for Purchase_Id 12 to arrive at the table shown below?
18222

See my confusion? I am appealing to anyone who can unravel the mystery that seems to surround the Access TRANSFORM and PIVOT statements to please, help me.

[jonh]

The SELECT part of the query only selects the ID not the SumOfAmount. That's defined by the TRANSFORM. The column headers are defined in the PIVOT.

You start with two values for ID 12 and you still have two values for ID 12 in the crosstab.

The result from the SELECT has just been TRANSFORMED. Instead of values being in rows and going down they are displayed as columns going across.

If you added a total column, (edit) or only used one column, the total for ID 12 is still 71.

[Mister_joe]

Thanks Mr. John. Please, have a look at my wild imagination below and comment. Really, I should not have to imagine it, Microsoft ought to have a well laid out procedure of how the TRANSFORM/PIVOT statement work. Is there no such thing?
18223

[jonh]

Your TRANSFORM table is missing the Purchase_Id field.

What happens next - Transpose the data, including the ID, to the new layout and group / sum. ? I don't work for Microsoft and don't know the inner workings.

But I don't need to know the inner workings to know what a crosstab query does.

You know that Access has a query designer built in right?

If you select Crosstab mode it adds a crosstab specific parameter to the grid.

[Mister_joe]

Thanks for the recommendations. I appreciate your assistance.