HELP!! Jet Database Engine Error on Crosstab Query

[Henry]

HELP!

I am trying to execute a crosstab query and receive the following error message

The Microsoft Jet database engine does not recognize "[Connection Inventory].[Connection Inventory ID]" as a valid field name or expression

The crosstab query is based on another query that works fine by itself. This query has criteria to limit the records retrieved from a table (one to many relationship) to only the most current record. If I delete this sub query, the crosstab query works fine (except that it retrieves too many records). The sub query that causes the crosstab query to fail is

(([Project Phase History].[Date])=(Select Max ( [Date]) From [Project Phase History] Where [Project Phase History].[Connection Inventory ID] = [Connection Inventory].[Connection Inventory ID]))

The complete query is

SELECT [Project Phase].[Project Phase Grouping], [Project Phase History].Date, [Connection Inventory].[Connection Inventory ID], [Application Master].[Application Master], [Application Master].[Deactivated Application Indicator
FROM [Project Phase] INNER JOIN ((([Application Master] INNER JOIN Application ON [Application Master].[Application Master ID] = Application.[Application Master ID]) INNER JOIN [Connection Inventory] ON Application.[Application ID] = [Connection Inventory].[Application ID]) INNER JOIN [Project Phase History] ON [Connection Inventory].[Connection Inventory ID] = [Project Phase History].[Connection Inventory ID]) ON [Project Phase].[Project Phase ID] = [Project Phase History].[Project Phase ID
WHERE ((([Project Phase].[Project Phase Grouping])=2 Or ([Project Phase].[Project Phase Grouping])=3) AND (([Project Phase History].[Date])=(Select Max ( [Date]) From [Project Phase History] Where [Project Phase History].[Connection Inventory ID] = [Connection Inventory].[Connection Inventory ID])) AND (([Application Master].[Deactivated Application Indicator])=False))

The Crosstab query is

TRANSFORM Count([Active Connections Summary Query].[Connection Inventory ID]) AS [CountOfConnection Inventory ID
SELECT [Active Connections Summary Query].[Application Master], Count([Active Connections Summary Query].[Connection Inventory ID]) AS [Total Of Connection Inventory ID
FROM [Active Connections Summary Query
GROUP BY [Active Connections Summary Query].[Application Master
PIVOT [Active Connections Summary Query].[Project Phase Grouping]

It appears that the crosstab query is unable to evaluate the subquery at execution time. Is there anyway around this

Help

Thank
Henry

 

[Chris2]

HELP!!

I am trying to execute a crosstab query and receive the following error message:

The Microsoft Jet database engine does not recognize "[Connection

Inventory].[Connection Inventory ID]" as a valid field name or expression.

The crosstab query is based on another query that works fine by itself.

This query has criteria to limit the records retrieved from a table (one to
many relationship) to only the most current record. If I delete this sub
query, the crosstab query works fine (except that it retrieves too many
records). The sub query that causes the crosstab query to fail is:

(([Project Phase History].[Date])=(Select Max ( [Date]) From [Project

Phase History] Where [Project Phase History].[Connection Inventory ID] =
[Connection Inventory].[Connection Inventory ID]))

The complete query is:

SELECT [Project Phase].[Project Phase Grouping], [Project Phase

History].Date, [Connection Inventory].[Connection Inventory ID],
[Application Master].[Application Master], [Application Master].[Deactivated
Application Indicator]

FROM [Project Phase] INNER JOIN ((([Application Master] INNER JOIN

Application ON [Application Master].[Application Master ID] =
Application.[Application Master ID]) INNER JOIN [Connection Inventory] ON
Application.[Application ID] = [Connection Inventory].[Application ID])
INNER JOIN [Project Phase History] ON [Connection Inventory].[Connection
Inventory ID] = [Project Phase History].[Connection Inventory ID]) ON
[Project Phase].[Project Phase ID] = [Project Phase History].[Project Phase
ID]

WHERE ((([Project Phase].[Project Phase Grouping])=2 Or ([Project

Phase].[Project Phase Grouping])=3) AND (([Project Phase
History].[Date])=(Select Max ( [Date]) From [Project Phase History] Where
[Project Phase History].[Connection Inventory ID] = [Connection
Inventory].[Connection Inventory ID])) AND (([Application
Master].[Deactivated Application Indicator])=False));

The Crosstab query is:

TRANSFORM Count([Active Connections Summary Query].[Connection Inventory

ID]) AS [CountOfConnection Inventory ID]

SELECT [Active Connections Summary Query].[Application Master],

Count([Active Connections Summary Query].[Connection Inventory ID]) AS
[Total Of Connection Inventory ID]

FROM [Active Connections Summary Query]
GROUP BY [Active Connections Summary Query].[Application Master]
PIVOT [Active Connections Summary Query].[Project Phase Grouping];

It appears that the crosstab query is unable to evaluate the subquery at

execution time. Is there anyway around this?

Help!

Thanks
Henry

The following is mentioned as a subquery, am I to assume it is an excerpt
of [Active Connections Summary Query]? It looks like it, but I'm not sure.

(([Project Phase History].[Date])=(Select Max ( [Date]) From [Project Phase
History] Where [Project Phase History].[Connection Inventory ID] =
[Connection Inventory].[Connection Inventory ID]))


I'm going to assume that this Query is [Active Connections Summary Query],
I'm hoping I'm not mistaken in that assumption.

SELECT [Project Phase].[Project Phase Grouping]
,[Project Phase History].Date
,[Connection Inventory].[Connection Inventory ID]
,[Application Master].[Application Master]
,[Application Master].[Deactivated Application Indicator]
FROM [Project Phase]
INNER JOIN
((([Application Master]
INNER JOIN
Application
ON [Application Master].[Application Master ID] =
Application.[Application Master ID])
INNER JOIN
[Connection Inventory]
ON Application.[Application ID] = [Connection Inventory].[Application
ID])
INNER JOIN
[Project Phase History]
ON [Connection Inventory].[Connection Inventory ID] = [Project Phase
History].[Connection Inventory ID])
ON [Project Phase].[Project Phase ID] = [Project Phase History].[Project
Phase ID]
WHERE ((([Project Phase].[Project Phase Grouping]) = 2
OR ([Project Phase].[Project Phase Grouping]) = 3)
AND (([Project Phase History].[Date]) =
(SELECT MAX([Date])
FROM [Project Phase History]
WHERE [Project Phase History].[Connection Inventory ID] =
[Connection Inventory].[Connection Inventory ID]))
AND (([Application Master].[Deactivated Application Indicator]) =
False));

While it *may* not have anything to do with the current problem, Date is a
reserved word in Access, and it should not be used as a Column name because
it can cause unexpected results.

Also, it can be unwise to use spaces in Table and Column names, because
that can cause unexpected results, too.

Also, in the subquery, MAX([Date]) should probably be:
MAX([Project Phase History].[Date]).
It could be that the query optimizer can't figure out which [Date] column
to use from the Inner or Outer Query, and may be throwing an error pointing
at the wrong part of the Query as the guilty party . . . (ok, so that
explanation is a bit of a reach . . .).

These are all ideas off the top of my head. The naming looks correct
(usually the error mentioned is caused by a misspelling somewhere), so I
don't think that's the problem. The Correlated call to the Outer Query
looks ok.

I'm unable to test my various ideas (too many tables and too many
assumptions to make), and am somewhat doubtful that they'll help in this
specific case.

My apologies I'm unable to do better.


Sincerely,

Chris O.







The Crosstab query is:

TRANSFORM COUNT([Active Connections Summary Query].[Connection Inventory
ID]) AS [CountOfConnection Inventory ID]
SELECT [Active Connections Summary Query].[Application Master]
,COUNT([Active Connections Summary Query].[Connection Inventory ID])
AS [Total Of Connection Inventory ID]
FROM [Active Connections Summary Query]
GROUP BY [Active Connections Summary Query].[Application Master]
PIVOT [Active Connections Summary Query].[Project Phase Grouping];

 

[Gary Walter]

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

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.

 

[Gary Walter]

Hi Henry,

Tom once had your same problem...same error msg
when he tried to base a crosstab on another query that
used a subquery in the WHERE clause.

Michel pointed out it is possible to hit a wall
in Jet with your "overall levels"
of FROM (not his exact words).

Tom's solution was to find the aggregate in the
original query (now changed to a group by
query), then INNER JOIN that query back with
original table in a second query that *would* work
in making the crosstab.

Or..you might try using DMax in your first query.
Please repond back *in this thread*
if that does not work.

SELECT Table2.Table2Date, Table1.Table1Data,
Table1.Table1RowTitle, Table2.Table2ColumnTitle
FROM Table1 INNER JOIN Table2
ON Table1.Table1Key = Table2.Table1FK
WHERE (Table2.Table2Date =
DMax ("Table2Date", "Table2",
"Table2.Table1FK = " & Table1.Table1Key));

Gary Walter

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

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.

 

[Henry]

I tried using DMax in the first query and that worked. Thanks for all your help. I've been wrestling with this for almost a week

Thanks again

Henry