Question Stock Balances - Stock Checking ? (1 Viewer)

I have a basic stock system that has calculated balances based on transactions , and for some time I've been unhappy that I was creating and maintaining a Locations Table containing fields;
PartID (PK)/(FK) , Location(PK), Qty and StockCheckDate

to accommodate multiple possible locations for the same part.

In a moment of tidying up/improving things, I was going to remove this and simply calculate the current location balances, as I do the overall Part Qty values from my PartsMovement Table.
The problem is - how do I store the stock check date? If I put it against the top level Parts record I can't guarantee that all the locations where stock is held are checked, as different locations for the same part may be checked on different days? Is this one of those occasions where NOT storing the calculation actually works against me ?

I don't have a table listing just the locations, as it has never seemed necessary. Stock locations are created and removed on the hoof as it were, and can always be looked up from the current stock transaction records.

Any thoughts - am I missing something obvious. (I have Man-flu so it's quite possible I'm being very thick... )

Minty,

The man flu must be going around. (Been sidelined for a few days).

I think the stock Taking approach presented by Allen Browne is basically
- you take Physical Stock (by Location and Part on a certain Date) and then
- you can determine your current stock (again by Location and Part) based on last physical stock taking and the incoming(positive/add) and outgoing(negative/subtract) transactions.

Seems you need a table to record PhysicalStockTakingDate for each Part at each Location.

Hope these ramblings are useful.

It can become difficult if every part has a different last stock check date.

Maybe this is a case where, after you have a stock check, and set a new opening balance you possibly COULD archive the older movements to an archive table.

Then you won't have any older stock movements to contaminate your data, and you can just sum the transactions you do have to get the total/balance on hand

Or maybe, on reflection, flag the older transactions as "old", so you can just sum the unflagged transactions. That sounds better.

To make it clear, I would store the stock take quantity as a transaction in the movements table, with it's own transaction type. I would never have more than one transaction table.

Thanks both, Dave the idea of keeping the stock check as a special transaction type is a great idea. I can flag all previous transactions as "Old" for that location at the time a stock check balance is entered, and that will also speed up the transaction balance sums as the number of records grow.

It can take nearly a week to stock take the whole inventory and we have some stock at remote locations, so we do spread out the stock take to specific storage locations.

Pen and paper time to make sure I think it through.

I can flag all previous transactions as "Old" for that location at the time a stock check balance is entered, and that will also speed up the transaction balance sums as the number of records grow.

Click to expand...

Or you could Select top 1 as Allen's article shows, you'll have to adjust to include Location.

Product + Location +LatestStockTakeDate should get the info you need.


Good luck.