[Last Call] Learn how to a build a cloud-first strategyRegister Now


Crosstab with subquery

Posted on 2009-12-23
Medium Priority
Last Modified: 2012-05-08

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
FROM tblTransfers
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
SELECT qryGenerateTimeSeries_Top1.ActingDMC
FROM qryGenerateTimeSeries_Top1
GROUP BY qryGenerateTimeSeries_Top1.ActingDMC
PIVOT qryGenerateTimeSeries_Top1.fMonth;

The query qryGenerateTimeSeries_Top1 runs fine and gives me the results I am expecting.
What am i doing wrong?
Question by:richary
  • 2
LVL 43

Accepted Solution

pcelba earned 2000 total points
ID: 26111684
You should rather ask what is Access doing wrong... I think Jet engine does not have enough power to process this complex task.

You have two options (at least):
1) Create temporary table from your query and calculate pivot results from this table:

SELECT tblData.Group, tblMonths.fMonth, tblData.VR, (SELECT TOP 1 tblTransfers.To
FROM tblTransfers
WHERE (((tblTransfers.TxDate)<tblMonths.fmonth) AND ((tblTransfers.Group)=tblData.Group))
ORDER BY tblTransfers.Group, tblTransfers.TxIdx) AS ActingDMC
INTO YourTempTable
FROM tblData, tblMonths
ORDER BY tblData.Group, tblMonths.fMonth;

2) Try newer Access version which does not use Jet engine but much better ACE engine

Author Closing Comment

ID: 31669370
pcelba, Have decided to use temp table as you suggest. It works fine. I have to use 2003 so the other option isn't available to me.

Thanks for your help-saved me a lot of time!
LVL 43

Expert Comment

ID: 26113910
You are welcome!

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question