T Miller said:
How easy is it to explain how to set one up and use it? I mean I know
where
it is, but not sure what table should be used to get a particular result
and
how it actually works?
It might help you to start with a typical crosstab
in NorthWind db, then dissect it piece by piece...
(I think the "breakdown" later on can help alot)
TRANSFORM Count(*) AS cnt
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders
FROM Orders
GROUP BY orders.ShipCountry
PIVOT orders.EmployeeID;
In Design View, its grid would look like:
Field: ShipCountry TotalOrders: Count(*)
Table: Orders
Total: Group By Expression
Crosstab: Row Heading Row Heading
Field: EmployeeID
Table: Orders
Total: Group By
Crosstab: Column Heading
Field: cnt: Count(*)
Table:
Total: Expression
Crosstab: Value
The results might be broken down into "3 field positions":
1) Row Heading
2) Column Heading
3) Value
1) The Row Heading(s) come from
any fields in the Group By clause
of your SELECT stmt
(plus any other aggregates in the
SELECT stmt):
"SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders
FROM Orders
GROUP BY orders.ShipCountry"
2) The Column Headings come from
the PIVOT clause at the end of the
query:
"PIVOT orders.EmployeeID;"
3)The Value comes from beginning
TRANSFORM clause:
"TRANSFORM Count(*) AS cnt"
The following probably won't come out
legibly in newsgroup, but here be an
attempt to "break down" results from the
above query:
[---row heading---------------][-col heading=employee id--]
[-GroupBy---][-Expression--][-value=count(*)for each id-]
Ship Country TotalOrders 1 2 3 4 5 6 7 8 9
Argentina 16 1 1 1 4 1 3 3 2
Austria 40 5 6 5 6 4 6 5 3
Belgium 19 1 2 1 6 4 1 2 2
Brazil 83 11 9 10 20 5 8 8 9 3
Canada 30 5 5 9 3 3 2 2 1
Denmark 18 4 3 1 3 1 4 2
Finland 22 2 6 2 3 2 1 1 4 1
France 77 9 11 13 14 5 9 5 8 3
Germany 122 19 14 19 25 4 9 6 17 9
Ireland 19 1 3 5 1 3 2 1 3
Italy 28 5 7 1 6 1 1 2 3 2
Mexico 28 6 4 6 4 1 5 2
Norway 6 2 1 1 2
Poland 7 2 2 1 1 1
Portugal 13 2 2 3 2 1 2 1
Spain 23 3 2 3 7 2 3 2 1
Sweden 37 5 4 8 3 3 2 2 8 2
Switzerland 18 2 3 4 1 2 3 1 2
UK 56 9 5 8 12 2 5 5 6 4
USA 122 21 9 21 22 6 14 7 19 3
Venezuela 46 8 4 8 8 3 2 3 9 1
If we knew for sure before hand all the EmployeeID's,
we could create a "totals" query that mimics
the crosstab above using subqueries.
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=1) As 1,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=2) As 2,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=3) As 3,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=4) As 4,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=5) As 5,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=6) As 6,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=7) As 7,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=8) As 8,
(SELECT Count(*) FROM Orders As O
WHERE
O.ShipCountry=Orders.ShipCountry
AND
O.EmployeeID=9) As 9,
FROM Orders
GROUP BY orders.ShipCountry;
You might think of the crosstab query
as just a shorthand for creating all those
subqueries
1) What aggregate should we use for
each subquery?
Put it in a TRANFORM clause
TRANSFORM Count(*)
2) What fields do we want to match
between main query and subquery?
GROUP BY fields of SELECT clause
3) Within each group, what field (or expression)
will determine a distinct aggregate value?
Put it in the PIVOT clause
PIVOT orders.EmployeeID
=========================
So, you want to design a crosstab w/o
using the wizard.....
the first step might be to just to start
with an appropriate group-by query
SELECT
Orders.ShipCountry,
Count(*) AS TotalOrders
FROM Orders
GROUP BY
Orders.ShipCountry;
Field: ShipCountry TotalOrders: Count(*)
Table: Orders
Total: Group By Expression
Pretty straight-forward query that gives every distinct
ShipCountry in a *row*, and counts the number of orders
for each country.
But, you'd also like to count the number of orders
for each EmployeeID within each country group...
you know, tack on some additional *columns* for all
the distinct EmployeeID's and count their orders
within a country row group.
So, changing to crosstab, you get one more line in grid.
Field: ShipCountry TotalOrders: Count(*)
Table: Orders
Total: Group By Expression
Crosstab: Row Heading Row Heading
Just like in the original group by query, you want
every distinct country to be in a row (Row Heading)
You then doubleclick on EmployeeID to add it to the grid
and you set its "Crosstab:" to "Column Heading" to add
the additional distinct *columns.*
Field: EmployeeID
Table: Orders
Total: Group By
Crosstab: Column Heading
All that's left is one more entry in the grid
that will count the orders for each of those
additional columns within a country group
-- the Value...
Field: cnt: Count(*)
Table:
Total: Expression
Crosstab: Value
You don't have to give this value field
an alias ... it is just a *value* and does not
appear as a *heading* in your query results,
but later on you will learn that you can actually
use this value alias in a row heading to further
dissect your data (search Google Groups for
Steve Dassin crosstabs).
For example, to "count the distinct counts"
of these additional column values...
Field: DistinctCnt: Count(cnt)
Table:
Total: Expression
Crosstab: Row Heading
i.e., for Argentina, DistinctCnt = 4
Argentina 16 *4* 1 1 1 4 1 3 3 2
there were 1's, a 2, 3's, and a 4 --> 4 distinct values
"Count" is just one of the many aggregates that
could work with your value alias...
Avg(cnt)
Max(cnt)
Min(cnt)
etc...
I hope that helps....
