Column Heading in Crosstab Query

[UHNSTrust]

I'm changing th eSQL of a crosstab query on the fly (querydef). However every time I do it the Column Headings setting of the query gets lost. Can you keep this or program it through VBA.

My SQL for the query is

TRANSFORM Max(IIf([TAB_SCORE]='1',[TAB_NO_Value1],IIf([STAFF_NAME]='',[TAB_DATE],[STAFF_NAME]))) AS Expr1
SELECT EntID.NamStr AS [Client Name], EntDem.Dob AS [Date of Birth], EntID.ID AS [ID No]
FROM (EntDem INNER JOIN (((CLIENT_ASSESSMENT_HEADER INNER JOIN CLIENT_ASSESSMENT_COMMENT ON (CLIENT_ASSESSMENT_HEADER.EntSys = CLIENT_ASSESSMENT_COMMENT.EntSys) AND (CLIENT_ASSESSMENT_HEADER.ASS_CODE = CLIENT_ASSESSMENT_COMMENT.ASS_CODE) AND (CLIENT_ASSESSMENT_HEADER.ASS_DATE = CLIENT_ASSESSMENT_COMMENT.ASS_DATE) AND (CLIENT_ASSESSMENT_HEADER.ASS_TIME = CLIENT_ASSESSMENT_COMMENT.ASS_TIME)) INNER JOIN CLIENT_ASS_TAB_DETAILS ON (CLIENT_ASSESSMENT_HEADER.EntSys = CLIENT_ASS_TAB_DETAILS.EntSys) AND (CLIENT_ASSESSMENT_HEADER.ASS_CODE = CLIENT_ASS_TAB_DETAILS.ASS_CODE) AND (CLIENT_ASSESSMENT_HEADER.ASS_DATE_TIME = CLIENT_ASS_TAB_DETAILS.ASS_DATE_TIME)) INNER JOIN EntID ON CLIENT_ASSESSMENT_HEADER.EntSys = EntID.EntSys) ON EntDem.EntSys = EntID.EntSys) INNER JOIN CLIENT_ASS_TAB_VALUES ON (CLIENT_ASS_TAB_DETAILS.ASS_CODE = CLIENT_ASS_TAB_VALUES.ASS_CODE) AND (CLIENT_ASS_TAB_DETAILS.TAB_NO = CLIENT_ASS_TAB_VALUES.TAB_NO)
WHERE (((CLIENT_ASSESSMENT_HEADER.ASS_CODE)='CPA7DAY') AND ((CLIENT_ASSESSMENT_HEADER.ASS_COMPLETE)<>'2') AND ((EntID.LstNam) Not Like '*ZCareplus*') AND ((CLIENT_ASS_TAB_DETAILS.ASS_DATE) Between #1/4/2007# And #5/30/2007#))
GROUP BY EntID.NamStr, EntDem.Dob, EntID.ID
PIVOT CLIENT_ASS_TAB_VALUES.HeadingName1
WITH OWNERACCESS OPTION;

The colum names I want to keep are;
Client Name, Date of Birth, ID No, Date of admission, Ward, Date of Discharge from Ward, CPA Level, Proposed Date of Contact, Care Co-Ordinator Not Allocated, Care co-ordinator Allocated, Received & Accepted By, Team/Clinical Area, Date of Actual Contact, Method Of Contact, Reason for NO 7 Day Follow-Up

Thanks in advance

Jonathan

 

[lameid]

Do you mean the column headings change for your column heading field/ column?

If so, in the QBE (query design view), right click the field, get its properties and specify the list you want in the column headings property. (Unfortunately I don't know the SQL cold for crosstab queries, so I had to give you directions.)

 

[PHV]

You may use the PIVOT field IN (value list) syntax:
...
PIVOT CLIENT_ASS_TAB_VALUES.HeadingName1 IN ('Client Name','Date of Birth','ID No','Date of admission','Ward','Date of Discharge from Ward','CPA Level','Proposed Date of Contact','Care Co-Ordinator Not Allocated','Care co-ordinator Allocated','Received & Accepted By','Team/Clinical Area','Date of Actual Contact','Method Of Contact','Reason for NO 7 Day Follow-Up')

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[UHNSTrust]

Hi,

Thanks for the responses. PHV, this worked great.

Jonathan