Trying to understand crosstab queries

[IanONet]

I have avoided Crosstab queries for too long. Now is the time for me to
"get it".

So I attempted to do the query from the Northwind example in the help.
Here is the data from the Select query:
Last Name Category Sum of Subtotal
Buchanan Beverages $13,517.50
Buchanan Condiments $2,802.45
Buchanan Confections $5,489.05
Buchanan Dairy Products $23,850.40
Buchanan Grains/Cereals $4,233.25
Buchanan Meat/Poultry $11,869.40
Buchanan Produce $7,605.10
Buchanan Seafood $6,200.60
Callahan Beverages $18,640.80
Callahan Condiments $15,447.95
Callahan Confections $22,639.50

and the SQL for that query:
SELECT Employees.LastName, Products.CategoryID AS [Category Name],
Sum(([Order Details].[UnitPrice]*[Quantity])) AS [Sum of Subtotal]
FROM Products INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
GROUP BY Employees.LastName, Products.CategoryID
ORDER BY Employees.LastName, Products.CategoryID;

Now the task is to rearrange the data so I get the Categories on the
top row and the last names in the first column. The dollar amount
should fill the field.

I know I am confused by what it wants for row headers and column
headers.
Here is my attempt in SQL:
TRANSFORM Sum(qselGOsInTo.[Sum of Subtotal]) AS [SumOfSum of Subtotal]
SELECT qselGOsInTo.LastName, qselGOsInTo.[Category Name]
FROM qselGOsInTo
GROUP BY qselGOsInTo.LastName, qselGOsInTo.[Category Name]
PIVOT qselGOsInTo.[Category Name];

I'd appreciate it if someone could fix that query AND explain just how
I am supposed to think about the data in this transformation process.

Just what goes into the TRANSFORM clause and why?
Just what goes into the PIVOT clause and why?
I suspect that I have the group by correct ;-)

Thanks in advance,
IanO

 

[Guest]

I'm a relative newcomer to crosstabs as well. My first attempts were
repeatedly of the crash and burn variety, but eventually they did work. I've
always used the wizard, and never written the SQL from scratch. But here are
my suggestions.

That Northwind database is chock full of examples, but its sheer bulk make
simple examples hard to come by. Create a simpler table, with fewer choices.
How about a Budget table, with only date, description, and amount fields
(forget about primary keys for now). Use the crosstab query wizard, and take
it slow. First use date for Row Headings, and description for Column
Headings. Do what you will with the amounts. Create another with
description for Row, and date for Column. Look at the difference, and, what
seems important to you, examine the underlying SQL. If you can wrap your
head around a simple example, more complicated crosstabs become easier.

I have avoided Crosstab queries for too long. Now is the time for me to
"get it".

So I attempted to do the query from the Northwind example in the help.
Here is the data from the Select query:
Last Name Category Sum of Subtotal
Buchanan Beverages $13,517.50
Buchanan Condiments $2,802.45
Buchanan Confections $5,489.05
Buchanan Dairy Products $23,850.40
Buchanan Grains/Cereals $4,233.25
Buchanan Meat/Poultry $11,869.40
Buchanan Produce $7,605.10
Buchanan Seafood $6,200.60
Callahan Beverages $18,640.80
Callahan Condiments $15,447.95
Callahan Confections $22,639.50

and the SQL for that query:
SELECT Employees.LastName, Products.CategoryID AS [Category Name],
Sum(([Order Details].[UnitPrice]*[Quantity])) AS [Sum of Subtotal]
FROM Products INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
GROUP BY Employees.LastName, Products.CategoryID
ORDER BY Employees.LastName, Products.CategoryID;

Now the task is to rearrange the data so I get the Categories on the
top row and the last names in the first column. The dollar amount
should fill the field.

I know I am confused by what it wants for row headers and column
headers.
Here is my attempt in SQL:
TRANSFORM Sum(qselGOsInTo.[Sum of Subtotal]) AS [SumOfSum of Subtotal]
SELECT qselGOsInTo.LastName, qselGOsInTo.[Category Name]
FROM qselGOsInTo
GROUP BY qselGOsInTo.LastName, qselGOsInTo.[Category Name]
PIVOT qselGOsInTo.[Category Name];

I'd appreciate it if someone could fix that query AND explain just how
I am supposed to think about the data in this transformation process.

Just what goes into the TRANSFORM clause and why?
Just what goes into the PIVOT clause and why?
I suspect that I have the group by correct ;-)

Thanks in advance,
IanO

 

[IanONet]

Thanks for your reply. Since you seem to have a better grasp of
crosstab than I, may I ask that you try to accomplish the query that I
wrote in the original message. You may use the wizard of course. Just
when it works, post the SQL here so we all can read it.

The example I posted was from the Help on crosstab. The desired result
is supposed
to look like:
LastName Beverages Condiments Confections Dairy Products
Buchanan with their totals for the categories
Callahan here in the field of Sum(...)

Other than the UnitPrice*Quantity, this seems to be the simplest
example.

 

[Guest]

Starting with the query you originally posted, named "OrigQuery" for this
example, and using the crosstab query wizard, the SQL is as follows:

TRANSFORM Sum(OrigQuery.[Sum of Subtotal]) AS [SumOfSum of Subtotal]
SELECT OrigQuery.LastName, Sum(OrigQuery.[Sum of Subtotal]) AS [Total Of Sum
of Subtotal]
FROM OrigQuery
GROUP BY OrigQuery.LastName
PIVOT OrigQuery.[Category Name];

This will list names as row headings on the left, and categories (as ID#'s)
along the top as column headings.

However, if your original query uses the CategoryName field from the
Categories Table instead of CategoryID from the Products table, the Category
words are spelled out as column headings in the crosstab. The SQL for the
crosstab remains the same.

 

[IanONet]

Thanks so much AccessTaxman.
First of all, the error in the OrigQuery I feel was caused by the
subdatasheet.
When I opened the Products Table, Category is displayed not Category
ID.
I did this SELECT Categories.* FROM Categories; to see where the
CategoryName really existed.

So I changed the OrigQuery to
SELECT Employees.LastName, Categories.CategoryName, Sum(([Order
Details].[UnitPrice]*[Quantity])) AS [Sum of Subtotal]
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID =
[Order Details].ProductID
GROUP BY Employees.LastName, Categories.CategoryName
ORDER BY Employees.LastName, Categories.CategoryName;

which is a five table join!

Then I reran your query but needed to change [Category Name] to
CategoryName.
(I'm doing this so future readers can get a working copy)
Now that I have a working query, I am still looking for the concept of
which field(s) need to be included in the TRANSFORM clause, Which on
the SELECT and Which on the PIVOT clause. Pehaps someone could share
how they think about this.

The suspicion that I have now, a glimmer of undestanding, is that by
including [CategoryName] in the PIVOT clause, it rotated the single
column of CategoryName into multiple columns going accross. How does
that correspond to RowHeading or Column Heading?

 

[Guest]

Hopefully I'm not beating a dead horse, but what you really seem to be after
is one single query or SQL statement that extracts a breakdown by last name
(row headings) of total orders and totals by category (column headings) -
based solely on the data in the tables. If so, I've pieced this little ditty
together:

TRANSFORM Sum([Order Details]![UnitPrice]*[Order Details]![Quantity]) AS
[The Value]
SELECT Employees.LastName, Sum([Order Details]![UnitPrice]*[Order
Details]![Quantity]) AS Total
FROM (Categories INNER JOIN Products ON Categories.CategoryID =
Products.CategoryID) INNER JOIN ((Employees INNER JOIN Orders ON
Employees.EmployeeID = Orders.EmployeeID) INNER JOIN [Order Details] ON
Orders.OrderID = [Order Details].OrderID) ON Products.ProductID = [Order
Details].ProductID
GROUP BY Employees.LastName
PIVOT Categories.CategoryName;

Enjoy!!!!