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

Error - tried to execute query-does not include expression in aggregate function

100+
P: 167
I am pulling my hair out on this! Seems like it should be easy, I just can not get it to work like I want. I know I am doing something wrong, so I hope someone can be so kind to guide me!!!

I have 2 tables:

Table1 - columns:
Period
FuelType

Table2 - columns:
Period
FuelType
Miles
Gallons
Item - (values, 'F' for fuel, 'M' for miles)
AdjustmentAmount

I am joining the 2 tables on Period & FuelType columns. For each Period within a FuelType I want to sum the miles & gallons.(this is working correctly). I also want to sum the AdjustmentAmount column so I have created an expression for that. The problem is if I put teh aggregate function 'Sum' on this expression instead of 'group by' I get the error "Trying to execute a query that deos not include the specified expression as part of an aggregate function." The expression I created looks at the value in column 'Item' and sums based on the value either 'F' or 'M'. My query runs as is, however, when I change "(IIf([Item]='M',[Adjustment],Null)) AS MilesAdj" to "SUM((IIf([Item]='M',[Adjustment],Null)) AS MilesAdj)", I get the error. Here is my sql statement:
May 14 '07 #1
Share this Question
Share on Google+
3 Replies


100+
P: 167
On my previous post, not enough room to include my sql statement, here it is:

Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Table1.Period, Table1.FuelType, Sum(ITable1.TaxableMiles) AS RMiles, Sum(Table1.PaidGallons) AS RFuel, (IIf([Item]='M',[Adjustment],Null)) AS MilesAdj, IIf(Item='F',Adjustment,Null) AS GalsAdj
  2. FROM Table1 INNER JOIN Table2 ON (Table1.Period = Table2.Period) AND (Table1.FuelType = Table2.FuelType)
  3. GROUP BY Table1.Period, Table1.FuelType, Table2.Item, Table2.Adjustment
May 14 '07 #2

MMcCarthy
Expert Mod 10K+
P: 14,534
You have posted this in the Articles section. I am moving it to the Access forum.

ADMIN
May 14 '07 #3

NeoPa
Expert Mod 15k+
P: 31,347
Expand|Select|Wrap|Line Numbers
  1. SELECT DISTINCTROW Table1.Period,
  2.                    Table1.FuelType,
  3.                    Sum(ITable1.TaxableMiles) AS RMiles,
  4.                    Sum(Table1.PaidGallons) AS RFuel,
  5.                    (IIf([Item]='M',[Adjustment],Null)) AS MilesAdj,
  6.                    IIf(Item='F',Adjustment,Null) AS GalsAdj
  7. FROM Table1 INNER JOIN Table2
  8.   ON (Table1.Period = Table2.Period)
  9.  AND (Table1.FuelType = Table2.FuelType)
  10. GROUP BY Table1.Period,
  11.          Table1.FuelType,
  12.          Table2.Item,
  13.          Table2.Adjustment
Clearly you typed this in rather than using Copy/Paste.
That is the cause of so much wasted time.

I've re-arranged the SQL so that it's in code tags and isn't a large mess of gobledegook. Now it is clear that the third line (in my version) has an extraneous 'I' before the table name.

Some things to start with :
  1. Change ITable1 to Table1.
  2. Lose DISTINCTROW predicate. Very good to know about, but inappropriate in a GROUP BY query.
  3. Think about what you should actually be GROUPing by. Remember, the Sum() results are determined by this.
May 14 '07 #4

Post your reply

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