Crosstab Query Issue: Sorting columns in proper month order

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;
Who is Participating?

PIVOT MonthName(tblMonths.[Month],True)
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.
cnv3410Author Commented:
That works great!!!!  I have learned yet another thing about Access that will be useful in the future.  Thanks!!!!
Thank you!  Glad I could help.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.