Update Sum Query

[Samantha]

I need to update a temp table (tblBOM) with a summed quantity from a query
(WOShortagesWithSOSum). Here's what I have so far that is giving me error
"not updatable query ..."
The query WOShortagesWithSOSum is a summarized query:

sql = "UPDATE tblBOM INNER JOIN [WOShortagesWithSOSum] ON tblBOM.Component =
[WOShortagesWithSOSum].PartNumber "

sql = sql & " SET tblBOM.QtyShort = [WOShortagesWithSOSum].[TotalQty];"

Any pointers are very much apprciated!

 

[Ofer Cohen]

Sometimes the InnerJoin between the two tables make the query not updatable.

Try a different approach using dlookup in a new query, and then execute the
query

UPDATE tblBOM SET QtyShort = Nz(DLookUp("[TotalQty]",
"[WOShortagesWithSOSum]" , "[PartNumber] = " & [Component]),0)

 

[John W. Vinson]

I need to update a temp table (tblBOM) with a summed quantity from a query
(WOShortagesWithSOSum). Here's what I have so far that is giving me error
"not updatable query ..."
The query WOShortagesWithSOSum is a summarized query:

sql = "UPDATE tblBOM INNER JOIN [WOShortagesWithSOSum] ON tblBOM.Component =
[WOShortagesWithSOSum].PartNumber "

sql = sql & " SET tblBOM.QtyShort = [WOShortagesWithSOSum].[TotalQty];"

Any pointers are very much apprciated!

No Totals query, nor any query including a Totals query, is ever updateable.

You may need to use DSum() or DLookUp() to sum or look up the value from the
query.

As a rule, though, you should NOT be storing sums in any table, even a temp
table. What will you be doing with the temp table that you can't do with a
totals query directly?

John W. Vinson [MVP]