Sorting in a Cross Tab Query

P

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
 
J

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'.
 
P

PerryK

Jerry,

That did it -- Thanks much
--
Perry K


Jerry Whittle said:
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'.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top