Crosstab with subquery

Posted on 2009-12-23
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
    LVL 41

    Accepted Solution

    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

    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 41

    Expert Comment

    You are welcome!

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Enabling OSINT in Activity Based Intelligence

    Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

    In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
    If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
    Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
    In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

    779 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

    Need Help in Real-Time?

    Connect with top rated Experts

    15 Experts available now in Live!

    Get 1:1 Help Now