D
Daniel
Hi,
I posted before on this, but I didn't get any solution.
I base a crosstab query on a query which includes 2 subqueries. The
query runs fine, but the crosstab query fails with "The Microsoft jet
database engine does not recognize 'table1.table1ID' as a valid field
name or expression."
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;
and it fails on table1.table1ID in the subquery. The crosstab query
runs when I change table1.table1ID to '2':
SELECT table1.[table1ID], table1.[field1], table1.[field2]
FROM table1
WHERE table1.table1ID = (SELECT TOP 1 table2.table2ID FROM table2
WHERE table2.table2ID = 2);
Is this a known limitation? Am I doing something wrong? The actual
query I'm generating the crosstab query off of follows, with the
failure on tRs.ID:
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 tRs.documentTypeID = 4
AND 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 tRMax.ID =
(SELECT TOP 1 t2.ID
FROM qryReleasesToRevisions AS t2
WHERE t2.releaseID = tRs.ID
ORDER BY t2.revOrder ASC);
Thank you,
Daniel
I posted before on this, but I didn't get any solution.
I base a crosstab query on a query which includes 2 subqueries. The
query runs fine, but the crosstab query fails with "The Microsoft jet
database engine does not recognize 'table1.table1ID' as a valid field
name or expression."
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;
and it fails on table1.table1ID in the subquery. The crosstab query
runs when I change table1.table1ID to '2':
SELECT table1.[table1ID], table1.[field1], table1.[field2]
FROM table1
WHERE table1.table1ID = (SELECT TOP 1 table2.table2ID FROM table2
WHERE table2.table2ID = 2);
Is this a known limitation? Am I doing something wrong? The actual
query I'm generating the crosstab query off of follows, with the
failure on tRs.ID:
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 tRs.documentTypeID = 4
AND 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 tRMax.ID =
(SELECT TOP 1 t2.ID
FROM qryReleasesToRevisions AS t2
WHERE t2.releaseID = tRs.ID
ORDER BY t2.revOrder ASC);
Thank you,
Daniel