Lost sort on Crosstab Query 1

[BusMgr]

I have a constructed a crosstab query based on another query which has four fields:
PlotLocal
Mdate
SumofInvoiceAmt
SortDate

MDate is a concatenation of Month & Year, sort date is an integer from 1 to 12 corresponding to the month (1 = Jan, 2 = Feb, etc). The query is sorted, but when I create the crosstab query, I end up with MDate's being sorted alphabetically, so April comes first, Aug second, etc.

Is there some way to construct the crosstab query so that the months are listed in chronological order; Jan, Feb, etc?

My crosstab query code as follows:

TRANSFORM Sum(qryMonthlySales2_Karl.SumOfInvAmt) AS Sales
SELECT qryMonthlySales2_Karl.PlotLocal AS [Country or State]
FROM qryMonthlySales2_Karl
GROUP BY qryMonthlySales2_Karl.PlotLocal
ORDER BY qryMonthlySales2_Karl.PlotLocal
PIVOT qryMonthlySales2_Karl.MDate;

I am looking for table with the location as the vertical axis with month as the horizontal axis and the sum of invoices amount as the data values.

Any help would be appreciated.

Thanks in advance.

BusMgr

 

[lameid]

You have to specify the list in order to get it to work...

TRANSFORM Sum(qryMonthlySales2_Karl.SumOfInvAmt) AS Sales
SELECT qryMonthlySales2_Karl.PlotLocal AS [Country or State]
FROM qryMonthlySales2_Karl
GROUP BY qryMonthlySales2_Karl.PlotLocal
ORDER BY qryMonthlySales2_Karl.PlotLocal
PIVOT qryMonthlySales2_Karl.MDate IN ("Jan","Feb","Mar","Apr","May","Jun","Jul","Aug","Sep","Oct","Nov","Dec");

The IN portion of the query above is the same as the column headings property of the field in the QBE (right click field select properties).