Stock Level Dilemna

[KevinM]

I made the mistake of including a stock level field in a products table some years ago (I was 'young' and naiive back then).

I am now in a position to amend this database and I am well aware that including a stock level field (or any calculated value) in a table is a complete NO NO.

So the obvious way to do it is the calculate the stock level in a totals query based on the QTY IN- QTY OUT and creating transactions (Issue or Return) to adjust the stock levels at stock checks.
I'm pretty sure that this is the 'professional way of doing it on a stock system. (or is there a better way?)

BUT...

All transactions need to be removed from the db and archived at the end of the year, thus affecting the stock levels.

What is the best way of maintaining stock levels to be carried over to the next year.
Do I need to use an 'archive table' that will store all my stock levels/transactions at the end of the year?
I feel this is going back to doing it the 'wrong way' again!

TIA

 

[Rob.Mills]

What I've done in similar situations is to setup a function that will first calculate the inventory total for the year that you will be archiving and create a 'Beginning Inventory amount' in the table. Then run an append query followed by a delete query. It does that for each product. Takes a little time for the function to run but that's how I've done it. Wouldn't doubt if there's a more efficient method though.

 

[The_Doc_Man]

The way I have tried this in the past (with my check-balance variant of the inventory problem) is that you select a date and time before which you will archive all records.

Synthesize a new record of a third type, neither a stock ADD nor a stock REMOVE, call it a "balance on hand at date" record for lack of a better term. It must contain the instantaneous count of parts on hand at the exact moment chosen as the cutoff date and time, but no parts associated with any other transactions after the cutoff. In a running system, you can generate count this by summing your ADD and REMOVE (and prior BALANCE) records up to the critical date.

You do one of these for each type of item in your inventory, even if the balance on hand is zero or negative (overcommitted) at the moment. Option: if the balance is zero, you could omit creating the record, which means that retired parts eventually will cease to be recorded in your system. This also would automatically handle cases where a part is added to your parts list since the last archive step occurred.

Now perform your archiving step but do not take out the balance record. Make its time and date such that it is the first record to NOT be archived based on your chosen archiving criteria. Like, maybe make its time and date 0.1 seconds after midnight of the morning after your cutoff date or something like that.

Then include this BALANCE record in the query that runs your totals. Treat it like a stock ADD except that it is independent of any purchase order or shipments. Remember that it is possible by the suggested rules above to omit this record if the balance would have been reported as zero. So new parts can just "appear" and old parts will "disappear" as the inventory progresses. (Assuming of course that when you discontinue a part you generate a stock REMOVE entry for that item.)

Next archiving period, do the same thing, only allow all prior "balance on hand at date" records to be archived, too. That way, you never have more that one of these at a time for any given inventory item.

From what you describe, your hardest step is going to be to figure out the balance on hand at the critical date in your OLD scheme, though this should not be impossible. What I would do there is to do the same exact thing as I described except with the dates sorted in the reverse order. This would allow you to take your current balance, tack on the stock ADD and stock REMOVE transactions to your critical date, and generate the balance record for your cutoff date.

Hope that was clear enough.