[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 753
  • Last Modified:

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.

SQL:

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;
0
cnv3410
Asked:
cnv3410
  • 3
1 Solution
 
GRayLCommented:
try:

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

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now