Update a table from a query
in [General]
|
From: SAP2 on 13 Apr 2010 16:05 Hello All, I am trying to update a table using a query. I keep getting "Operation must use an updateable query". I am not an SQL expert. I just sort of muddle through. My update query is this: UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON (CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) AND (CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) SET CostSummarySheet.Tot_Used = [qryUpdateTotalUsed3]![SumOf$$] WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND ((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); I have tried to use DSum to no avail: UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON (CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) AND (CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) SET CostSummarySheet.Tot_Used = DSum("$$","qryUpdateTotalUsed3","PROJ_NUM='" & qryUpdateTotalUsed3.PROJ_NUM & "' And CostCNS='" & [CostCNS] & "' ") WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND ((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); My regular Select Query looks like this: SELECT qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, qryUpdateTotalUsed2.CostCNS, Sum(qryUpdateTotalUsed2.[$$]) AS [SumOf$$] FROM qryUpdateTotalUsed2 GROUP BY qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, qryUpdateTotalUsed2.CostCNS HAVING (((qryUpdateTotalUsed2.PROJ_NAME) Is Not Null)); Is there a way to do what I am asking? I am trying to avoid "making a table" from the select query as it would ruin the automatic link with Excel for updating this table (CostSummarySheet). I am using Access 2003. Thank you in advance.
From: ghetto_banjo on 13 Apr 2010 16:41 I have always gotten that error when trying to do an Update Query from a Linked Table. Never found a solution other than doing a Make Table first to make it a local table.
From: Jeff Boyce on 13 Apr 2010 16:56 If your data is in Excel and you are linked to it, this behavior is as designed/expected in some versions of Access. Check article # 904953 in the MS knowledge base. Regards Jeff Boyce Microsoft Access MVP -- Disclaimer: This author may have received products and services mentioned in this post. Mention and/or description of a product or service herein does not constitute endorsement thereof. Any code or pseudocode included in this post is offered "as is", with no guarantee as to suitability. You can thank the FTC of the USA for making this disclaimer possible/necessary. "SAP2" <SAP2(a)discussions.microsoft.com> wrote in message news:B7A4CBC3-0F10-48AB-BA48-C699436E52EE(a)microsoft.com... > Hello All, > I am trying to update a table using a query. I keep getting "Operation > must > use an updateable query". I am not an SQL expert. I just sort of muddle > through. My update query is this: > > UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON > (CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) AND > (CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) SET > CostSummarySheet.Tot_Used = [qryUpdateTotalUsed3]![SumOf$$] > WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND > ((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); > > I have tried to use DSum to no avail: > UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON > (CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) AND > (CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) SET > CostSummarySheet.Tot_Used = DSum("$$","qryUpdateTotalUsed3","PROJ_NUM='" & > qryUpdateTotalUsed3.PROJ_NUM & "' And CostCNS='" & [CostCNS] & "' ") > WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND > ((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); > > > My regular Select Query looks like this: > SELECT qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, > qryUpdateTotalUsed2.CostCNS, Sum(qryUpdateTotalUsed2.[$$]) AS [SumOf$$] > FROM qryUpdateTotalUsed2 > GROUP BY qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, > qryUpdateTotalUsed2.CostCNS > HAVING (((qryUpdateTotalUsed2.PROJ_NAME) Is Not Null)); > > Is there a way to do what I am asking? > > I am trying to avoid "making a table" from the select query as it would > ruin > the automatic link with Excel for updating this table (CostSummarySheet). > > I am using Access 2003. > > Thank you in advance. > > > >
From: John W. Vinson on 13 Apr 2010 17:14 On Tue, 13 Apr 2010 13:05:01 -0700, SAP2 <SAP2(a)discussions.microsoft.com> wrote: >Hello All, >I am trying to update a table using a query. I keep getting "Operation must >use an updateable query". I am not an SQL expert. I just sort of muddle >through. My update query is this: > >UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON >(CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) AND >(CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) SET >CostSummarySheet.Tot_Used = [qryUpdateTotalUsed3]![SumOf$$] >WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND >((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); > >I have tried to use DSum to no avail: >UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON >(CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) AND >(CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) SET >CostSummarySheet.Tot_Used = DSum("$$","qryUpdateTotalUsed3","PROJ_NUM='" & >qryUpdateTotalUsed3.PROJ_NUM & "' And CostCNS='" & [CostCNS] & "' ") >WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND >((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); > > >My regular Select Query looks like this: >SELECT qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, >qryUpdateTotalUsed2.CostCNS, Sum(qryUpdateTotalUsed2.[$$]) AS [SumOf$$] >FROM qryUpdateTotalUsed2 >GROUP BY qryUpdateTotalUsed2.PROJ_NAME, qryUpdateTotalUsed2.PROJ_NUM, >qryUpdateTotalUsed2.CostCNS >HAVING (((qryUpdateTotalUsed2.PROJ_NAME) Is Not Null)); The problem is that no Totals query, nor any query including or referencing a Totals query, is ever updateable. An annoying restriction, especially in cases like this where there's no logical reason it should not be updateable! Just the fact that you're Joining to qryUpdateTotalUsed will block updateability. However... if you're using the same criteria (for Proj_Num and CostCNS) in the DSum, maybe you don't need the join at all: try UPDATE DISTINCTROW CostSummarySheet SET CostSummarySheet.Tot_Used = DSum("$$","qryUpdateTotalUsed3","PROJ_NUM='" & PROJ_NUM & "' And CostCNS='" & [CostCNS] & "' "); -- John W. Vinson [MVP]
From: Hans Up on 13 Apr 2010 21:56
SAP2 wrote: > Hello All, > I am trying to update a table using a query. I keep getting "Operation must > use an updateable query". I am not an SQL expert. I just sort of muddle > through. My update query is this: > > UPDATE DISTINCTROW CostSummarySheet INNER JOIN qryUpdateTotalUsed3 ON > (CostSummarySheet.CNS_Num = qryUpdateTotalUsed3.CostCNS) AND > (CostSummarySheet.Proj_Num = qryUpdateTotalUsed3.PROJ_NUM) SET > CostSummarySheet.Tot_Used = [qryUpdateTotalUsed3]![SumOf$$] > WHERE (((CostSummarySheet.CNS_Num)=[qryUpdateTotalUsed3].[CostCNS]) AND > ((CostSummarySheet.Proj_Num)=[qryUpdateTotalUsed3].[PROJ_NUM])); You can't update a query based on DISTINCTROW. |