469,946 Members | 2,062 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

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

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

167 100+
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:

Table2 - columns:
Item - (values, 'F' for fuel, 'M' for miles)

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
3 12124
167 100+
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
14,534 Expert Mod 8TB
You have posted this in the Articles section. I am moving it to the Access forum.

May 14 '07 #3
32,233 Expert Mod 16PB
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.

Similar topics

3 posts views Thread by Paul M | last post: by
11 posts views Thread by David Berry | last post: by
reply views Thread by Morten Gulbrandsen | last post: by
3 posts views Thread by Matias Silva | last post: by
19 posts views Thread by sara | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.