Update a table from a query
in [General]
|
From: John W. Vinson on 13 Apr 2010 22:26 On Tue, 13 Apr 2010 19:56:55 -0600, Hans Up <hans.updyke(a)nospam.invalid> wrote: >You can't update a query based on DISTINCTROW. Hans, I think you're confusing DISTINCTROW with DISTINCT. In fact sometimes you have to add DISTINCTROW to a query in order for it to become updateable! -- John W. Vinson [MVP]
From: Hans Up on 14 Apr 2010 02:49 John W. Vinson wrote: > Hans, I think you're confusing DISTINCTROW with DISTINCT. In fact sometimes > you have to add DISTINCTROW to a query in order for it to become updateable! I completely misunderstood DISTINCTROW. I expected 2 rows instead of 3 for the sample data and query below. fld1 Tot_Used ---- ----- foo $1.23 foo $1.23 bar $1.23 SELECT DISTINCTROW c.Tot_Used FROM CostSummarySheet AS c; Thanks, John. You're sure useful to have around. :-)
From: SAP2 on 14 Apr 2010 08:54
John, No, it asks for the parameter value for CostCNS now. I will go he make table route. Thanks everyone for your time. "John W. Vinson" wrote: > 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] > . > |