Crosstab Query Error (DB can't find field)

[David Benyo via AccessMonster.com]

I have the following query that gets the most recent status:

SELECT tblProcesses.ProcessID, tblProcesses.AccountNumber, tblProcesses.
Process, tblProcesses.ProviderResponsible, tblProcesses.ProcessNote,
tblProcesses.DateOfProcess
FROM tblProcesses
WHERE (((tblProcesses.DateOfProcess) = (SELECT Max(T.DateOfProcess) FROM
tblProcesses as T WHERE T.AccountNumber = tblProcesses.AccountNumber)));

I'm trying to create a simple crosstab on this query. I'm using the following
sql:

TRANSFORM Count(CurrentProcessStatus.ProcessID) AS CountOfProcessID
SELECT CurrentProcessStatus.ProviderResponsible, Count(CurrentProcessStatus.
ProcessID) AS [Total Of ProcessID]
FROM CurrentProcessStatus
GROUP BY CurrentProcessStatus.ProviderResponsible
PIVOT CurrentProcessStatus.Process;

When I run the crosstab I get the message that the db engine doesn't
recognize "tblProcesses.AccountNumber" as a valid field/expression. I'm
confused because the first query runs perfect. The db is written in A.2000.
Can anyone see a potential problem with either of these two queries?

 

[Gary Walter]

I have the following query that gets the most recent status:

SELECT tblProcesses.ProcessID, tblProcesses.AccountNumber, tblProcesses.
Process, tblProcesses.ProviderResponsible, tblProcesses.ProcessNote,
tblProcesses.DateOfProcess
FROM tblProcesses
WHERE (((tblProcesses.DateOfProcess) = (SELECT Max(T.DateOfProcess) FROM
tblProcesses as T WHERE T.AccountNumber = tblProcesses.AccountNumber)));

I'm trying to create a simple crosstab on this query. I'm using the
following
sql:

TRANSFORM Count(CurrentProcessStatus.ProcessID) AS CountOfProcessID
SELECT CurrentProcessStatus.ProviderResponsible,
Count(CurrentProcessStatus.
ProcessID) AS [Total Of ProcessID]
FROM CurrentProcessStatus
GROUP BY CurrentProcessStatus.ProviderResponsible
PIVOT CurrentProcessStatus.Process;

When I run the crosstab I get the message that the db engine doesn't
recognize "tblProcesses.AccountNumber" as a valid field/expression. I'm
confused because the first query runs perfect. The db is written in
A.2000.
Can anyone see a potential problem with either of these two queries?

--

Hi David,

Crosstabs need to be fed "simple things," i.e., it could
be the subquery is too much for it.

Try creating another query that groups by AccountNumber
and finds max date.

Join this query w/tblProcesses on AccountNumber and dates,
then try basing crosstab on that query.

If that doesn't work, then feed crosstab a temp table
that you always "prefill" from your original query above.

good luck,

gary

 

[David Benyo via AccessMonster.com]

Gary,

Thanks. This is the solution I've gone with. It's the way I orginally had it
setup, but didn't realize the crosstab would have difficulties when I
combined the queries into one. Is there any specific reason why crosstabs
don't like "complex"? Is it because of the natural complexity of crosstabs
themselves? I didn't think that one was all that complex, but if it doesn't
work, then I guess it was.

Thanks for your advice though.

I have the following query that gets the most recent status:

[quoted text clipped - 22 lines]

A.2000.
Can anyone see a potential problem with either of these two queries?

Hi David,

Crosstabs need to be fed "simple things," i.e., it could
be the subquery is too much for it.

Try creating another query that groups by AccountNumber
and finds max date.

Join this query w/tblProcesses on AccountNumber and dates,
then try basing crosstab on that query.

If that doesn't work, then feed crosstab a temp table
that you always "prefill" from your original query above.

good luck,

gary

 

[Gary Walter]

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

 

[David Benyo via AccessMonster.com]

Gary,

Thanks. You're absolutely correct. I wasn't entirely thinking about what
access has to do to create a complex crosstab. Breaking it out the way you
did sheds some light on what we take for granted. Thank God we don't have to
write crosstabs out like that all the time!

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

Thanks. This is the solution I've gone with. It's the way I orginally had
it

[quoted text clipped - 6 lines]

Thanks for your advice though.