field positions in crosstab query

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Please explain the position and purpose of the 3 typical field positions in a
crosstab query
 
Denise said:
Please explain the position and purpose of the 3 typical field positions in a
crosstab query
Click to expand...

Meaning no offense, but....
your question sounds like it comes
straight from a class workbook.....

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 EmployeeID cnt: Count(*) TotalOrders: Count(*)
Table: Orders Orders
Total: Group By Group By Expression Expression
Crosstab: Row Heading Column Heading Value Row Heading

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(*)-------------]
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

Again meaning no offense, good luck with your assignment.

gary
 
Back
Top