• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 600
  • Last Modified:

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



0
mikskibum
Asked:
mikskibum
  • 4
  • 3
  • 3
  • +1
1 Solution
 
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 4
  • 3
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now