By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,657 Members | 1,029 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,657 IT Pros & Developers. It's quick & easy.

Update Query Help!

P: n/a
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.

Nov 12 '05 #1
Share this Question
Share on Google+
1 Reply


P: n/a
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.

Nov 12 '05 #2

This discussion thread is closed

Replies have been disabled for this discussion.