Sort Report By Expression Not Working? (1 Viewer)

Hi Experts,

I have a report based on the below query. This pulls my records just fine into a report.

SELECT Avg(tblScoring.NScore) AS AvgOfNScore, Avg(tblScoring.MScore) AS AvgOfMScore, Avg(tblScoring.IScore) AS AvgOfIScore, tblScoring.Nominee, tblScoring.AwardType, tblScoring.AwardYear
FROM tblScoring
GROUP BY tblScoring.Nominee, tblScoring.AwardType, tblScoring.AwardYear
HAVING (((tblScoring.AwardYear)=DatePart("yyyy",Date())))

However, I am trying to sort my report on the following expression: =Sum([AvgOfNScore]+[AvgOfMScore]+[AvgOfIScore])

For some reason I cannot determine, the report will not sort correctly.

Any thoughts as to how I can accomplish this? I cannot add the calculation to my query because it is an aggregate.

Thank you in advance for any assistance you can provide!

Your sort expression doesn't make sense. You have individual records in your report, but somehow you want to aggregate them and then sort them? I would remove the 'SUM' from your sort expression.

Failing that, I would add a new field to your query:

SortOrder: Avg(NScore) + Avg(MScore) + Avg(IScore)

Then under that change the Group By to Expression. Or possibly:

SortOrder: AVG(NScore + MScore + IScore)

Thank you, but neither of these solutions worked. I get the following error message: "Cannot have an aggregate function in GROUP BY clause"

Beg your pardon, when I change to EXPRESSION instead of GROUP BY I no longer get the error message; however, the sorting is still not working

Can you post a sample of your database?

I was able to figure this out - your solution of adding the SortOrder field to my query along with moving the "sort by" ABOVE the grouping level on my report did the trick. Thanks