Mulitple Line Update Query

[andyb2005]

I have two tables.
[Stock] has a list of part numbers and quantities.
[Order] has a list of part numbers and their order number.

So [Order] table could have the following data:

Index, Order No, Qty, Part No,
101 9702 1 121-454
102 9702 2 121-403
103 9702 1 120-500
104 9703 3 121-454

[Stock] table looks like this:
Part No, Qty
120-500 50
121-454 10
121-455 20


I want to be able to update the [Stock] table quantities from the [Order] table.

How would I do this in an access query to update multiple order rows.

 

[gol4]

here is one option

UPDATE stock INNER JOIN orders ON stock.[part no]= order.[part no] SET stock.qty = [stock].[qty]-[order].[qty]
where index > 101

next time you run query index would be 104 so you dont do it more then one time