Forums Register Login

ODBC Microsoft Access Driver aggregate error

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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
Thank you my well lotioned goddess! Here, have my favorite tiny ad!
a bit of art, as a gift, that will fit in a stocking
https://gardener-gift.com


reply
reply
This thread has been viewed 1651 times.
Similar Threads
Error in insert into
problem with "group by"
Sum function sql statement
how to execute complex queries
select works in db, but not across jdbc...
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 29, 2024 07:27:37.