Must use updateable query message

  • Thread starter Thread starter Mark
  • Start date Start date
M

Mark

I get an Operation must use updateable query when I run
the following query when I run the following UPDATE
query. I've checked the the select statement only
returns 1 APPL_CODE per client, so that's not the problem

UPDATE DISTINCTROW dbo_tMatched_Loans_and_Submissions AS
MLS INNER JOIN [SELECT TL.ClientID, TL.APPL_CODE as Source
FROM dbo_tClient_Tools AS TL
WHERE TL.APPL_CODE IN ('PBAD','CDSE') AND
TL.ClientID<>'49236' GROUP BY TL.ClientID, TL.APPL_CODE].
AS CT ON MLS.Eval_Client_ID = CT.ClientID SET MLS.Source
= [CT].[Source]
WHERE (((MLS.Source)='DECSW'));

Any help would be appreciated.

Thanks

Mark
 
I get an Operation must use updateable query when I run
the following query when I run the following UPDATE
query. I've checked the the select statement only
returns 1 APPL_CODE per client, so that's not the problem

UPDATE DISTINCTROW dbo_tMatched_Loans_and_Submissions AS
MLS INNER JOIN [SELECT TL.ClientID, TL.APPL_CODE as Source
FROM dbo_tClient_Tools AS TL
WHERE TL.APPL_CODE IN ('PBAD','CDSE') AND
TL.ClientID<>'49236' GROUP BY TL.ClientID, TL.APPL_CODE].
AS CT ON MLS.Eval_Client_ID = CT.ClientID SET MLS.Source
= [CT].[Source]
WHERE (((MLS.Source)='DECSW'));

Any help would be appreciated.
Click to expand...

The GROUP BY portion of the query is the problem. No GROUP BY query is
ever updateable in Access. Since you're not using any Sum or Count
operators, I'm not sure why you want the GROUP BY anyway! Try
unchecking the Totals icon, or removing the GROUP BY clause in the
subquery.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps
 
JET insists that _both_ source components are updateable even though in this
case, you only want to update one source component (MLS). In your case, the
SubQuery (aliased as CT) is not updateable since it is a GROUP BY Query.

Try using DLookUp() to replace the Join and SubQuery like (NOT tested):

UPDATE [dbo_tMatched_Loans_and_Submissions] AS MLS
SET MLS.Source = DLookUp("APPL_CODE", "dbo_tClient_Tools",
"(ClientID = '" & MLS.Eval_Client_ID & "') AND (ClientID <> '49236')
AND ([APPL_CODE] In ('PBAD','CDSE'))")
WHERE (MLS.Source='DECSW');
 
Back
Top