How Do You Order an Aggregate Function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

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.
 
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.
 
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
 
Back
Top