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

[ncsthbell]

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:

[ncsthbell]

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

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
FROM Table1 INNER JOIN Table2 ON (Table1.Period = Table2.Period) AND (Table1.FuelType = Table2.FuelType)
GROUP BY Table1.Period, Table1.FuelType, Table2.Item, Table2.Adjustment

[MMcCarthy]

You have posted this in the Articles section. I am moving it to the Access forum.

ADMIN

[NeoPa]

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
FROM Table1 INNER JOIN Table2
  ON (Table1.Period = Table2.Period)
 AND (Table1.FuelType = Table2.FuelType)
GROUP BY Table1.Period,
         Table1.FuelType,
         Table2.Item,
         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.