Joe said:
Does anyone know of any in-depth tutorials for Pivot Tables and Pivot Charts -
Online and/or books?
Thanks!
Sorry, I can't suggest a book, but have you looked at the "Northwind
Traders" sample database? The [Quarterly Orders by Product] Query is a
Crosstab Query that you can use to display a Pivot Table. Actually, I
used a variation which reduced the number of records (I omitted the
customer names).
So, if you add the following two Queries to your copy of "Northwind
Traders", you should be able to see (pretty much) the same information
both in Crosstab Query format and as a Pivot Table.
[Q_VJ Quarterly Orders By Product] SQL:
TRANSFORM Sum(CCur([Order Details].[UnitPrice]*[Quantity]
*(1-[Discount])/100)*100) AS ProductAmount
SELECT [Products].[ProductName], Year([OrderDate]) AS OrderYear
FROM Products INNER JOIN (Orders INNER JOIN [Order Details]
ON [Orders].[OrderID]=[Order Details].[OrderID])
ON [Products].[ProductID]=[Order Details].[ProductID]
WHERE ((([Orders].[OrderDate])
Between #1/1/1997# And #12/31/1997#))
GROUP BY [Products].[ProductName], Year([OrderDate])
PIVOT "Qtr " & DatePart("q",[OrderDate],1,0)
In ("Qtr 1","Qtr 2","Qtr 3","Qtr 4");
The Pivot Table will contain these same data, in a slightly different
format:
[Q_VJ Quarterly Orders By Product] Query Datasheet View:
Product Name Order Qtr 1 Qtr 2 ...
Year
------------- ----- --------- ---------
Alice Mutton 1997 $2,667.60 $4,013.10 ...
Aniseed Syrup 1997 $544.00 $600.00 ...
Boston Crab M 1997 $1,768.41 $1,978.00 ...
Camembert Pie 1997 $3,182.40 $4,683.50 ...
Carnarvon Tig 1997 $1,500.00 $2,362.50 ...
...
(remainder of 77 records is omitted)
The next Query provides data for the Pivot Table, and is used in the
Pivot Table Wizard:
[Q_VJ Orders By Product] SQL:
SELECT Products.ProductName, Year([OrderDate]) AS OrderYear,
"Qtr " & DatePart("q",[OrderDate],1,0) AS Quarter,
Sum(CCur([Order Details].[UnitPrice]*[Quantity]
*(1-[Discount])/100)*100) AS ProductAmount
FROM Products INNER JOIN (Orders INNER JOIN [Order Details]
ON Orders.OrderID = [Order Details].OrderID)
ON Products.ProductID = [Order Details].ProductID
WHERE (((Orders.OrderDate)
Between #1/1/1997# And #12/31/1997#))
GROUP BY Products.ProductName,
Year([OrderDate]),
"Qtr " & DatePart("q",[OrderDate],1,0);
The beginning of this dataset looks like this:
[Q_VJ Orders By Product] Query Datasheet View:
Product Name OrderYear Quarter ProductAmount
-------------- --------- ------- -------------
Alice Mutton 1997 Qtr 1 $2,667.60
Alice Mutton 1997 Qtr 2 $4,013.10
Alice Mutton 1997 Qtr 3 $4,836.00
Alice Mutton 1997 Qtr 4 $6,087.90
Aniseed Syrup 1997 Qtr 1 $544.00
...
(remainder of 286 records is omitted)
Now you can open the Database Window, go to the Forms tab, click on New,
and open the PivotTable Wizard. Select [Q_VJ Orders By Product] (or
whatever you called this Query). In the PivotTable Wizard, select all 4
fields of the Query, click Layout, and drag...
[OrderYear] to PAGE
[Quarter] to COLUMN
[Product Name] to ROW
[ProductAmount] to DATA (but it will change to [Sum of ProductAmount])
Click on Finish, then in the new Form, click on the "Edit PivotTable
Object" button at the bottom. (The Form itself isn't very useful, at
least not in this case.)
The result (displayed by Excel) looks something like this (but I
shortened or omitted some of the data):
OrderYear (All)
Sum of ProductAmount Quarter
ProductName Qtr 1 Qtr 2 ... Grand Total
------------ ----------- ----------- -----------
Alice Mutton $2,667.60 $4,013.10 ... $17,604.60
Aniseed Syru $544.00 $600.00 ... $1,724.00
Boston Crab $1,768.41 $1,978.00 ... $9,814.73
Camembert Pi $3,182.40 $4,683.50 ... $20,505.40
Carnarvon Ti $1,500.00 $2,362.50 ... $15,950.00
...
Wimmers gute $2,912.70 $1,735.65 ... $8,056.47
Zaanse koeken $817.00 $285.95 ... $2,930.75
Grand Total $138,288.90 $143,177.02 ... $617,085.08
MS Access Help provides a lot of information on using PivotTables.
Probably Excel Help does, too. I created a PivotChart on these data in
just a minute or so, though it was a bit hard to read with all those
products listed.
-- Vincent Johns <
[email protected]>
Please feel free to quote anything I say here.
