How would I do this? - Stock Control

[mellowyellow]

Have a fairly simple scenario I hope someone can help me carry out.

I am focussing on two tables: Sales and Stock

Stock contains information on each product
and includes a 'currentStock' field which is decremented with each sale.

Sales records each sale with date and stock id.

I want one action to update both tables, what would be the best method?

Thanks

 

[neileg]

Do a search on stock or inventory. You will find plenty of explanations why you should not store the stock balance, it should be calculated from the transactions in and out.

 

[KeithG]

Create a macro to run your queries

 

[mellowyellow]

Do a search on stock or inventory. You will find plenty of explanations why you should not store the stock balance, it should be calculated from the transactions in and out.

would you mind linking me to one of these threads please?
I've tried searching with little luck.

 

[Len Boorman]

If you think stock control is simple you are in for a bit of a shock. It can be extremely complex and you need to clearly set out your scope.

As neileg says, Stock is Receipts - Sales and as a derived value should not be stored.

L