Bookshop stock keeper

[VirtualBob]

I'm hoping to write a database to help a small organisation keep track of their book sales and keep track of stock. They sell lots of titles a month and some run out sooner than others. So what they want is to do a stocktake, record sales, and show remaining stock. When stock hits a certain point it flags something to say reorder.

I have a file that contains rows for each title:

title | stockcount | triggerpoint

I need to create another file that works with this data. How do I create a field that keeps a running tally as you put in the quantity sold in that period (eg weekly tally per record) it takes into account all previous sales to give you a 'remaining stock' field without resorting to running a summary preview. If it goes beneath the triggerpoint it displays red.

At some point you restock, but then do you start a new file as all the previous records exhausted the last stock?

[Anuviel]

I would do a Sum(field) totaling all sales of a certain item and just keep it in the item record?

Assuming you have sales, items, orders tables.