If you have a single record showing product on-hand and you add or subtract quantities as they are bought and sold, you are going to forever have trouble. The correct (but somewhat painful) way to do this is through transaction summaries.
Build a transaction table. Include the produce identifier (maybe an SKU or stock number), the quantity, and the nature of the transaction. Design your records with 1 or -1 multiplier codes in them for transactions that add or remove stock. Build summation queries on the multiplied transaction amounts and grouped by the product identifier. Then on any given day, your inventory on hand is merely the sum of the transaction amounts for each product group.
Transactions either add stock or remove it. So you have "Purchase from Supplier" - which adds stock so its multiplier is +1. You have "Sold to customer" which removes stock so its multiplier is -1. You can have stock returns, stock shrinkage, and other types of transactions as needed. Your stock level on any date is merely that same sum based on a WHERE clause where the transaction date is earlier than the date of interest.
When you are ready to archive data, you define one more type of transaction - an inventory total. You build the totals for your inventory up to the cutoff date. Then you add in a record with the cutoff date, a multiplier of +1, and the amount of the given product in inventory as of that cutoff date. Then, once you add the inventory totals records for each product, remove the individual records for each product for which the transaction date precedes the cutoff date. This is pretty much always going to involve simple summation queries with group-by clauses.
