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

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,

Jun 25, 2012
Reaction score
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.

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question