In sample NorthWind mdb, a typical
crosstab query might be:
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(*) EmployeeID
cnt: Count(*)
Table: Orders Orders
Total: Group By Expression Group By
Expression
Crosstab: Row Heading Row Heading Column Heading 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
