Crosstab with subquery
Posted on 2009-12-23
I'm trying to generate a crosstab from a query which itself uses a subquery and getting the error"Microsoft Jet database engine does not recognize 'tblMonths.fmonth' as a valid field name or expression'.
The solutions I've found involve setting fmonth as a paramter to the crosstab, but this just results in a box being thrown up prompting me for a value of fmonth when I try to run the query which is not what I want.
My base query is called qryGenerateTimeSeries_Top1
SELECT tblData.Group, tblMonths.fMonth, tblData.VR, (SELECT TOP 1 tblTransfers.To
WHERE (((tblTransfers.TxDate)<tblMonths.fmonth) AND ((tblTransfers.Group)=tblData.Group))
ORDER BY tblTransfers.Group, tblTransfers.TxIdx;) AS ActingDMC
FROM tblData, tblMonths
ORDER BY tblData.Group, tblMonths.fMonth;
and the crosstab that throws the error is
TRANSFORM Sum(qryGenerateTimeSeries_Top1.VR) AS SumOfVR
GROUP BY qryGenerateTimeSeries_Top1.ActingDMC
The query qryGenerateTimeSeries_Top1 runs fine and gives me the results I am expecting.
What am i doing wrong?