Sorting in a Cross Tab Query

[PerryK]

I have a basic Cross Tab Query that is feed by a table that is updated monthy.

The months are labled as 200901, 200902, 200903, etc..
The are also lines for 3Month, 6Month, 12Month, and YTD Average.

The Months are used for the column headings in the Cross Tab Query.

The problem i have is that when the data is updated each month, the dates
are not in order.

As an example when April data is added:
200901 200902 200903 3Month 6Month 12Month 200904 YTD

The correct order should be:
200901 200902 200903 200904 3Month 6Month 12Month YTD

Is there an easy way to do this?

Thanks

 

[Jerry Whittle]

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'200901','200902','200903','200904','3Month','6Month','12Month','YTD'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want. You can also make data not show up by
taking out a column. For example, remove 200902 and 200902 data won't show.
It will also create a column even if there isn't any data such as '211109'.

 

[PerryK]

Jerry,

That did it -- Thanks much
--
Perry K

One way to fix this problem is to open the crosstab query in design view;
right click in the area near the tables; and select Properties. Next go into
the Column Headings and put in something like:

'200901','200902','200903','200904','3Month','6Month','12Month','YTD'

The above should match the expected data. Advantages of Column Headings
include sorting across as you want. You can also make data not show up by
taking out a column. For example, remove 200902 and 200902 data won't show.
It will also create a column even if there isn't any data such as '211109'.