Update Query Problem

[mrliam69]

I want to update a tables field based on the results of a view using another field as the join.

VIEW1 contains partnumber and orderqty
TABLE1 contains partnumber and onorderqty

I would like to update TABLE1.onorderqty to TABLE1.onorderqty + VIEW1.orderqty

hopefully in VB so I can run it on a form close button.

Can any one help ?

Liam

 

[Norris68]

UPDATE Table1 INNER JOIN VIEW1 ON Table1.PartNumber = VIEW1.PartNumber SET Table1.OnOrderQty = Table1.OnOrderQty+VIEW1.OrderQty

You may want a WHERE clause in there to limit the affected rows, as this will currently update all rows in Table1 that have matches in View1.

 

[mrliam69]

TBLPurchaseOrderLines
=========================
¦ PartNumber ¦ OrderQty ¦
=========================
¦ ABC123 ¦ 10 ¦
-------------------------
¦ DEF456 ¦ 20 ¦
-------------------------
¦ ABC123 ¦ 30 ¦
=========================

Table is then grouped to sum up the quantities per partnumber

ViewPOLNTGrouped
=========================
¦ PartNumber ¦ OrderQty ¦
=========================
¦ ABC123 ¦ 40 ¦
-------------------------
¦ DEF456 ¦ 20 ¦
=========================

Then I would like it to add those sums for each partnumber to the current figure in the stock table

TBLStock
=================================================
¦ PartNumber ¦ OnOrderQty ¦
=================================================
¦ ABC123 ¦ 10 + ViewPOLNTGrouped.OrderQty ¦
-------------------------------------------------
¦ DEF456 ¦ 05 + ViewPOLNTGrouped.OrderQty ¦
=================================================

I was hoping to do it from a RUNSQL statement in the Close Button on the Form or call it from a stored procedure but I don't know what a stored procedure is or how to use one.
Please help as I have been struggling for a week now and I can find little reference to update queries on the net.


Regards

Liam