New to update queries...Operation must use....

[Shawn12]

Hi,

I have read many of the threads relating to this error, and have tried some of the things in those threads with no sucess. Here is the situation:

I have a table called ForecastedSvcLvl1
I have a query called FCST_SL_QUERY_2

I am trying to update 2 fields in the table for various days using the query. The SQL is as follows:

UPDATE ForecastedSvcLvl1 INNER JOIN FCST_SL_QUERY_2 ON (ForecastedSvcLvl1.ForecastDate = FCST_SL_QUERY_2.FORECASTDATE) AND (ForecastedSvcLvl1.Group = FCST_SL_QUERY_2.CTNAME) SET ForecastedSvcLvl1.FcstSvcLvl = [FCST_SL_QUERY_2].[FCST_SL], ForecastedSvcLvl1.LastModified = [FCST_SL_QUERY_2].[Last_Modified];

I am getting the "Operation must use an updateable query" error. Any help would be greatly appreciated. Thanks.

 

[PHV]

What is the SQL code of FCST_SL_QUERY_2 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shawn12]

This is the SQL for the qry:

SELECT FCST_SL_QUERY_2.FORECASTDATE, FCST_SL_QUERY_2.CTNAME, FCST_SL_QUERY_2.ctID, FCST_SL_QUERY_2.f1, FCST_SL_QUERY_2.f2, FCST_SL_QUERY_2.FCST_SL, FCST_SL_QUERY_2.Last_Modified
FROM FCST_SL_QUERY_2

 

[PHV]

So, you have a recursive select query ????

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[Shawn12]

This is the originating query

SELECT FCST_SL_QUERY_1.ctID, FCST_SL_QUERY_1.FORECASTDATE, FCST_SL_QUERY_1.ctName, Sum(FCST_SL_QUERY_1.fcstContactsReceived) AS f1, Sum(FCST_SL_QUERY_1.WTD_CT_NO) AS f2, Round([f2]/[f1],2)/100 AS FCST_SL
FROM FCST_SL_QUERY_1
GROUP BY FCST_SL_QUERY_1.ctID, FCST_SL_QUERY_1.FORECASTDATE, FCST_SL_QUERY_1.ctName;

 

[dhookom]

GROUP BY queries are not updateable. Any query in Access that contains a non-updateable query will also be non-updateable.

Access quite often requires using the much less efficient DSum() or DCount() or similar in place of GROUP BY. You might also be able to create your own user-defined function to return the sums.

Duane
Hook'D on Access
MS Access MVP

 

[Shawn12]

Thanks I will try that.