Solved

Acccess - Sorting Months in a Crosstab Query Chronologically

Posted on 2003-10-22
12
563 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
  • 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
 
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
Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

 

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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

706 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now