crosstab sorting

[jommyjet]

I have a crosstab query that breaks out monthly totals, but I'd like a more decriptive nanme for the column heading than 1/1/2003. I have a field containing a text value jan2003 and I'd like to use that as a heading, but access sorts the column headings by the text value so apr2003 is ahead of jan2003. Is there a way to display the text field, but sort by the date field? thanks

 

[jommyjet]

i imagine this can be posed as a renaming question. can I rename crosstab column headings?

 

[CosmoKramer]

Utilize the IN clause of the PIVOT statement. This can be used to do two things, limit/define the columns that will be printed, and control the order they are displayed. So, you could do something like this:

PIVOT TableName.FieldName In ('jan2003', 'feb2003', 'mar2003', ....etc.);

 

[psemianonymous]

Working from another alley, you can first make a select query which does all the "column heading" formatting for you, i.e. add a field which you call

ColHeader: Format([mydatefld],"mmmyyyy&quot


Then have the crosstab query pull its data from this new query and you will have your formatted headers.

--
Find common answers using Google Groups:

http://groups.google.com/groups?group=comp.databases.ms-access

Corrupt MDBs FAQ

http://www.granite.ab.ca/access/corruptmdbs.htm