Column Heading on Crosstab Query

[yanto]

Hi,
Is it posssible to sort the column heading of the crosstab query? I
have a crosstab query and I want to sort the column heading name
produced by that query, I just want to control the sequence of column
heading name.
TIA
Yanto

 

[Allen Browne]

Use the Column Headings property of the crosstab query, e.g.:
"Jan";"Feb";"Mar"; ...

More info:
http://allenbrowne.com/ser-67.html#ColHead

 

[yanto]

Hi Allen,
Thanks for your fasstt reponse, I have followed the instruction in
your link, but got unexpected result.
my query is:

TRANSFORM Sum(qdfPMGroupPerDept.Val) AS SumOfVal
SELECT qdfPMGroupPerDept.PayrollID, qdfMSalOTperDept.StartDate,
qdfPMGroupPerDept.AccPeriod, qdfMSalOTperDept.Dept,
qdfMSalOTperDept.BasicSal, qdfMSalOTperDept.OT1, qdfMSalOTperDept.OT2
FROM qdfPMGroupPerDept INNER JOIN qdfMSalOTperDept ON
(qdfPMGroupPerDept.Dept = qdfMSalOTperDept.Dept) AND
(qdfPMGroupPerDept.PayrollID = qdfMSalOTperDept.PayrollID)
GROUP BY qdfPMGroupPerDept.PayrollID, qdfMSalOTperDept.StartDate,
qdfPMGroupPerDept.AccPeriod, qdfMSalOTperDept.Dept,
qdfMSalOTperDept.BasicSal, qdfMSalOTperDept.OT1, qdfMSalOTperDept.OT2
ORDER BY qdfPMGroupPerDept.Extra
PIVOT qdfPMGroupPerDept.Extra In
("Calori","ExtTrans","ExtMilk","Insurance");

without clause IN ...., these columns (Calori etc) got value, but
after adding IN clause, they contain no value.
Is there anything I missed?
Thanks for help

Best Regard
Yanto

 

[Allen Browne]

It would seem that the values you typed do not exactly match the values in
the field.

Spaces? Null characters? Fixed-width fields?

 

[yanto]

There are no spaces or null characters. the table's field width is 50
(text)
the content of that field are "Calori","ExtTrans" etc.
Do I have to use trim() function?
Any suggestion? Tried to googling but didn't get good and matched
result.
Any sample code to try?
TIA

 

[Allen Browne]

There may be something else going on, such as a lookup field that displays a
text value (in a combo) that is not the actual value stored in the table.

The fact that the crosstab returns nothing suggests that the actual names
don't match the values you typed into the Column Headings property. Perhaps
you could clear the Column Headings property, and get Access to show you the
field names when the crosstab does work.

Open the Immediate Window (Ctrl+G.)
Enter something like this:
? CurrentDb.QueryDefs("Query1").Fields(1).Name
changing the 1 to other values to get the other field names too.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

There are no spaces or null characters. the table's field width is 50
(text)
the content of that field are "Calori","ExtTrans" etc.
Do I have to use trim() function?
Any suggestion? Tried to googling but didn't get good and matched
result.
Any sample code to try?
TIA