Is it Dsum or am I a dumbdumb?

[TheAxe]

Ok I have a table for receiving goods (ReceiveGoods) and it has:
1] RecQty - thats the amount ordered
2] RecRec - Thats the amount received

I have an inventory table for all my products which has:
1] Onorder

Problem:
I want to do: RecQty-RecRec by product in my Receiving table and place the result in Onorder in Inventory. This is the query I made

Update Query
Field: Onorder
Table: Inventory
Update to: DSum("[RecQty]-[RecRec]","ReceiveGoods","[Inventory]![inventoryID]"="[ReceiveGoods]![InventoryID]&quot

I get blanks when I run it. Why????

 

[TheAxe]

Sorry guys once I wrote it out a solution came to me:

I did one Dsum for one field - Dsum for the other field. Its a massive statement but it works. Anyone think of a more elegant solution?

 

[rajeessh]

Use Sum in query rather than DSUM

DSUM imp-lements sum by Access VBA
In query try to minimioze its use

 

[sko]

Try this:

UPDATE tblInventory INNER JOIN tblReceivedGoods ON [tblInventory].[InventoryID]=[tblReceivedGoods].[InventoryID] SET tblInventory.OnOrder = [tblReceivedGoods]![QtyOrdered]-[tblReceivedGoods]![QtyReceived];

Looks like you're using query design view, so...

Add both tables and make sure they are joined one to many
Field: OnOrder
Table: Inventory
Update: Right-click and use build. Select your received goods table> RecQty > value > minus
Select received goods > RecRec > oK

HTH