Update Total from one Table into another

[Guest]

Hi

I have 1 TABLE called "InventoryAdditions" which has a FIELD called "Quantity
I then have another TABLE called "Inventory" with a FIELD called "TotalAdded

What would be "Access Syntax" (I can do this in regular SQL but having trouble in Access) to
UPDATE the "TotalAdded" in the "Inventory" Table with the SUM of all the "Quantitys" in the "InventoryAdditions" for each specific item

I have tried this..
UPDATE Inventory SET TotalAdded = (Select SUM(InventoryAdditions.Quanity) FROM InventoryAdditions Where InventoryAdditions.ItemID = Inventory.ItemID

But this is not working & I can't figure out how to do it

Any help would be greatly appreciated

Thank you
Jef
(e-mail address removed)

 

[John Spencer (MVP)]

In Access, you will have to use the DSUM function or build a temporary table and
populate it with new records based on your query and then use that to update
your Inventory table.

UPDATE Inventory
SET TotalAdded =
DSUM("Quantity","InventoryAdditions","ItemID=" & Chr(34) & Inventory.ItemID & Chr(34))

If your ItemID is a number field then drop the & Chr(34). They are there to
enclose the value in Inventory.ItemID in quotation marks ("), which is not what
you want for number fields.

 

[Guest]

That worked !

Thank you John.