Link to home
Start Free TrialLog in
Avatar of mikskibum
mikskibum

asked on

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*"



Avatar of walterecook
walterecook
Flag of United States of America image

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
Avatar of mikskibum
mikskibum

ASKER

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
ASKER CERTIFIED SOLUTION
Avatar of bonjour-aut
bonjour-aut
Flag of Austria image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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
If you can live with that:

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

it will work - try

regards, franz
Avatar of Benjamin Lu
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
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!
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
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
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');
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
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