Arrange the months in Asending

[Guest]

I made a query "YrSlQ" which is based on another query (which filters the
records of one whole year). In my query I have below 2 fields only :

Format([InvDt],"mmmm") - grouped
InvValue - Sum

The result of the query is ok, It groups the months on one field like
Jan....to ..Dec. 12 Rows and on the second field it totals the value.
Perfect.

My problem is that I am unable to sort it month wise, If I put the sort
Asending, It takes April , Augst ..means from A-Z. If I put desending it
starts from September,October...means from Z-A.

BUT I want to sort my rows as January - February - March ...to December.

Please advise.

Regards.

Irshad.

 

[Tom Lake]

My problem is that I am unable to sort it month wise, If I put the sort

Asending, It takes April , Augst ..means from A-Z. If I put desending it
starts from September,October...means from Z-A.

BUT I want to sort my rows as January - February - March ...to December.

Create a hidden column that is

MonthNo: Month([InvDt])

and sort THAT into Ascending order.

Tom Lake

 

[Vincent Johns]

My problem is that I am unable to sort it month wise, If I put the sort
Asending, It takes April , Augst ..means from A-Z. If I put desending it
starts from September,October...means from Z-A.

BUT I want to sort my rows as January - February - March ...to December.

Create a hidden column that is

MonthNo: Month([InvDt])

and sort THAT into Ascending order.

Tom Lake

For example, given these values:

InvDt InvValue
--------- ----------
6/10/2005 $5.84
1/16/2005 $2.78
4/2/2005 $299,282.73
6/18/2005 $88.88

you can define Query [YrSlQ] as ...

SELECT Format([InvDt],"mmmm") AS [Month],
Sum(Table1.InvValue) AS SumOfInvValue
FROM Table1
GROUP BY Format([InvDt],"mmmm"), Month([InvDt])
ORDER BY Month([InvDt]);

producing this:

Month SumOfInvValue
-------- -------------
January $2.78
April $299,282.73
June $94.72

-- Vincent Johns <[email protected]>
Please feel free to quote anything I say here.