Sorting crosstab order

[chimp]

i have a crosstab query running, i then query this crosstab to obtain the layout i require.

i was getting results as shown below:

my months were displayed as 01/01/04 for Jan 04, etc i just need Jan 04 to be displayed as the data is for the whole month....

i have discovered if i use

PIVOT Format([DateField],"mmm/yy");

this will do what i want, except for one thing

the months are displayed in the wrong order...

they appear like:

April, Feb, Jan,Jul, jun, mar, may

these are obviously being sorted in alphbetical order, can this be changed to sort by months as they should be

 

[SydneyGeek]

Not sure that you can -- the same thing drove me nuts when I encountered it. It seems that the crosstab format removes any intelligence about month ordering, if those months are in the column header. For some reason, if you re-orient so the months are in the ROW header and the otehr field is in the column header, you get back the ability to sort months correctly but that usually compromises the layout.

Denis

 

[chimp]

i have managed to sort this out,

got to the help file in access and type in crosstab, look for sorting

look for ColumnHeadings Property

You can use the ColumnHeadings property to specify the order or to limit the number of columns displayed in a crosstab query. For example, in a query that displays the names of countries, you may want to show them in a specific order, such as United States first, Canada second, and the United Kingdom third.
Setting
Enter the data values from the Column Heading field of the crosstab query as a string expression in the order you want the headings displayed in the datasheet for the crosstab query, separated by a comma (or the list separator set in the Regional Settings Properties dialog box in Windows Control Panel). The following table shows sample ColumnHeadings property settings and the result in the crosstab query's datasheet.
Sample setting Result
"Qtr 1", "Qtr 2", "Qtr 3", "Qtr 4" Displays column headings for each quarter.
"Mexico", "Canada", "USA" Displays column headings for each country.
You can set this property by using the query's property sheet. You can also set it in SQL view of the Query window or in Visual Basic by using a PIVOT clause in the SQL statement.
Note The column headings you specify for the ColumnHeadings property must exactly match the data values in the Column Heading field in the query design grid. Otherwise, the data won't appear in the columns.
Remarks
You use the ColumnHeadings property to obtain more control over the appearance of column headings in a crosstab query. By default, Microsoft Access displays all data values as column headings in ascending order. For example, if your Column Heading field name is Month, the default column headings displayed will be April, August, December, February, and so on. You can use the ColumnHeadings property to display the data in the correct order with the appropriate setting: "January", "February", "March", and so on.
If you include a column heading in the ColumnHeadings property setting, the column is always displayed in query Datasheet view, even if the column contains no data. This is useful for a report based on a crosstab query, for example, when you always want to display the same column headings in the report.
Tip You can use the ColumnHeadings property to improve the speed of some crosstab queries by limiting the number of columns displayed.