Re: HELP!! Jet Database Engine Error on Crosstab Query

From: Gary Walter (garylwpleasenospam_at_wamego.net)
Date: 05/11/04 

Date: Tue, 11 May 2004 09:04:17 -0500

Hi Henry,

Did you try

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

Actually...I'm surprised that the original
subquery worked out the correlation.

But then, I could be mistaken.

Good luck,

Gary Walter

"Henry" <anonymous@discussions.microsoft.com> wrote in message
news:761CB194-E9B6-4B52-89D2-4E17158F1187@microsoft.com...
> OK. I've tried to simplify the query by creating two tables. I named them Table1
and Table2. The data elements in each are:
> Table1:
> Table1Key
> Table1Data
> Table1RowTitle
>
> Table2:
> Table2Key
> Table1FK
> Table2Date
> Table2ColumnTitle
>
> 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.Table2ColumnTitle
> FROM Table1 INNER JOIN Table2 ON Table1.Table1Key = Table2.Table1FK
> 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 CountOfTable1Data
> SELECT Query1.Table1RowTitle, Count(Query1.Table1Data) AS [Total Of Table1Data]
> FROM Query1
> GROUP BY Query1.Table1RowTitle
> 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.
>
> This problem is identical to the one described in my first post - I've just
simplified the number of tables and field names.
>
> Any clues as to why the crosstab query can't evaluate the subquery? Any
workarounds?
>
> Thanks for your help.

Relevant Pages