Hi David,

I am not privy to the actual process
Access implements "behind the scenes" to
execute a crosstab, but have seen them
choke on correlated subqueries before this.

One clue is when one tries to make the
equivalent (say for SQL Server, for example)
by listing out all the subqueries that a
PIVOT clause automatically creates
behind the scenes.

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;

[---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.

The Transform/Pivot add extra columns
to an aggragate query. This aggregate
query creates groups, the pivot determines
how to further breakdown those groups,
and the transform determines the aggregation
on those extra columns.

Imagine the hoops that would have to be jumped
through when you introduce a correlated subquery
into that process.

hope that helps,

gary