Crosstab data missing (1 Viewer)

[nuttychick]

I have a very strange problem...

I have some normal select queries created that build up into one query. The final query is a cross tab.
The purpose of this final query is to show all the information for one record and have the STATUS TYPES as column headings the DATE of the STATUS TYPE is the value of the cross tab, so I get the record details along with the date of each Status types, each record may or may not have a date for each status type.

The query appears to run fine, however when testing I find that some of the columns for the status types are not populated with the date even though one definatley exists...it appears to be very random.

I have worked backwards - firstly changing the cross tab to a select query, when doing this all the status types and dates for each record are listed correctly.. soon as you change it back to a crosstab some of the dates are missing...so it's something the Crosstab does not like?

I have crosstab's minimal fields including just the record ID STATUS TYPE and DATE and this works fine...
Any ideas?

 

[DCrake]

Is it possible to send a small snippet of the database to examine?

 

[nuttychick]

Investigates seems to show that the crosstab might not like memo fields?
I have removed the only memo field and I have been working from a good point and then adding fields to the query one to by one - there is appears to be a point at after linking 1 particular query and adding more than 2 fields from this query is when the data is lost.
The query I'm linking to is a simple query to gather contact names - although it is from a right mix of old messy tables...

here is the final crosstab query SQL...

TRANSFORM Last(QryDataDump.StatusDate) AS LastOfStatusDate
SELECT QryDataDump.[Study Code], QryDataDump.study_title, QryDataDump.study_description, QryDataDump.[Study Type], QryDataDump.LastOfOverall AS RAG, QryDataDump.LastOfDateRAGamended AS [RAG Updated], QryDataDump.[Current Status], QryDataDump.[Current Status Date], QryDataDump.[Business Unit], QryDataDump.[HMRC Program Area], QryDataDump.IMSPortfolioAreaName, QryDataDump.OGD, QryDataDump.LeadSupplier, QryDataDump.[SDD Involvement], QryDDSlowContacts.[Aspire Viability Study Manager], QryDDSlowContacts.[Lead Architect], QryDDSlowContacts.[Opportunity Owner], QryDDContact.AspireBusinessManagerName, QryDDContact.AspireDeliveryManager, QryDDContact.SDLT, QryDDContact.[SD Porfolio], QryDDSlowContacts.[Fujitsu Architect], QryDDSlowContacts.[Accenture Architect], QryDDContact.[Aspire Requirements Manager], QryDDSlowContacts.[HMRC Viability Study Manager], QryDDContact.[SDD Bid Manager], QryDDContact.[IMS Business Partner], QryDDContact.SRO, QryDDContact.[Business Project Manager], QryDDContact.[Strategic Architect], QryDDContact.[SDD Architect], QryDDContact.[IMS Asset Manager], QryDDContact.[Commercial Advisor], QryDDContact.[Portfolio Delivery Manager], QryDataDump.JDBCreationDate, QryDataDump.[Baseline VRG], QryDataDump.[Baseline Viability Start], QryDataDump.[Baseline Viability End], QryDataDump.[Baseline IT Supplier Proposal Due], QryDataDump.[Baseline Proposal Expiry], QryDataDump.[Baseline SRO Approval], QryDataDump.[Baseline Commercial Sign Off], QryDataDump.[Baseline Projected Implementation], QryDataDump.[Current VRG], QryDataDump.[Current Viability Start], QryDataDump.[Current Viability End], QryDataDump.[Current IT Supplier Proposal Due], QryDataDump.[Current Proposal Expiry], QryDataDump.[Current SRO Approval], QryDataDump.[Current Commercial Sign Off], QryDataDump.[Current Projected Implementation], QryDataDump.PLine AS [P-Line], QryDataDump.SLine AS [S-Line], QryDataDump.Other, QryDataDump.P01Value AS [P01 Value], QryDataDump.ApprovedOBCCost AS [Approved OBC £], QryDataDump.Rework, QryDataDump.QryDDStudyData.Working AS Active, QryDataDump.[First Proposal Issued Date], Last(QryDataDump.[IT Supplier Proposal Slippage Comment]) AS [IT Supplier Proposal Slippage Comment]
FROM (QryDataDump LEFT JOIN QryDDContact ON QryDataDump.[Study Code] = QryDDContact.Feasibility_code) LEFT JOIN QryDDSlowContacts ON QryDataDump.StudyID = QryDDSlowContacts.study_id
WHERE (((QryDataDump.[Study Type])="Viability" Or (QryDataDump.[Study Type])="Viability Planning"))
GROUP BY QryDataDump.[Study Code], QryDataDump.study_title, QryDataDump.study_description, QryDataDump.[Study Type], QryDataDump.LastOfOverall, QryDataDump.LastOfDateRAGamended, QryDataDump.[Current Status], QryDataDump.[Current Status Date], QryDataDump.[Business Unit], QryDataDump.[HMRC Program Area], QryDataDump.IMSPortfolioAreaName, QryDataDump.OGD, QryDataDump.LeadSupplier, QryDataDump.[SDD Involvement], QryDDSlowContacts.[Aspire Viability Study Manager], QryDDSlowContacts.[Lead Architect], QryDDSlowContacts.[Opportunity Owner], QryDDContact.AspireBusinessManagerName, QryDDContact.AspireDeliveryManager, QryDDContact.SDLT, QryDDContact.[SD Porfolio], QryDDSlowContacts.[Fujitsu Architect], QryDDSlowContacts.[Accenture Architect], QryDDContact.[Aspire Requirements Manager], QryDDSlowContacts.[HMRC Viability Study Manager], QryDDContact.[SDD Bid Manager], QryDDContact.[IMS Business Partner], QryDDContact.SRO, QryDDContact.[Business Project Manager], QryDDContact.[Strategic Architect], QryDDContact.[SDD Architect], QryDDContact.[IMS Asset Manager], QryDDContact.[Commercial Advisor], QryDDContact.[Portfolio Delivery Manager], QryDataDump.JDBCreationDate, QryDataDump.[Baseline VRG], QryDataDump.[Baseline Viability Start], QryDataDump.[Baseline Viability End], QryDataDump.[Baseline IT Supplier Proposal Due], QryDataDump.[Baseline Proposal Expiry], QryDataDump.[Baseline SRO Approval], QryDataDump.[Baseline Commercial Sign Off], QryDataDump.[Baseline Projected Implementation], QryDataDump.[Current VRG], QryDataDump.[Current Viability Start], QryDataDump.[Current Viability End], QryDataDump.[Current IT Supplier Proposal Due], QryDataDump.[Current Proposal Expiry], QryDataDump.[Current SRO Approval], QryDataDump.[Current Commercial Sign Off], QryDataDump.[Current Projected Implementation], QryDataDump.PLine, QryDataDump.SLine, QryDataDump.Other, QryDataDump.P01Value, QryDataDump.ApprovedOBCCost, QryDataDump.Rework, QryDataDump.QryDDStudyData.Working, QryDataDump.[First Proposal Issued Date]
ORDER BY QryDataDump.[Study Code]
PIVOT QryDataDump.Status;