Stock reduction

[Jean Smyth]

Hi
Can anyone help.

I have a table with units in stock eg. beans 27 tins. An order is placed for 6 tins of beans. How can I reduce the amount of stock in my stock table.

Hope someone can help.

Thanks

 

[Ryko]

One way would be to manually replace 27 with 21 in your table but then you'd probably be settled just jotting it down on a piece of paper.

Assuming you track orders in your database you could have it done automatically after each new order details are filled (using an update query).

Another way would be having units in stock as a calculated field where current units in stock = initial stock - sum of units ordered (where order date <= current date). You'd probably also want to track stock additions and include it in the equation as well.

 

[Jean Smyth]

Many thanks I have worked out how to count the stock sold but cant figure out how to work the query that will deduct from stock table.

Thanks

 

[neileg]

You should not be storing the stock balance. You should store the in and out transactions and calculate the balance when you need it by summing these transactions.

 

[Jean Smyth]

Thanks Neil

Regards
Jean