File APIs for Java Developers
Manipulate DOC, XLS, PPT, PDF and many others from your application.
The moose likes JDBC and Relational Databases and the fly likes ODBC Microsoft Access Driver aggregate error Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login

Win a copy of REST with Spring (video course) this week in the Spring forum!
JavaRanch » Java Forums » Databases » JDBC and Relational Databases
Bookmark "ODBC Microsoft Access Driver aggregate error" Watch "ODBC Microsoft Access Driver aggregate error" New topic

ODBC Microsoft Access Driver aggregate error

Dave Wds

Joined: Mar 09, 2004
Posts: 3
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];
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.

Amy Phillips
Ranch Hand

Joined: Apr 02, 2003
Posts: 280
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?
I agree. Here's the link:
subject: ODBC Microsoft Access Driver aggregate error
It's not a secret anymore!