• Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

ODBC Microsoft Access Driver aggregate error

 
Dave Wds
Greenhorn
Posts: 3
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
I am having trouble with the Microsoft Access driver. I have 2 queries, which have almost the same select and groupings, however one will process and the other will not. Does anyone know why one query would throw an aggregate error and the other would not?
Select Statement that doesn�t throw the error:
SELECT tbl_ISTS_Orders.[PERSON IN CHARGE], Sum([tbl_ISTS-RCC_Data].Value) AS SumOfValue, Sum(Budget_2004.[Monthly Budget]) AS [SumOfMonthly Budget], tbl_ISTS_Orders.ORDER, [tbl_ISTS-RCC_Data].[Cost Ctr], Sum(Dashboard_Forecast.[Forecast Amount]) AS [SumOfForecast Amount]
FROM Dashboard_Forecast INNER JOIN (([tbl_ISTS-RCC_Data] INNER JOIN Budget_2004 ON ([tbl_ISTS-RCC_Data].Period = Budget_2004.Period) AND ([tbl_ISTS-RCC_Data].Year = Budget_2004.Year)) INNER JOIN tbl_ISTS_Orders ON ([tbl_ISTS-RCC_Data].ParOrd = tbl_ISTS_Orders.ORDER) AND (Budget_2004.RCVRCC = tbl_ISTS_Orders.RCVRCC)) ON Dashboard_Forecast.PCC = [tbl_ISTS-RCC_Data].[Cost Ctr]
WHERE (((Budget_2004.Types)='C'))
GROUP BY tbl_ISTS_Orders.[PERSON IN CHARGE], tbl_ISTS_Orders.ORDER, [tbl_ISTS-RCC_Data].[Cost Ctr];
Select Statement that throws the error:
SELECT Sum(tbl_ISTS_Device_Fees.Value) AS SumOfValue, Sum(Budget_DeviceFee_Collected.Plan_Amount) AS SumOfPlan_Amount, Sum(Budget_DeviceFee_Costs.Plan_Amount) AS SumOfPlan_Amount1, tbl_ISTS_Device_Fees.CostCtr, tbl_ISTS_Orders.ORDER, tbl_ISTS_Orders.[PERSON IN CHARGE], Sum(Dashboard_Forecast.[Forecast Amount]) AS [SumOfForecast Amount] FROM Dashboard_Forecast INNER JOIN (((tbl_ISTS_Device_Fees INNER JOIN Budget_DeviceFee_Collected ON (tbl_ISTS_Device_Fees.CostCtr = Budget_DeviceFee_Collected.CC) AND (tbl_ISTS_Device_Fees.Period = Budget_DeviceFee_Collected.Period)) INNER JOIN Budget_DeviceFee_Costs ON (tbl_ISTS_Device_Fees.CostCtr = Budget_DeviceFee_Costs.CC) AND (tbl_ISTS_Device_Fees.Period = Budget_DeviceFee_Costs.Period)) INNER JOIN tbl_ISTS_Orders ON tbl_ISTS_Device_Fees.ParOrder = tbl_ISTS_Orders.ORDER) ON Dashboard_Forecast.PCC = tbl_ISTS_Device_Fees.CostCtr GROUP BY tbl_ISTS_Device_Fees.CostCtr, tbl_ISTS_Orders.ORDER, tbl_ISTS_Orders.[PERSON IN CHARGE];
Error:
java.sql.SQLException: [Microsoft][ODBC Microsoft Access Driver] You tried to execute a query that does not include the specified expression 'COSTCTR' as part of an aggregate function.

Thanks
Dave
 
Amy Phillips
Ranch Hand
Posts: 280
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi Dave,
I'm not entirely sure but could it be something to do with the fact that in the first query you have [COST CTR] and in the second COSTCTR without the space?
Amy
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic