Help on sorting by month (logical, not alpha)

[Bronte1226]

I have a crosstab query that I need to sort the month columns on the logical month order instead of by the alpha sorting that it defaults to since it is a text field. How do I do this?

Here is the query:

TRANSFORM Sum(ActivityLog.CUST_CHANG) AS SumOfCUST_CHANG
SELECT ActivityLog.NAME, Avg(ActivityLog.CUST_CHANG) AS [Total Of CUST_CHANG]
FROM ActivityLog
GROUP BY ActivityLog.NAME
ORDER BY ActivityLog.Month
PIVOT ActivityLog.Month;

 

[PHV]

You may try something like this:
PIVOT ActivityLog.Month IN ('Jan','Feb',...,'Dec');

ie force the column order.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[dhookom]

BTW: Month and Name are not good choices for field names since they are a function and a property. In addition, you should be storing a numeric month rather than text as this will ease your querying and reporting.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]