Solved Crosstab Query - does not recognize as valid field name or expression (1 Viewer)

jack555

Member
Local time
Today, 16:55
Joined
Apr 20, 2020
Messages
93
while creating a crosstab query throws below error. The source is another query having a calculation. please help how to rectify this.

Crosstab Query
Code:
PARAMETERS qryKPIDataEntered.KPI_Code Text ( 255 );
TRANSFORM First(qryDataSequence.Calculation) AS FirstOfCalculation
SELECT qryDataSequence.KPI_Title, qryDataSequence.Target
FROM qryDataSequence
GROUP BY qryDataSequence.KPI_Title, qryDataSequence.Target
PIVOT qryDataSequence.EntrySequence;

Source Query - qryDataSequence
Code:
SELECT (SELECT COUNT(*)+1   
FROM qryKPIDataEntered AS T2   
WHERE T2.KPI_code = qryKPIDataEntered.KPI_code AND qryKPIDataEntered.Action_Date < T2.Action_Date) AS EntrySequence, qryKPIDataEntered.*
FROM qryKPIDataEntered;

Error as below. I have added a parameter to crosstab query as someone suggested but still, I am missing something.

1605502049194.png


*minor edit was done to add source query name.
 
Last edited:

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:55
Joined
Aug 30, 2003
Messages
36,118
Unless I'm blind, the source query does not return that date field, so it can't be used in the criteria.
 

jack555

Member
Local time
Today, 16:55
Joined
Apr 20, 2020
Messages
93
Unless I'm blind, the source query does not return that date field, so it can't be used in the criteria.
Thanks for the reply. It does return the date field and in the date format only.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:55
Joined
Aug 30, 2003
Messages
36,118
Sorry, I mixed up which was the base query. Have you tried adding a parameter on that date field?
 

jack555

Member
Local time
Today, 16:55
Joined
Apr 20, 2020
Messages
93
Instead of building a crosstab query from another query having a calculated field, I created a make table query and done the crosstab from the table. it works as intended. I believe the issue with calculated field query. Had a workaround, but helps for me.
 

Users who are viewing this thread

Top Bottom