Acccess - Sorting Months in a Crosstab Query Chronologically

I have a crosstab query that I need sorted by month (jan-feb-march-april-etc).  I am using the query below to assign the month number a name.  The problem is that when I do this the query sorts the Months as a column heading alphabetically.   I can make the query work by using the month number, but I would really like to have the month name.  Let me know if this isn't enough info, or if it doesn't make sense.  I appreciate the help.

The Crosstab query looks like this (it is querying off of another very large query):

TRANSFORM Sum([TableName].CountOfId) AS SumOfCountOfId
SELECT [TableName].IdName
FROM [TableName]
GROUP BY [TableName].IdName
ORDER BY MonthName([monthId])
PIVOT MonthName([monthId]);


The Crosstab query is querying from the following query (there may be a better way of grouping this data by month as well):

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="1") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="2") AND (([TableName].IdName) Like "*System Admin*"

UNION


SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="3") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="4") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="5") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="6") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="7") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="8") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="9") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="10") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="11") AND (([TableName].IdName) Like "*System Admin*"

UNION

SELECT [TableName].IdName, [TableName].Month, [TableName].CountOfId
FROM [TableName]
WHERE ((([TableName].MonthId)="12") AND (([TableName].IdName) Like "*System Admin*"



mikskibumAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

walterecookCommented:
How about using month number AND abbreviation:
 Format([month],"mm") & " - " & Format([month],"mmm") instead of
[TableName].Month

On another note, why are you doing 12 union queries?  

Good luck
Walt
0
mikskibumAuthor Commented:
Walt,  The 12 Union queries is obviously not the most efficient way of doing things, and that will be next on my list.  I can input the formula you suggested instead of [TableName].Month in each of the 12 queries.  The problem is that the output is putting the months in the following format:  Jan-Jan, Feb-Feb, Mar-Mar, Apr-Apr, etc.  I still cannot sort the months chronologically.  I really appreciate your help.

Thanks
0
bonjour-autCommented:
you can do a custom order by:

PIVOT MonthName([monthId]) IN ['jan','feb','mar',........];

be carefull - the names are case-sensitive
this method has also the advantage, that no column is left out, when there are no values in the whole crosstab for it.

regards, franz
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

walterecookCommented:
Are you sure in the first clause you have only 2 m's and not 3 m's

Feb-Feb, Mar-Mar, Apr-Apr would imply that you have "mmm" in both places.

Walt
0
bonjour-autCommented:
If you can live with that:

PIVOT MonthName([monthId]) IN ['Jan-Jan', 'Feb-Feb', 'Mar-Mar', 'Apr-Apr',....];

it will work - try

regards, franz
0
Benjamin_LukCommented:
Please change to :
........
TRANSFORM Sum([TableName].CountOfId) AS SumOfCountOfId
SELECT [TableName].IdName
FROM [TableName]
GROUP BY [TableName].IdName
PIVOT [monthId] In (1,2,3,4,5,6,7,8,9,10,11,12);

Or :
.................
PIVOT Format(([MonthID] & "/01/2000"),"mmm");

As you MonthID is Number,

Hope it will work

Regards

Ben
0
mikskibumAuthor Commented:
Walter, I did have only two ('mm') in the first statement and three ('mmm') in the second statement.  I tried changing them around (to just one, or more) and I still could not get it to sort chronologically.  Do you have any other ideas?

Franz, I like your first suggestion because it gives the month name and orders the months correctly, but for some strange reason it only populates the month of May.  I don't know if you have any idea what could be causing this, but it is very strange.

Ben, Your first suggestion works, but does not give me a month name.  I could live with that, but ideally I would like to have the month name in there.  Your second suggestion also works, but does  not order the months chronologically.  If you have an idea on how to get either of those it would be greatly appreciated.

Thanks All!
0
bonjour-autCommented:
the IN clause is very case sensitive

having only populated may would indicate, that all other month-strings have trailing spaces or other small differences in the string

so best thing is, to do a crosstab without the IN clause, export it to excel and coppy the column headings as they are fron the excel-field into the IN.clause between ......,'stringcopyfromexcel',..........

regards franz
0
mikskibumAuthor Commented:
Franz, That would work, but my problem is that I am trying to automate graphs. So I want the data to be pre-formatted and then I am runnning some VBA code to turn them into excel graphs.  So I am really hoping there is some solution where I can get the data in the correct format before exporting it.  I will double-check the spacing issues.

Thanks, Mike
0
mikskibumAuthor Commented:
Franz, I was able to get it working using your first suggestion.  Your last comment made me realize that the whole month name had to be in the IN clause.  I used the following query to format the data correctly.  Thanks to everyone for your help.


TRANSFORM Sum([TableName].CountOfId) AS SumOfCountOfId
SELECT [TableName].IdName
FROM [TableName]
GROUP BY [TableName].IdName
PIVOT MonthName([MonthId]) In ('january', 'february', 'march', 'april', 'may', 'june', 'july', 'august', 'september', 'october', 'november', 'december');
0
Benjamin_LukCommented:
Mike,
For the Second method , please try this, I forgot to add the month name :

PIVOT Format(([MonthID] & "/01/2000"),"mmm") i n('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Octr', 'Nov', 'Dec');

Regards

Ben
0
Benjamin_LukCommented:
Mike,
Autual this one should be best, as I change the date 01 to 26:

PIVOT Format(([MonthID] & "/26/2000"),"mmm") i n('Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun', 'Jul', 'Aug', 'Sep', 'Octr', 'Nov', 'Dec');

Regards

Ben
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.