Crosstab Query Issue: Sorting columns in proper month order
Posted on 2006-05-02
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
GROUP BY [tblMilestones-MonthlyUpdates].MBO_ID, tblOperatingCommittee.OC_NAME, qryPrimaryExecutionOwners.EXEC_NAME, tblStrategicInitiatives.SI_NAME, tblMBO_Defintion.MBO_NAME, [qryMBOScorecard-CurrentMonthUpdate].PERF