Column Headings

Access 2000

TRANSFORM Count(Demographics.[NHS Number]) AS [CountOfNHS Number]
SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total Of
NHS Number]
FROM Demographics
GROUP BY Demographics.Hospital
PIVOT Demographics.[Primary diagnosis];

Q - How to I change Column Heading Total of NHS Number to Total
Q - How to I change Primary diagnosis headings from bound column heading C48
to text string associated with it "Peritoneal"

"MDW" wrote:
Access 2000

TRANSFORM Count(Demographics.[NHS Number]) AS [CountOfNHS Number]
SELECT Demographics.Hospital, Count(Demographics.[NHS Number]) AS [Total Of
NHS Number]
FROM Demographics
GROUP BY Demographics.Hospital
PIVOT Demographics.[Primary diagnosis];

Q - How to I change Column Heading Total of NHS Number to Total
Q - How to I change Primary diagnosis headings from bound column heading C48
to text string associated with it "Peritoneal"


Hi MDW,

If you don't already have a way of translating
"C48" to "Peritoneal" from your existing data,
then it sounds to me like you need a translation
table

DiagTrans

Diag Trans
C48 Peritoneal
.... ....

Then your query might look like:

TRANSFORM Count(D.[NHS Number]) AS [CountOfNHS Number]
SELECT D.Hospital, Count(D.[NHS Number]) AS [Total]
FROM Demographics As D
LEFT JOIN
DiagTrans As DT
ON D.[Primary diagnosis] = DT.Diag
GROUP BY D.Hospital
PIVOT SWITCH(DT.Trans IS NULL, D.[Primary diagnosis], True, DT.Trans);

If you could be assured that every Primary diagnostic
value was accounted for in the translation table, then
you would not need the Switch stmt in PIVOT clause:

PIVOT DT.Trans;


Of course I may have misunderstood completely.

Good luck,

Gary Walter