If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
How Do You Order an Aggregate Function
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 |
#2
|
|||
|
|||
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. -- Duane Hookom MS Access MVP -- "A Boy Named Joe" wrote in message ... 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 |
#3
|
|||
|
|||
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 "Duane Hookom" wrote: 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. -- Duane Hookom MS Access MVP -- "A Boy Named Joe" wrote in message ... 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 |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
SPELL CURRENCY FUNCTION | Rajesh | Using Forms | 1 | October 1st, 2005 11:04 PM |
Automatically up date time in a cell | Mark | General Discussion | 5 | May 12th, 2005 12:26 AM |
DISTINCT option of an aggregate function in Access 2003 | [email protected] | Running & Setting Up Queries | 1 | May 5th, 2005 04:46 PM |
Sql aggregate function | SillySally | Running & Setting Up Queries | 5 | March 25th, 2005 10:21 PM |
Wrong sort order of report's details section | dsjohn_242 | Setting Up & Running Reports | 6 | July 12th, 2004 10:21 PM |