Crosstab Query Error (1 Viewer)

[nuttychick]

Good morning - hoping someone can shed some light on this one.
This is an Access 2003 database...
I have several queries that build up into one which I have then used to create a Crosstab Query.
All the sub queries run fine. But when I combine these with a crosstab I get the error

'The Microsoft Jet database engine does not recognize 'studies.study_id' as a valid field name or expression.

studies.study_id is a field in more than one of the sub queries, but is added just once in the final query.
The Crosstab part of the query works fine if I build it independant of other tables\queries etc...its when I start adding additional qry's in that it falls over...

Looking on the help I'm told to check that the Query Parameters are specified - but the study_id field does not have parameters set on any of the queries.
There is criteria set on other fields in the sub queries but these are permanent criteria to reduce the records retrieved...

Hoping someone has the answers, the only thing I can think of doing is making the crosstab section of the report export into a temporary table and use that in the final report...

Please help!

TRANSFORM Last(QryDataDump2.StatusDate) AS LastOfStatusDate
SELECT QryDataDump2.StudyID, QryDataDump2.[Study Code], QryDataDump2.[Business Unit], QryDataDump2.JDBCreationDate, QryDataDump2.study_title, QryDataDump2.study_description, QryDataDump2.OGD, QryDataDump2.LeadSupplier, QryDataDump2.[SDD Involvement], QryDataDump2.[Study Progress], QryDataDump2.PLine, QryDataDump2.SLine, QryDataDump2.Other, QryDataDump2.P01Value, QryDataDump2.ApprovedOBCCost, QryDataDump2.Rework, QryDataDump2.IMSPortfolioAreaName, QryDataDump2.[Current Status], QryDataDump2.[Current Status Date], QryDataDump2.QryDDStudyData.Working, QryDataDump2.StudyTypeApp, QryDataDump2.[Current RAG], QryDataDump2.[RAG Updated Date], QryDataDump2.[HMRC Program Area], QryDataDump2.Description, QryDataDump2.LastOfVRFDateID, QryDataDump2.LastOfVRFdate, QryDataDump2.LastOfLandingSlotID, QryDataDump2.LastOfLandingSlotDate, QryDataDump2.LastOfViabilityEndDateID, QryDataDump2.LastOfViabilityEndDate, QryDataDump2.LastOfITSuppPropIssDateID, QryDataDump2.LastOfITSuppPropIssDate, QryDataDump2.LastOfSellByDateID, QryDataDump2.LastOfSellByDate, QryDataDump2.LastOfIAGAppDateID, QryDataDump2.LastOfIAGAppDate, QryDataDump2.LastOfCommercialReviewId, QryDataDump2.LastOfCommercialReviewDate, QryDataDump2.LastOfProjectedImplementationID, QryDataDump2.LastOfProjectedImplementationDate, QryDataDump2.FirstOfVRFDateID, QryDataDump2.FirstOfVRFdate, QryDataDump2.FirstOfLandingSlotID, QryDataDump2.FirstOfLandingSlotDate, QryDataDump2.FirstOfViabilityEndDateID, QryDataDump2.FirstOfViabilityEndDate, QryDataDump2.FirstOfITSuppPropIssDateID, QryDataDump2.FirstOfITSuppPropIssDate, QryDataDump2.FirstOfSellByDateID, QryDataDump2.FirstOfSellByDate, QryDataDump2.FirstOfIAGAppDateID, QryDataDump2.FirstOfIAGAppDate, QryDataDump2.FirstOfCommercialReviewId, QryDataDump2.FirstOfCommercialReviewDate, QryDataDump2.FirstOfProjectedImplementationID, QryDataDump2.FirstOfProjectedImplementationDate, QryDataDump2.[First Proposal Issued], QryDataDump2.[First Proposal Issued Date], QryDataDump2.[Aspire Viability Study Manager], QryDataDump2.[HMRC Viability Study Manager], QryDataDump2.[SDD Bid Manager], QryDataDump2.LastOfOverall, QryDataDump2.LastOfDateRAGamended
FROM QryDataDump2
GROUP BY QryDataDump2.StudyID, QryDataDump2.[Study Code], QryDataDump2.[Business Unit], QryDataDump2.JDBCreationDate, QryDataDump2.study_title, QryDataDump2.study_description, QryDataDump2.OGD, QryDataDump2.LeadSupplier, QryDataDump2.[SDD Involvement], QryDataDump2.[Study Progress], QryDataDump2.PLine, QryDataDump2.SLine, QryDataDump2.Other, QryDataDump2.P01Value, QryDataDump2.ApprovedOBCCost, QryDataDump2.Rework, QryDataDump2.IMSPortfolioAreaName, QryDataDump2.[Current Status], QryDataDump2.[Current Status Date], QryDataDump2.QryDDStudyData.Working, QryDataDump2.StudyTypeApp, QryDataDump2.[Current RAG], QryDataDump2.[RAG Updated Date], QryDataDump2.[HMRC Program Area], QryDataDump2.Description, QryDataDump2.LastOfVRFDateID, QryDataDump2.LastOfVRFdate, QryDataDump2.LastOfLandingSlotID, QryDataDump2.LastOfLandingSlotDate, QryDataDump2.LastOfViabilityEndDateID, QryDataDump2.LastOfViabilityEndDate, QryDataDump2.LastOfITSuppPropIssDateID, QryDataDump2.LastOfITSuppPropIssDate, QryDataDump2.LastOfSellByDateID, QryDataDump2.LastOfSellByDate, QryDataDump2.LastOfIAGAppDateID, QryDataDump2.LastOfIAGAppDate, QryDataDump2.LastOfCommercialReviewId, QryDataDump2.LastOfCommercialReviewDate, QryDataDump2.LastOfProjectedImplementationID, QryDataDump2.LastOfProjectedImplementationDate, QryDataDump2.FirstOfVRFDateID, QryDataDump2.FirstOfVRFdate, QryDataDump2.FirstOfLandingSlotID, QryDataDump2.FirstOfLandingSlotDate, QryDataDump2.FirstOfViabilityEndDateID, QryDataDump2.FirstOfViabilityEndDate, QryDataDump2.FirstOfITSuppPropIssDateID, QryDataDump2.FirstOfITSuppPropIssDate, QryDataDump2.FirstOfSellByDateID, QryDataDump2.FirstOfSellByDate, QryDataDump2.FirstOfIAGAppDateID, QryDataDump2.FirstOfIAGAppDate, QryDataDump2.FirstOfCommercialReviewId, QryDataDump2.FirstOfCommercialReviewDate, QryDataDump2.FirstOfProjectedImplementationID, QryDataDump2.FirstOfProjectedImplementationDate, QryDataDump2.[First Proposal Issued], QryDataDump2.[First Proposal Issued Date], QryDataDump2.[Aspire Viability Study Manager], QryDataDump2.[HMRC Viability Study Manager], QryDataDump2.[SDD Bid Manager], QryDataDump2.LastOfOverall, QryDataDump2.LastOfDateRAGamended
PIVOT QryDataDump2.Status;

 

[DCrake]

Can you post a cut down version of you mdb to test?

 

[nuttychick]

Short answer is no...
Long answer is no, not without a fair bit of work to remove the sensitive HMRC\Capgemini data it contains and replace it with test data etc...

Any ideas that spring to mind I can check before doing that?

 

[DCrake]

Ok,

So having a further look at your code you seem to have a lot of row headings in your cross tab query, which all origniate from a single underlying query QryDataDump2.

Your initial query regarded studies_study_id. Is this field in your underlying query?

I would ask myself.. do I need all the fields in the query?


For example, do you actually need both the Id and value
.../
QryDataDump2.LastOfLandingSlotID,
QryDataDump2.LastOfLandingSlotDate,
QryDataDump2.LastOfViabilityEndDateID,
QryDataDump2.LastOfViabilityEndDate,
QryDataDump2.LastOfITSuppPropIssDateID,
QryDataDump2.LastOfITSuppPropIssDate,
/...


Try stripping down all the row headings down to just one and see if it works, then introduce more fields as row headings until you fiind the root cause.

 

[nuttychick]

DCrake,

Have sent you a private message - wondering if I can email you copy of the encrypted db direct - not ideal to post on site.

I will try your suggestions above in the meantime.

Thanks!

 

[nuttychick]

Problem resolved - turned out to be some 'funky' criteria in a sub query. Changed how this was performed and happy days - its working now

 

[DCrake]

Glad you got it resolved. Did you learn anything?

 

[nuttychick]

That I am a legend....lol....only kidding.

I did learn not to give up investigating problems so quickly...and that experience is v important!
Also learnt that a 10 year old database tends to contain some interesting coding techniques...