Update Query Help!

[Kunal]

Hi,

I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransactions", one for each product sold in that transaction. I
have a subtransaction table that maintains record of each subtransaction,
with data such as: ProductID, Quantity, TransactionID etc.
The Products table has fields: ProductID, UnitsSold, UnitsOrdered,
UnitCostPrice etc.

What I want is to update UnitsSold for each ProductID in Products table by
adding Quantity field for each subtransaction in Subtransaction table that
has this ProductID.

Currently I have this Query:
UPDATE Products INNER JOIN Subtransactions ON
[Products].[ProductID]=[Subtransactions].[ProductID]
SET Products.UnitsSold =
DSum("Quantity","Subtransactions","Subtransactions .ProductID=[Forms]![Transa
ctions]![Subtransactions Subform].[Form]![ProductID]")
WHERE
((([Subtransactions].[ProductID])=[Forms]![Transactions]![Subtransactions
Subform].Form!ProductID));

The problem with this query is that it only updates the UnitsSold for the
product that is currently "selected" in the Subtransactions Subform.

Can someone suggest how can the query be modified so as to update ALL
products, and not the only one selected?

Thanks in advance.

Kunal.

[Allen Browne]

Can I suggest you do not do this?
How will you handle it when a user deletes one of the subform records? What
about if someone changes the quantity from 100 to 10 after you have already
run this update query?

For an alternative way to solve this issue, see:
Inventory Control - Quantity On Hand
at:
http://allenbrowne.com/AppInventory.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html

"Kunal" <ku*********@motorola.com> wrote in message
news:bj**********@avnika.corp.mot.com...

Hi,

I need some help on writing an update query to update "UnitsSold" field in
Products Table whenever I save a transaction. The transaction may contain
several "Subtransactions", one for each product sold in that transaction. I have a subtransaction table that maintains record of each subtransaction,
with data such as: ProductID, Quantity, TransactionID etc.
The Products table has fields: ProductID, UnitsSold, UnitsOrdered,
UnitCostPrice etc.

What I want is to update UnitsSold for each ProductID in Products table by
adding Quantity field for each subtransaction in Subtransaction table that
has this ProductID.

Currently I have this Query:
UPDATE Products INNER JOIN Subtransactions ON
[Products].[ProductID]=[Subtransactions].[ProductID]
SET Products.UnitsSold =
DSum("Quantity","Subtransactions","Subtransactions .ProductID=[Forms]![Transa ctions]![Subtransactions Subform].[Form]![ProductID]")
WHERE
((([Subtransactions].[ProductID])=[Forms]![Transactions]![Subtransactions
Subform].Form!ProductID));

The problem with this query is that it only updates the UnitsSold for the
product that is currently "selected" in the Subtransactions Subform.

Can someone suggest how can the query be modified so as to update ALL
products, and not the only one selected?

Thanks in advance.

Kunal.