Jet Database Engine Error on Crosstab Query

[Guest]

I can't get a crosstab query to work when there is a subquery in the underlying query. For the purpose of illustrating the problem, I have simplified the query by creating two tables. I named them Table1 and Table2. The data elements in each are

Table1
Table1Ke
Table1Dat
Table1RowTitl

Table2
Table2Ke
Table1F
Table2Dat
Table2ColumnTitl

There is a one-to-many relationship between the two tables. What I want to do is select only one record from Table2 for each record in Table1. The record selected will be the one with the most recent Table2Date. I built a query to do this

SELECT Table2.Table2Date, Table1.Table1Data, Table1.Table1Data, Table1.Table1RowTitle, Table2.Table2ColumnTitl
FROM Table1 INNER JOIN Table2 ON Table1.Table1Key = Table2.Table1F
WHERE (((Table2.Table2Date)=(Select Max ([Table2Date]) From Table2 Where Table2.Table1FK = Table1Key)))

This query works fine. Next, I want to build a CrossTab query to count the occurances of Table1Data using Table1RowTitle as the rows and Table2ColumnTitle as the columns. The crosstab query I built looks like

TRANSFORM Count(Query1.Table1Data) AS CountOfTable1Dat
SELECT Query1.Table1RowTitle, Count(Query1.Table1Data) AS [Total Of Table1Data
FROM Query
GROUP BY Query1.Table1RowTitl
PIVOT Query1.Table2ColumnTitle

When I execute this query, I get the error message

The Microsoft Jet database engine does not recognize "Table1Key" as a valid field name or expression

It appears that the Jet database engine can't deal with the double where condition

Any clues as to why the crosstab query can't evaluate the subquery? Any workarounds

Thanks
Henry

 

[Duane Hookom]

It's just something that Crosstab queries will not support. I have gotten
around this by substituting a domain aggregate function for the subquery
WHERE Table2.Table2Date=DMax("[Table2Date]", "Table2", "Table1FK = " &
Table1Key);
I think this is the correct syntax and assumes the field is numeric.

--
Duane Hookom
MS Access MVP

I tried both suggestions. Both resulted in the same error message as

before except now the invalid field is "Table1.Table1Key" rather than
"Table1Key". The crosstab query seems to choke on whatever is to the right
of the equal sign in that sub query.