Crosstab Sorting Issue

[Acropolis]

Hi,

I have a cross tab which has dates as the columns, i want these formatted as mmm-yy as the day is unimportant, I can get them formatted as such, but when the sorting is applied they are sorted alphabetically not in date order.

Is there a way round this? Having them show as dd-mm-yy will cause confusion when looking at is as people will think it's for specific date rather than the month.

The date is actually 01-mm-yyyy, always the 1st of the month from the table it comes from.

Any idea's?

Thanks

 

[Acropolis]

Don't want to set the column names manually in the query as they will change all the time as they are dependant upon the start and end date of a project, so never fixed.

 

[isladogs]

Create a standard query where the dates are sorted in date order
Then base your crosstab query on that and display dates in mmm-yy format

 

[Acropolis]

Tried that, and it appears to ignore the sort from the initial query entirely.

Have just re-tried it without the format mmm-yy on it, tried ASC and DESC and it makes no difference, it appears to pull the sort from the cross tab query only.

 

[isladogs]

Here's a chart using a dynamic crosstab query that I created by that method:

 

[Acropolis]

Ok.

I've put everything into a normal query, and it shows all the data, left the date format alone as normal, and sorted ASC on the date.

Created a Crosstab based on that, applied formatting to the date field in the cross tab and it didn't make any difference.

 

[isladogs]

Well, as you can see from my example, it is perfectly possible to make this work. There's no trickery in what I showed you ...

 

[JHB]

Could you post some sample data in a database or Excel sheet?