Solved

Acccess - Sorting Months in a Crosstab Query Chronologically

Posted on 2003-10-22
12
577 Views
Last Modified: 2011-10-03
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
Comment
Question by:mikskibum
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 3
  • +1
12 Comments
 
LVL 17

Expert Comment

by:walterecook
ID: 9602429
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
 

Author Comment

by:mikskibum
ID: 9602612
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
 
LVL 18

Accepted Solution

by:
bonjour-aut earned 250 total points
ID: 9602676
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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
LVL 17

Expert Comment

by:walterecook
ID: 9602695
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9602827
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9603986
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
 

Author Comment

by:mikskibum
ID: 9606480
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
 
LVL 18

Expert Comment

by:bonjour-aut
ID: 9606553
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
 

Author Comment

by:mikskibum
ID: 9607101
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
 

Author Comment

by:mikskibum
ID: 9607167
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9611495
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
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9611505
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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

It’s the first day of March, the weather is starting to warm up and the excitement of the upcoming St. Patrick’s Day holiday can be felt throughout the world.
Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

738 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question