sorting months

[sdel_nevo]

Hi Guys

I have a query that works well and produces the results i'm looking for

TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
SELECT Format([EntryDate],"mmm") AS [Month]
FROM tblAccountTransactions
WHERE (((tblAccountTransactions.TransType) Not Like " ") AND (((Year([EntryDate])*12+Month([EntryDate])-1)) Is Not Null) AND ((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.EntryTitle) Not Like "setup") AND ((tblAccountTransactions.TotalPayment)>0))
GROUP BY Format([EntryDate],"mmm")
PIVOT tblAccountTransactions.TransType;

this produces the following results

Month	Money In	Money Out
Feb		£20.00
Mar		£60.00
May	£100.00
Oct	£10.00	£20.00

What I cant seem to get my head around is changing the sort so that the months are shown in the following order

month name	month number
April	4
may	5
June	6
July	7
August	8
September	9
October	10
November	11
December	12
Jan	1
Feb.	2
march	3

any help would be fab

steve

[ArviLaanemets]

TRANSFORM Sum(tblAccountTransactions.TotalPayment) AS [SumOfTotal Payment]
SELECT FORMAT([EntryDate],"yyyymm") AS [MonthNo], Format([EntryDate],"mmm") AS [MonthTxt]
FROM tblAccountTransactions
WHERE (((tblAccountTransactions.TransType) Not Like " ") AND (((Year([EntryDate])*12+Month([EntryDate])-1)) Is Not Null) AND ((tblAccountTransactions.Category) Is Not Null And (tblAccountTransactions.Category) Not Like 86 And (tblAccountTransactions.Category) Not Like 85 And (tblAccountTransactions.Category) Not Like 90) AND ((tblAccountTransactions.EntryTitle) Not Like "setup") AND ((tblAccountTransactions.TotalPayment)>0))
GROUP BY FORMAT([EntryDate],"yyyymm"), Format([EntryDate],"mmm")
PIVOT tblAccountTransactions.TransType;

, and add an ORDER BY FORMAT([EntryDate],"yyyymm") clause (an unfamiliar syntax for me, so you have yourself to find out where to put it).

PS. I changed query column name [Month] to [MonthTxt], as Month is a reserved word - better to be consistent in avoiding using it freely.

[CJ_London]

try sorting on this formula

((Monthnum-3) mod 10)-((Monthnum<=3)*12)

[sdel_nevo]

Hi Guys

Many thaks for the help,

PS. I changed query column name [Month] to [MonthTxt], as Month is a reserved word - better to be consistent in avoiding using it freely.

good catch have done this

hi Ajax

the sort worked brill, many thanks guys

Steve