Originally Posted by
ArviLaanemets
A foolproof way to keep tabs on stock is to keep the log of all stock movements. An example:
You have a table where you register items, like
tblItems: ItemID, ItemType, ItemName, ...
And you have a table where you register all item movements in single or in several stock like
tblStockMovements: MovementID, StockID, ItemID, MovementDate, MovementType, ...
with different movement types for incoming items (purchase, produced, returned, etc), and for outgoing items (sale, used in production, scrapped, etc.), and also for inventory corrections. You can then easily calculate the stock balance at any time moment adding all incoming movement quantities for item up to this time moment, and deleting all outgoing movement quantities for same item in same time period. Plus you can get a lot of another useful info from this log file whenever you need it.