Let me explain it step-by step.
You buy some goods/articles and then sell them.
The first table your database need is the list of all articles you buy or sell - you can't buy or sell anything what you haven't registered before
tblArticles: ArticleID, ArticleName, ...
Then you need an inventory table, where you register periodically, how much of every article you have in stock at given date. NB! This is not for everyday inventory! You can make it p.e. at end of every year, and register real quantity of every article at inventory date early midnight (i.e. InventoryDate 00:00:00). And whenever you register a new article through the year, you may enter it into inventory table too - with quantity equal to 0. Or you can assume, that when article is missing from Inventory table at given inventory date, then it's inventory quantity is 0.
tblArtInventory: ArtInvID, ArtID, InvDate, InvQty
And then you need a table where you register all transactions, like Orange wrote. Incoming articles positive and outgoing articles negative quantities.
tblTaransactions: TransactID, TransactDate, TransactType, ArtD, ArtQty, TransactPrice
Mark the field TransactType. You can have defined the transaction types as "buying" and "selling" too, or you leave it empty for ordinary transactions, but you define additional types like as "missing" or "surplus" or "scrapped" etc.
As next you create a saved query
qryCurrentData:
Code:
SELECT inv.ArtID, inv.InvQty FROM tblArtInventory AS inv WHERE inv.InvDate = (SELECT MAX(inv0.invDate) FROM tblArtInventory AS inv0) UNION SELECT tran.artID, tran.ArtQty FROM tblTransactions AS tran WHERE tran.TransactDate > (SELECT MAX(inv0.invDate) FROM tblArtInventory AS inv0)
And another one
qryStock:
Code:
SELECT stock.ArtID, SUM(stock.artQty) AS ArtBalance FROM qryCurrentData AS stock
Now you can start the work! At evening you run the query qryStock, and compare it with your daily inventory. When there is some difference, you register it with appropriate TransactType (positive quantity when surplus, negative when missing). When after that you run qryStock again, it must match with your daily inventory now.
Separate queries can give you non-zero totals of "missing" and "surplus" for every article for given period (day/week/minth/year).
Also you can account, how much of goods you had to scrap because p.e. going over realization term etc.