Sorting Month/Year within a Crosstab (1 Viewer)

[pickslides]

Hi all, i'm sure there will be a quick fix for this problem.

I have a crosstab where I want year and month in the header so I have formatted a variable MTHYR to be of the form mm/yyyy.

This works well as Access only wants one variable in the column header. My problem is that it is not sorted nicely and with my data being between Aug 2013 and Jun 2014 the headers look like this.

1/2014,10/2013,11/2013,12/2013,2/2014, not a very nice order.

Is there a way I can reformat MTHYR or a completely different solution?


My current code is

TRANSFORM Count(VOR_CALD_REGION.[Case Number]) AS [CountOfCase Number]
SELECT VOR_CALD_REGION.[Dealer Name], Count(VOR_CALD_REGION.Case Number]) AS [Total Of Case Number]
FROM VOR_CALD_REGION
GROUP BY VOR_CALD_REGION.[Dealer Name]
PIVOT VOR_CALD_REGION.MTHYR;

Thx, MQ

 

[vbaInet]

This should help somewhat:
http://allenbrowne.com/ser-67.html

 

[pickslides]

That worked really well.

Now how do I sort on the Total column that MS Access has in the crosstab wizard by default.

 

[vbaInet]

Not sure about wizards, I hardly use them (unless I'm creating forms and reports).

If you open the query in design view you will find a row that's labelled Sort.