Update a table from a query

  • Thread starter Thread starter SAP2
  • Start date Start date
S

SAP2

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.
 
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.
 
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.
 
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));
Click to expand...

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] & "' ");
 
SAP2 said:
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]));
Click to expand...

You can't update a query based on DISTINCTROW.
 
You can't update a query based on DISTINCTROW.
Click to expand...

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 said:
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!
Click to expand...

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. :-)
 
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 said:
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));
Click to expand...

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] & "' ");
Click to expand...
 
Back
Top