By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,493 Members | 3,101 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,493 IT Pros & Developers. It's quick & easy.

Aggregate function error opening a report

P: 78
Getting the following error opening a report, cannot figure out the cause:

"You tried to execute a query that does not include the specified expression 'RQ_FuelQuoteReportHistory.Vendor' as part of an aggregate function"
Not sure why this comes up as Vendor is not part of any aggregate function (that I know of!). The query runs fine. The error occurs when opening the report. Vendor is not used in sorting or grouping. I tried deleting the text box displaying vendor and that didn't work. There is no summing, etc. in the query. Any suggestions on what to check?
Jun 24 '08 #1
Share this Question
Share on Google+
7 Replies


100+
P: 122
I get this one all the time! It's caused by not including the field in a GROUP BY statement in your SQL. If it's not included in the aggregate function, it has to be included in the GROUP BY.
Jun 24 '08 #2

P: 78
I tried that, and no luck. I've dealt with this error before as well in queries, but the error is only occuring when I run the report, not when I run the actual query.
Jun 24 '08 #3

100+
P: 122
I haven't been able to reproduce the error and I'm not by any means an Access Expert. I'm sorry I couldn't help. Hopefully, one of the real experts will jump in here soon and help you out.
Jun 25 '08 #4

Expert Mod 2.5K+
P: 2,545
Hi. I think we'll need to see the SQL for the report's recordsource query to be able to advise further...

-Stewart
Jun 25 '08 #5

P: 78
Expand|Select|Wrap|Line Numbers
  1. SELECT M_Locations.TrainingCenterID, [Master Fuel History].FuelID, [Master Fuel History].QuoteDate, [Master Fuel History].FuelType, [Master Fuel History].VendorID, [Master Fuel History].Vendor, [Master Fuel History].Price, [Master Fuel History].AvGasDiscount, [Master Fuel History].AvGasMarkup, [Master Fuel History].AirportID, [Master Fuel History].[Airport Name]
  2. FROM [Master Fuel History] INNER JOIN M_Locations ON [Master Fuel History].AirportID = M_Locations.AirportID
  3. GROUP BY M_Locations.TrainingCenterID, [Master Fuel History].FuelID, [Master Fuel History].QuoteDate, [Master Fuel History].FuelType, [Master Fuel History].VendorID, [Master Fuel History].Vendor, [Master Fuel History].Price, [Master Fuel History].AvGasDiscount, [Master Fuel History].AvGasMarkup, [Master Fuel History].AirportID, [Master Fuel History].[Airport Name];
I get the same error with or without grouping.
Jun 25 '08 #6

Expert Mod 2.5K+
P: 2,545
OK, there is no apparent issue with the SQL recordsource. if you are able to run the query itself OK but running the report generates an error It suggests there is something in your report - a subtotal perhaps, or something within the report's grouping and ordering - which is causing the error. Hard to advise at this stage without seeing the report.

I note that the field as named in post 1 is sourced from a table RQ_FuelQuoteReportHistory that does not appear in the recordsource query. Is there a calculated control on the report, or a reference to such a control in the grouping and so on, which is incorrect somewhere?

-Stewart
Jun 25 '08 #7

P: 78
Somehow, the Order By had the RQ...Vendor in it. I have no idea how that ended up like that, but thanks for the heads up! It didn't even register in my brain that the two "Vendor" fields were different.
Jun 26 '08 #8

Post your reply

Sign in to post your reply or Sign up for a free account.