How Do You Order an Aggregate Function

[A Boy Named Joe]

I'm attempting to calculate (in decending order) the cost of inventory of
spare parts which were used last year. I'm doing this using a report which
groups by the part number; however, when I attempt to then sort by the total
cost of those issues and not by the individual lines which make up the
issues, I get the error message, "Cannot have aggregate function in ORDER BY
clause <clause>. (Error 3097)"

Is there a way around this difficulty?

Thanks to anyone who can offer some advise.

 

[Duane Hookom]

Joe,
You should be able to create a totals query that calculates your cost of
part number for the year. Then add this query to your report's record source
and join the part number fields. Add the total to your query grid so you can
use the value in your sorting and grouping dialog.

 

[A Boy Named Joe]

Good morning Duane,

Thanks for your tip. Since I don't use Access that much, it took me a while
to figure out exactly what you were saying; however, once I did - it worked
like a champ. After looking at the board I noticed that you spend a lot of
time answering questions, so I really appreciate your efforts to respond to
my individual concern.

Thanks again and have a grand day,

Joe

 

[ToddStewart]

Sort Report on Aggregate Function

I have the same problem, but your solution isn't working for me. The query my report is based on contains a number of parameters. The query that I create to perform the SUM doesn't recognize those parameters so it calculates the sum based upon the entire population rather than just the subset from my top-level query. Please advise.