468,792 Members | 1,730 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,792 developers. It's quick & easy.

Aggregate function error opening a report

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
7 2108
Annalyzer
122 100+
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
kpfunf
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
Annalyzer
122 100+
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
Stewart Ross
2,545 Expert Mod 2GB
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
kpfunf
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
Stewart Ross
2,545 Expert Mod 2GB
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
kpfunf
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.

Similar topics

1 post views Thread by sausage31 | last post: by
3 posts views Thread by Aaron | last post: by
reply views Thread by zhoujie | last post: by
2 posts views Thread by Marin | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.