Show Chart Legend in Non Alphabetic Order

[sxschech]

I am running a chart report off of a cross tab query. Unfortunately, the legend shows the description in a different order than the user would like. I tried putting in a sort on the cross tab and received an error about it not being a part of the aggregate expression. I ended up writing a select query off of the crosstab in order to get the desired result. Am I missing something in how to do it directly off of the cross tab rather than having to create yet another query?

Legend as displayed on chart from crosstab

[tt]
2008 Accepted 2008 Applied 2008 Deposit
2009 Accepted 2009 Applied 2009 Deposit
[/tt]

Legend the way requestor wants it displayed

[tt]
2008 Applied 2008 Accepted 2008 Deposit
2009 Applied 2009 Accepted 2009 Deposit
[/tt]

Original SQL

TRANSFORM Sum(tblChart_UG.TOT) AS TOT
SELECT [Term] & " " & [DisplayStatus] AS WeekNo
FROM tblChart_UG, tblWeeks
WHERE (((tblChart_UG.Term)=Val([CurrentTerm]) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-1 & Right([CurrentTerm],2)) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-2 & Right([CurrentTerm],2))) AND ((Right([tblChart_UG].[WeekNo],2))<99))
GROUP BY [Term] & " " & [DisplayStatus]
PIVOT Right([tblChart_UG].[WeekNo],2);

Modified to add a sort field

TRANSFORM Sum(tblChart_UG.TOT) AS TOT
SELECT [Term] & " " & [Status] AS WeekNoSort, [Term] & " " & [DisplayStatus] AS WeekNo
FROM tblChart_UG, tblWeeks
WHERE (((tblChart_UG.Term)=Val([CurrentTerm]) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-1 & Right([CurrentTerm],2)) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-2 & Right([CurrentTerm],2))) AND ((Right([tblChart_UG].[WeekNo],2))<99))
GROUP BY [Term] & " " & [Status], [Term] & " " & [DisplayStatus]
PIVOT Right([tblChart_UG].[WeekNo],2);

By adding the additional field for sorting purposes, wasn't sure how to tell the chart to use the "Weekno" field rather than the "WeeknoSort" field.

New added query for final display on chart

SELECT tblChart_Crosstab_UG.WeekNo, tblChart_Crosstab_UG.[00], tblChart_Crosstab_UG.[01], tblChart_Crosstab_UG.[02], tblChart_Crosstab_UG.[03], tblChart_Crosstab_UG.[04], tblChart_Crosstab_UG.[05], tblChart_Crosstab_UG.[06], tblChart_Crosstab_UG.[07], tblChart_Crosstab_UG.[08], tblChart_Crosstab_UG.[09], tblChart_Crosstab_UG.[10], tblChart_Crosstab_UG.[11], tblChart_Crosstab_UG.[12], tblChart_Crosstab_UG.[13], tblChart_Crosstab_UG.[14], tblChart_Crosstab_UG.[15], tblChart_Crosstab_UG.[16], tblChart_Crosstab_UG.[17], tblChart_Crosstab_UG.[18], tblChart_Crosstab_UG.[19], tblChart_Crosstab_UG.[20], tblChart_Crosstab_UG.[21]
FROM tblChart_Crosstab_UG;

 

[dhookom]

I don't see any "ORDER BY" clause in any of your queries. If you want to see something in a particular order, you must provide the order. You can ORDER BY a field that isn't displayed in the chart.

Duane
Hook'D on Access
MS Access MVP

 

[sxschech]

Sorry if wasn't clear, I did mention I tried to do an order by, but received an error message "You tried to execute a query that does not include the specified expression 'Term & " " & Status' as part of an aggregate function.

TRANSFORM Sum(tblChart_UG.TOT) AS TOT
SELECT [Term] & " " & [DisplayStatus] AS WeekNo
FROM tblChart_UG, tblWeeks
WHERE (((tblChart_UG.Term)=Val([CurrentTerm]) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-1 & Right([CurrentTerm],2)) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-2 & Right([CurrentTerm],2))) AND ((Right([tblChart_UG].[WeekNo],2))<99))
GROUP BY [Term] & " " & [DisplayStatus]
ORDER BY [Term] & " " & [Status]
PIVOT Right([tblChart_UG].[WeekNo],2);

In revisting it, looks like I figured out how to fix the problem. I went into the SQL view rather than design view and entered the order by statement. Got the error. Then added the same statement from the order by into the group by, ahead of what was already in the group by. I went back to design view and determined that the problem was that in order to do it in design view, you have to use GROUP by rather than WHERE and then leave the CrossTab cell blank since I don't want this item to show up on the chart report. The reason I had used WHERE was because that was in design view seemed to be the only option to cause Access not to display a field on the output. I didn't realize that I could leave the Crosstab row blank, I thought it had to have one of the choices of Row Heading, Col Heading, Value, etc.

Here is the corrected SQL:

TRANSFORM Sum(tblChart_UG.TOT) AS TOT
SELECT [Term] & " " & [DisplayStatus] AS WeekNo
FROM tblChart_UG, tblWeeks
WHERE (((tblChart_UG.Term)=Val([CurrentTerm]) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-1 & Right([CurrentTerm],2)) Or (tblChart_UG.Term)=Val(Left([CurrentTerm],4)-2 & Right([CurrentTerm],2))) AND ((Right([tblChart_UG].[WeekNo],2))<99))
GROUP BY [Term] & " " & [Status], [Term] & " " & [DisplayStatus]
ORDER BY [Term] & " " & [Status]
PIVOT Right([tblChart_UG].[WeekNo],2);