crosstab query fails when based on query with subquery?

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
 
G

Gary Walter

Hi Daniel,

Well...you are filtering on the inner tables
of an outer join...

Best suggestion:

feed (append) your query to a temp table,
then run xtab on the temp table.

good luck,

gary
 
G

Gary Walter

I'm sorry I did not address your recreation
but it just does not make sense (to me).

the where clause almost seems "circular"

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

is this not the same?

SELECT table1.table1ID, table1.f1, table1.f2
FROM table1
WHERE
table1.table1ID
In (SELECT t.table2ID FROM table2 As t)

the above does not error out in crosstab.

neither does this one:

SELECT table1.table1ID, table1.f1, table1.f2
FROM
table1
INNER JOIN
table2
Hi Daniel,

Well...you are filtering on the inner tables
of an outer join...

Best suggestion:

feed (append) your query to a temp table,
then run xtab on the temp table.

good luck,

gary

Daniel said:
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
 
D

Daniel

Hi Gary,

Thanks for the tip with the temporary tables - it's probably what I'll
have to do. The recreation query is useless except to demonstrate the
problem, and it could be replaced with other things, but I can't see
how to get rid of the subqueries in my actual program (I showed the
query in the OP).

Thanks,
Daniel

I'm sorry I did not address your recreation
but it just does not make sense (to me).

the where clause almost seems "circular"

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

is this not the same?

SELECT table1.table1ID, table1.f1, table1.f2
FROM table1
WHERE
table1.table1ID
In (SELECT t.table2ID FROM table2 As t)

the above does not error out in crosstab.

neither does this one:

SELECT table1.table1ID, table1.f1, table1.f2
FROM
table1
INNER JOIN
table2
Hi Daniel,
Well...you are filtering on the inner tables
of an outer join...
Best suggestion:
feed (append) your query to a temp table,
then run xtab on the temp table.
good luck,

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
 
G

Gary Walter

Hi Daniel,

I don't know if I'll be able to help, but
what is SQL for qryReleasesToRevisions?

It may be that you will have to bite the bullet
and show table structure, data examples,
and what you expect from each query
based on example data.

Sorry,

gary

Daniel said:
Thanks for the tip with the temporary tables - it's probably what I'll
have to do. The recreation query is useless except to demonstrate the
problem, and it could be replaced with other things, but I can't see
how to get rid of the subqueries in my actual program (I showed the
query in the OP).

Thanks,
Daniel

I'm sorry I did not address your recreation
but it just does not make sense (to me).

the where clause almost seems "circular"

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

is this not the same?

SELECT table1.table1ID, table1.f1, table1.f2
FROM table1
WHERE
table1.table1ID
In (SELECT t.table2ID FROM table2 As t)

the above does not error out in crosstab.

neither does this one:

SELECT table1.table1ID, table1.f1, table1.f2
FROM
table1
INNER JOIN
table2
Hi Daniel,
Well...you are filtering on the inner tables
of an outer join...
Best suggestion:
feed (append) your query to a temp table,
then run xtab on the temp table.
good luck,

"Daniel" wrote:
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
 
D

Daniel

Thanks for your time Gary.

Here goes ...

I need to issue documents ("releases"), each of which has list of
revisions (with dates). There are tags, which are date-stamped, which
appear on documents. Tags can go on multiple documents, so the
revision needs to be calculated per tag per document.

e.g.
160047.dgn has Rev. PA (June 2006), Rev. 00 (August 2006), Rev. 01
(October 2006), Rev. 02 (December 2006)

Multiple tags appear on 160047.dgn, and the date when each was edited
can be used to determine under which revision the tag was last
edited. So the tag "equipment number", edited July 2006, is Rev. 00
while "cable 1", edited November 2006, is Rev. 02.

This is for a CADD automation tool. I then generate a "revFlag" for
each tag:
0 => don't display revision information, if (revOrder <= Rev. 00's
order)
1 => display some revision information, if (revision > Rev. 00) AND
(revision <> not the latest revision)
2 => display & update revision information, if (revision = latest
revision)

For the automation, I need a wide table like this, but I store it in a
normalized table.
(wide table, from two separate crosstab queries):
release "equip_no" "equip_noRevFlag" "cable_1"
"cable_1RevFlag" ... (all tags)
160047.dgn 12-EQ-01 0 123 2

(tblEquipMCCVals, the normalized source table):
[equipmentID], [tagListID], [value] , [modDate]
6294 , 1 , 123 , 17/5/2007
6290 , 2 , "12-EQ-01", 17/5/2007

tagListID gives the tag name ("equip_no"), [value] is e.g. "12-EQ-01"
or 123, while each [equipmentID] is related to one or more 'releases'.

qryRevFlag is meant to give the revision flags for each tag & release,
and I build the crosstab query off of it:
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);


tblReleasesToRevisions lists all revisions:
[releaseID], [revisionID], [revReleaseDate], etc...
160047.dgn , 1 , June 2006

qryReleasesToRevisions looks up additional information such as
revision ordering, descriptions, etc... for each [releaseID] from
related tables.
[releaseID], [revReleaseDate], [revOrder], [revName], ...
160047.dgn , June 2006 , 0 , PA

The problem is that the crosstab query, generated from qryRevFlag,
fails to run with "The Microsoft Jet database engine does not
recognize 'tRs.ID' as a valid field name or expression."

Thank you for any input,
Daniel

Hi Daniel,

I don't know if I'll be able to help, but
what is SQL for qryReleasesToRevisions?

It may be that you will have to bite the bullet
and show table structure, data examples,
and what you expect from each query
based on example data.

Sorry,

gary

Daniel said:
Thanks for the tip with the temporary tables - it's probably what I'll
have to do. The recreation query is useless except to demonstrate the
problem, and it could be replaced with other things, but I can't see
how to get rid of the subqueries in my actual program (I showed the
query in the OP).
Thanks,
Daniel

I'm sorry I did not address your recreation
but it just does not make sense (to me).
the where clause almost seems "circular"
SELECT table1.[table1ID], table1.[field1], table1.[field2]
FROM table1
WHERE table1.table1ID =
(SELECT TOP 1 table2.table2ID
FROM table2
WHERE table2.table2ID = table1.table1ID);
is this not the same?
SELECT table1.table1ID, table1.f1, table1.f2
FROM table1
WHERE
table1.table1ID
In (SELECT t.table2ID FROM table2 As t)
the above does not error out in crosstab.
neither does this one:
SELECT table1.table1ID, table1.f1, table1.f2
FROM
table1
INNER JOIN
table2
ON table1.table1ID = table2.table2ID;
:
Hi Daniel,
Well...you are filtering on the inner tables
of an outer join...
Best suggestion:
feed (append) your query to a temp table,
then run xtab on the temp table.
good luck,
gary
:
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
 
G

Guest

Crosstabs don't handle subqueries very well. You can either generate
temporary tables or replace your subqueries with very slow domain aggregate
functions such as DLookup() or DMax() etc.

--
Duane Hookom
Microsoft Access MVP


Daniel said:
Thanks for your time Gary.

Here goes ...

I need to issue documents ("releases"), each of which has list of
revisions (with dates). There are tags, which are date-stamped, which
appear on documents. Tags can go on multiple documents, so the
revision needs to be calculated per tag per document.

e.g.
160047.dgn has Rev. PA (June 2006), Rev. 00 (August 2006), Rev. 01
(October 2006), Rev. 02 (December 2006)

Multiple tags appear on 160047.dgn, and the date when each was edited
can be used to determine under which revision the tag was last
edited. So the tag "equipment number", edited July 2006, is Rev. 00
while "cable 1", edited November 2006, is Rev. 02.

This is for a CADD automation tool. I then generate a "revFlag" for
each tag:
0 => don't display revision information, if (revOrder <= Rev. 00's
order)
1 => display some revision information, if (revision > Rev. 00) AND
(revision <> not the latest revision)
2 => display & update revision information, if (revision = latest
revision)

For the automation, I need a wide table like this, but I store it in a
normalized table.
(wide table, from two separate crosstab queries):
release "equip_no" "equip_noRevFlag" "cable_1"
"cable_1RevFlag" ... (all tags)
160047.dgn 12-EQ-01 0 123 2

(tblEquipMCCVals, the normalized source table):
[equipmentID], [tagListID], [value] , [modDate]
6294 , 1 , 123 , 17/5/2007
6290 , 2 , "12-EQ-01", 17/5/2007

tagListID gives the tag name ("equip_no"), [value] is e.g. "12-EQ-01"
or 123, while each [equipmentID] is related to one or more 'releases'.

qryRevFlag is meant to give the revision flags for each tag & release,
and I build the crosstab query off of it:
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);


tblReleasesToRevisions lists all revisions:
[releaseID], [revisionID], [revReleaseDate], etc...
160047.dgn , 1 , June 2006

qryReleasesToRevisions looks up additional information such as
revision ordering, descriptions, etc... for each [releaseID] from
related tables.
[releaseID], [revReleaseDate], [revOrder], [revName], ...
160047.dgn , June 2006 , 0 , PA

The problem is that the crosstab query, generated from qryRevFlag,
fails to run with "The Microsoft Jet database engine does not
recognize 'tRs.ID' as a valid field name or expression."

Thank you for any input,
Daniel

Hi Daniel,

I don't know if I'll be able to help, but
what is SQL for qryReleasesToRevisions?

It may be that you will have to bite the bullet
and show table structure, data examples,
and what you expect from each query
based on example data.

Sorry,

gary

Daniel said:
Thanks for the tip with the temporary tables - it's probably what I'll
have to do. The recreation query is useless except to demonstrate the
problem, and it could be replaced with other things, but I can't see
how to get rid of the subqueries in my actual program (I showed the
query in the OP).

I'm sorry I did not address your recreation
but it just does not make sense (to me).
the where clause almost seems "circular"
SELECT table1.[table1ID], table1.[field1], table1.[field2]
FROM table1
WHERE table1.table1ID =
(SELECT TOP 1 table2.table2ID
FROM table2
WHERE table2.table2ID = table1.table1ID);
is this not the same?
SELECT table1.table1ID, table1.f1, table1.f2
FROM table1
WHERE
table1.table1ID
In (SELECT t.table2ID FROM table2 As t)
the above does not error out in crosstab.
neither does this one:
SELECT table1.table1ID, table1.f1, table1.f2
FROM
table1
INNER JOIN
table2
ON table1.table1ID = table2.table2ID;
:
Hi Daniel,
Well...you are filtering on the inner tables
of an outer join...
Best suggestion:
feed (append) your query to a temp table,
then run xtab on the temp table.
good luck,

"Daniel" wrote:
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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top