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
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