Stock update query

[Danny B]

This has probably been asked MANY times before, but I can't seem to find a thread on it.

Right. I have a table with fields in it called 'Current Stock' and 'Stock Used'. I don't know how this happens, but I want to enter a number (via a form) on 'Stock Used', and have that number deducted from 'Current Stock'. It would be handy if the 'Stock Used' field could reset to nothing after this happens.

Do I make a new field contain 'Current Stock - Stock Used' or do I do something else?

Any help would be great!

Cheers

 

[llkhoutx]

CurrentStock is a bound field, bound to your table showing inventory.

StockUsed is bound probably to a table/suform and on its AfterUpate event, CurrentStock is reduced by StockUsed.

 

[KevinM]

Not a good idea to store derived values in a table.

Their values should be automatically calculated via a 'transactions table'.

You will need to make sure that they values are calculated everywhere in your db. And what if the values are changed in table view? You won't be able to re-calculate them there!

BTW, I was born in Portland and have some very fond memories of Weymouth. Went back there last year for the first time in 20 years....still a great place