Crosstab Query Issue: Sorting columns in proper month order

Posted on 2006-05-02
Last Modified: 2008-06-20
I have a crosstab query (SQL below) that shows a status score by month for various objectives.  I would like the columns to be in the proper month order and I have tried just about everything including things I found on this site.  The data table with the score has the month as a number and I want to convert it to "Jan", "Feb", "Mar", etc.  I have a translation table for the months (tblMonths).   I don't want to use the IN clause as I only want the months with data to show as I have a nice dynamic report driven by this query and I think it is cleaner to have the months appear as data is entered.    I have tried forcing an artificial date and formating it, but I either end up with the same result(months out of order) or an error message. I'm also open doing it a the time the report is generated if that is easier(I have VBA experience).  Thanks.


TRANSFORM Sum([tblMilestones-MonthlyUpdates].SCORE) AS SumOfSCORE
SELECT [tblMilestones-MonthlyUpdates].MBO_ID, tblOperatingCommittee.OC_NAME, qryPrimaryExecutionOwners.EXEC_NAME, tblStrategicInitiatives.SI_NAME, tblMBO_Defintion.MBO_NAME, [qryMBOScorecard-CurrentMonthUpdate].PERF
FROM ((((([tblMilestones-MonthlyUpdates] INNER JOIN tblMBO_Defintion ON [tblMilestones-MonthlyUpdates].MBO_ID = tblMBO_Defintion.MBO_ID) INNER JOIN tblOperatingCommittee ON tblMBO_Defintion.OC_ID = tblOperatingCommittee.OC_ID) INNER JOIN qryPrimaryExecutionOwners ON tblMBO_Defintion.MBO_ID = qryPrimaryExecutionOwners.MBO_ID) INNER JOIN tblStrategicInitiatives ON tblMBO_Defintion.SI_ID = tblStrategicInitiatives.SI_ID) INNER JOIN [qryMBOScorecard-CurrentMonthUpdate] ON tblMBO_Defintion.MBO_ID = [qryMBOScorecard-CurrentMonthUpdate].MBO_ID) INNER JOIN tblMonths ON [tblMilestones-MonthlyUpdates].MONTH = tblMonths.Month
WHERE (((tblMBO_Defintion.Delete)=No))
GROUP BY [tblMilestones-MonthlyUpdates].MBO_ID, tblOperatingCommittee.OC_NAME, qryPrimaryExecutionOwners.EXEC_NAME, tblStrategicInitiatives.SI_NAME, tblMBO_Defintion.MBO_NAME, [qryMBOScorecard-CurrentMonthUpdate].PERF
PIVOT tblMonths.Month;
Question by:cnv3410
    LVL 44

    Accepted Solution


    PIVOT MonthName(tblMonths.[Month],True)
    LVL 44

    Expert Comment

    When you must use an Access reserved word (month) as a name, be sure to wrap it in brackets to avoid the query throwing an error.

    Author Comment

    That works great!!!!  I have learned yet another thing about Access that will be useful in the future.  Thanks!!!!
    LVL 44

    Expert Comment

    Thank you!  Glad I could help.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    IT, Stop Being Called Into Every Meeting

    Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

    This article is a continuation or rather an extension from Cascading Combos ( and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
    In the article entitled Working with Objects – Part 1 (, you learned the basics of working with objects, properties, methods, and events. In Work…
    Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
    With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

    779 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

    16 Experts available now in Live!

    Get 1:1 Help Now