crosstab query doesn't recognize expression in source query

[Daniel]

Hi,

I've written a simple crosstab query based upon a select query which
has several left joins and subqueries in the WHERE clause. The select
query runs fine, but when I try to run the crosstab query it says that
"The Microsoft Jet database engine does not recognize 'tRs.ID' as a
valid field name or expression. tRs.ID is one of the fields that I
perform a LEFT JOIN on (not a parameter).

When I create a table from the select query, the crosstab query works
fine.

The crosstab query is:
TRANSFORM first(qR.revFlag)
SELECT qR.releaseID
FROM qtest2 AS qR INNER JOIN tblSchematicFieldsList AS SF ON
qR.schematicFieldsListID = SF.ID
GROUP BY qR.releaseID
PIVOT SF.fieldName & "_revFlag";

qtest2 is (with the apparently offensive tRs.ID in the FROM clause
rather than the SELECT clause because I can remove it from the SELECT
clause and still generate the error):
SELECT IIf(tRR.revOrder>8,IIf(tRR.revOrder=tRMax.revOrder,2,1),0) AS
revFlag, tEV.electricalID, tEV.schematicFieldsListID, tEV.fieldValue,
tRs.ID AS releaseID
FROM ((tblReleases AS tRs LEFT JOIN tblEquipMCCVals AS tEV ON
tRs.referenceID = tEV.electricalID)
LEFT JOIN qryReleasesToRevisions AS tRMax ON tRs.ID =
tRMax.releaseID)
LEFT JOIN qryReleasesToRevisions AS tRR ON tRs.ID = tRR.releaseID
WHERE tRR.ID=(SELECT TOP 1 t1.ID FROM qryReleasesToRevisions AS t1
WHERE t1.releaseID = tRs.ID
AND t1.revReleaseDate > tEV.modDate ORDER BY t1.revOrder ASC)
AND tRs.documentTypeID=4
AND tRMax.ID=(SELECT TOP 1 t2.ID FROM qryReleasesToRevisions AS t2
WHERE t2.releaseID = tRs.ID ORDER BY t2.revOrder ASC);

Am I making a mistake? Is this a known limitation of crosstab
queries?

Thank you,
Daniel

 

[KARL DEWEY]

This is a guess on my part but I think the problem is your use of an alias in
the subquery. Redo WHERE t2.releaseID = tRs.ID ORDER BY t2.revOrder ASC);
to read WHERE t2.releaseID = tblReleases.ID ORDER BY t2.revOrder ASC);

 

[Daniel]

Hi Karl,

Thanks for the idea, but unfortunately it doesn't help. You were
right that the problem lies in the subquer(ies) rather than the FROM
statement, because if I get rid of them it works fine. Which isn't
useful, of course. Is this a bug then? Any other thoughts?

Daniel

 

[KARL DEWEY]

Try removing all the aliases created in the main query from the subqueries.

 

[Daniel]

I've removed all aliases, and cut down the queries to:

qtest2:
SELECT tblReleases.location, tblReleases.sequenceNo
FROM tblReleases
WHERE tblReleases.documentTypeID = (SELECT TOP 1 ID FROM
qryReleasesToRevisions WHERE tblReleases.ID = releaseID ORDER BY
revOrder ASC);

crosstab query:
TRANSFORM first(qtest2.location)
SELECT qtest2.sequenceNo
FROM qtest2
GROUP BY qtest2.sequenceNo
PIVOT qtest2.location

but I get the same error message. I can recreate this problem in
Access 2000 with a blank database:
table1: [table1ID], [field1], [field2]
table2: [table2ID] (all integer/autonumber fields).

testqry: SELECT table1.[table1ID], table1.[field1], table1.[field2]
FROM table1
WHERE table1.table1ID = (SELECT TOP 1 table2.table2ID FROM table2
WHERE table2.table2ID = table1.table1ID);

crosstabqry: TRANSFORM Avg(qryTest.field2)
SELECT qryTest.table1ID
FROM qryTest
GROUP BY qryTest.table1ID
PIVOT qryTest.field1;

Any other thoughts would be appreciated.

Daniel