update query

[bigwillno2 via AccessMonster.com]

Hello i am trying to create an update query......FROM: another query which is
not editable and is product of another Union query.......TO: a newly made
table. i get an error that the update query is too complex can anyone help
with that, my query is posted bellow.

UPDATE qrAllItemsCountFXFab LEFT JOIN tblEditablePO ON qrAllItemsCountFXFab.
OrderNo = tblEditablePO.OrderNo SET tblEditablePO.OrderNo =
[qrAllItemsCountFXFab].[OrderNo], tblEditablePO.Vendor =
[qrAllItemsCountFXFab].[Vendor], tblEditablePO.qFill = [qrAllItemsCountFXFab].
[qFill], tblEditablePO.OrderQty = [qrAllItemsCountFXFab].[OrderQty],
tblEditablePO.Type = [qrAllItemsCountFXFab].[Type], tblEditablePO.Cost = DSum
("[QfillCost]*[OrderQty]","qrAllItems","[OrderNo] =" & [OrderNo]);

thanks for your help

 

[Guest]

You are not going to be able to use the DSUM for your last field. You cannot
do computations within the DSUM function. Instead, try inserting the
following into the "UpdateTo" cell that corresponds to the Cost field:

(SELECT Sum([QfillCost]*[OrderQty]) FROM qrAllItems WHERE [OrderNo] =
qryAllItemsCountFXFab.[OrderNo])

HTH
Dale