Crosstab Sort Order

G

Guest

Hi

I have a table with a month order field in the format 200511 200510 200509
etc and a seperate table with an appropriate decode Nov 2005 Oct 2005 Sep
2005 etc

I want to do a Crostab based on the last twelve months data and I have set
up a sub query on the decode table and this works as expected

However when this is added to a crosstab and the descriptive narrative is
added as a column heading then the crosstab sorts the data alphabetically ie
Apr 05 Aug 05 Dec 04 Feb 05 etc.

Please could you suggest a solution to retain the sort order for the
crosstab so that the oldest data is in column 1 and the newest in column 12

Please accept my thanks in anticipation

Kind Rgds and Seasons Greetings

Colin
 
D

Duane Hookom

You can enter the derived column headings in the Column Headings property in
the order you want the columns to appear.

If you are displaying the result in a form or report, the column order
shouldn't make much difference.
 
G

Guest

Hi Duane

Thanks for the reply.

Unfortunately it is not practical to keep changing the column Headings
propert as this will require manual intervention on a monthly basis to set
the properties ie if they were set today they would display Dec 2004 through
Nov 2005 for the top Twelve, but next month I would have automatically run
the crosstab and it would display Jan 2005 through Dec 2005.

The purpose for this is that I subsequently create a table based on the
crosstab which will be used as a table for a web page in a visual web 2005
project. The data for this project is updated by batch overnight and
therefore I need do a make table with the correct field headings as time goes
forward.
rgds

Colin
 
D

Duane Hookom

Use a column heading expression of:
ColHead: "Mth" & DateDiff("m",[YourDateField], Date())
Set the Column Headings property to:
"Mth11","Mth10",...."Mth0"
Mth0 will be the current month and Mth11 is 11 months previous. Your column
headings will not vary.
 
C

Chris2

Duane Hookom said:
Use a column heading expression of:
ColHead: "Mth" & DateDiff("m",[YourDateField], Date())
Set the Column Headings property to:
"Mth11","Mth10",...."Mth0"
Mth0 will be the current month and Mth11 is 11 months previous. Your column
headings will not vary.

Duane,

Thank you, as that idea also provides me with help on another
thread.


Sincerely,

Chris O.
 

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