Updating stock in Access Table (1 Viewer)

Hi
I am working on a simple database that is to keep stock of items in a shop and update the quantity left as and when items are added are sold .Can anybody help me with the query or vba code to use and may be how my tables should look like. I am quite new to this area of Access
Thank you

Most experienced developers don't store the stock-on-hand value but calculate it from the transaction information as required.

To elaborate on Galaxioms correct response, what you want to store in database tables is raw representations of real things, and though it may not be immediately obvious, the reality of how many you have in stock is this ...
Sometimes the example of a bank account is useful. Your bank balance is not a primary source of information, but rather, it is always just the current moments calculation of ...
Make sense?
Commonly implemented are database structures for PurchaseOrders and SalesOrders, and the details of these objects, quantities of products bought and sold, are the real-world data you'd normally use to calculate what your stock should be.
And like a bank account then, when you do inventory you essentially do a reconciliation, making sure the record-keeping and the real-world numbers agree.
Cheers,
Mark

Heart, you might want to take a look at the following thread as well

http://www.access-programmers.co.uk/forums/showthread.php?t=218467

It has an actual example database. Note my solution and Rainlovers are quite different fundamentally and mainly because Rainlover is dealing with homogenous products (he works in the mining business) whereas my solution may be better if you have hetrogeneous unique stock.

Mark

Your formular is missing something.

InStock = Purchased - Sold

Should be

InStock = Stock on Hand + Purchased - Sold

A simple oversight in that you left out the opening balance.

The principle I am trying to express is that InStock or StockOnHand or StockWeCurrentlyHave or StuffInTheWarehouse--whatever you want to call it--should not be stored anywhere as raw data. An opening balance is an inflow in respect to the calculation and so if I amend my formula it would only be to generalize it as follows...
If you do this ...
... you beg the question. How did you calculate the constant?
IMO.
Mark

Rumble in the jungle

Sorry Mark,

You are correct.

EDIT

Upon reflection I posted without due consideration.

Your Formula is not correct.

I shall post my reasons shortly.

thrilla in manila

Another way of looking at Stock Quanitities, is some Products have large volumes whilst others maybe Unique. Some Products maybe fast moving whilst others, the transactions are few and far between. There maybe thousands of Products with thousands of transactions so to get an evaluation of Stock requires dredging the entire history of transactions.

To eliminate this overhead you can adjust your stock balances when an event affects a particular Product. It may not be scientific but providing your can see all the events for one particular product then you can easily see if the strategy is working or not.

After awhile it can pay to archive some stock transactions into History so that you the Current Stock is not cluttered with events that are no longer relevant.

Simon

Rain’s formula looks correct.
Stock On Hand = Last Stocktake + Purchases since last Stocktake - Sales since Last Stocktake.

Now let’s look at it without the Last Stocktake…
Stock On Hand = Purchases since last Stocktake - Sales since Last Stocktake.
If both Purchases since last Stocktake and Sales since Last Stocktake are zero then Stock On Hand is zero.
So, if a company starts trading with 1000 widgets on hand then the reported Stock On Hand would be zero.



Since we don’t know what is in stock let’s look at another way to look at quantity on hand…

We have a tank with some liquid in it and the current quantity can be calculated by input flow – output flow.

However, that will only work when starting from 0 quantity. And it will only work if both measurements are perfectly accurate. But the measurements are not perfectly accurate, no measurements are. So an error exists and that error can be said to be the time integral of the difference between the two inaccuracies. If the difference between the two inaccuracies is positive then the error in quantity is positive and if the difference between the two inaccuracies is negative then the error in quantity is negative. What’s more is that the absolute error in quantity increases with time.

To correct the situation a certified dip of the tank is made periodically. The dip produces a known quantity to the best of its measurement capability. But the dip’s measurement capability is static and not changing over time.

So the error in the instantaneous quantity on hand is limited to the time integral of the difference between the inaccuracies of the two flow meters plus the static inaccuracy of the certified dip. Therefore, the instantaneous error of quantity on hand is largely governed by the time between dips. The more often the dips the less the error, the less often the dips the greater the error.

Instantaneous Quantity in Tank = Static Last Dip Measurement + Time Integral Since Last Dip of (Sum(Inflow +/- error) – Sum(Outflow +/- error)).

However, that formula will not work under some circumstances. Notoriously bad are quantity measurements in boiler drums. In this case the level is measured continuously but it can’t be measured accurately so a different approach is taken. Due to the inaccuracy of the instantaneous level measurement the time integral of the measurement is used instead. The time integral of the measurement is done every 10 minutes or so (10 minute stocktake) and the inaccuracies of the flow measurements then become trivial.

Instantaneous Quantity in Tank = Time Integral of (Level Measurement) + instantaneous inflow – instantaneous outflow.

There is a similarity between both calculations though. In both cases the Level and the Flows need to be considered in order to get a quantity.



The dips equate to a manual stocktake, but even a manual stocktake can be in error due to bad counting. If a stocktake is in error that error remains static until the next stocktake. The stocktake error does not grow with time, only the in and out quantities +/- errors integrate with time.

Chris.

Guys/Gals

While we have been debating the merits or otherwise of storing calculated values, we have not addressed the OP's original question.

heartofworship said:

Hi
I am working on a simple database that is to keep stock of items in a shop and update the quantity left as and when items are added are sold .Can anybody help me with the query or vba code to use and may be how my tables should look like. I am quite new to this area of Access
Thank you

Click to expand...

Could you please post your current table structure.

From there we could give some appropiate advice.

OK, question. What I would want to do in an inventory system is implement my inventory count--so the 'LastStockTake'--as a SalesOrder. So my inventory count looks like...
...but I have a customer called Inventory and I periodically create a SalesOrder to Inventory that balances the real world count of StockOnHand.
What is the problem with this approach?
Thanks,
Mark

I think what is missing from this discussion is the concept of traceable verification.

Traceable verification can be viewed as a system being verified by some outside standard. If the system supplies both the data and the means to verify that data then it is not traceable.

A manual stocktake is outside the system; it is not controlled by the system or influenced by the system. And that is the stocktake’s worth; it is an external audit controlled by its own standards and not the standards of the system.

A manual stocktake value entered as a SalesOrder would leave it exposed to exactly the same errors as any other SalesOrder. It ceases to be outside the system, it ceases to be immune from the system which it is intended to verify. It would simply become part of a system which is trying to verify itself by pulling itself up by its own bootstraps.

Chris.

Mark,

You have opened a discussion that to my knowledge has never been totally explored on this Forum.

It is good that you have done so. So let the fun begin.

Back in the dark days when I was a member of UA I tended to follow without question the opinions of others. I always said that storing calculated values was incorrect. But these days I have see the light and believe that there are other things to take into consideration.

With your formula/method it all depends on how serious you want to be.

Your method may work but it is based upon a mistruth. (You really don’t have a customer called Inventory)

If you are never likely to be subjected to an Audit, then fine go with what suits you best.

But the moment you go down this path you will always have to "Code" with this in mind.

I am sure you have heard the saying that excessive coding is a sign of a poorly designed database.

If the number of transactions per month were just a few then your work around would be reasonably easier to handle.

If your transactions were in the many thousands or more per month then you need a more serious approach.

Your calculations require going back to day one, whereas I only need to go back to the previous stock take.

If your company became more and more successful each month and sales grew you could end up with many millions of entries to include in your calculations.

But here is some food for thought. This is of course taking things to the extreme.

My mum is near 90 years old. She has banked with the same bank for 70 years. Do you think that all that time ago that the bank did not store calculated values. Of course they did. They didn’t have computers back then.

So one of the reasons for not storing calculated values is because with computers we don’t have to. The computer can recalculate on demand.

Another reason why calculations are not stored is because in the earlier days space was at a premium. They did not even store 4 digits with dates. Hence the Y2K bug.

Today we have large computers with lots of computing power. So there is less need to store calculated values.

So the question remains, “When do we store Calculated Values”. And of course what values do we store and what do we recalculate.

I will stop here for the moment and see if others have an opinion.

As I said this could be an involved discussion.

I'm listening. Thanks for your time.
Mark

lagbolt said:

...but I have a customer called Inventory and I periodically create a SalesOrder to Inventory that balances the real world count of StockOnHand.
What is the problem with this approach?

Click to expand...

The principle of entering a stock adjustment transaction is correct. However it is a bit clumsy using an inventory customer. A better way is to use a transaction type field to indicate the transaction is not a standard sale/return.

This field would also support the indication of the type of adjustment. For example spoilage, loss etc.

This Thread appears to have scared off the original poster.

But that is no reason why we can't discuss the topic in more detail.

I have already written some verse which is open to opinion.

One thing that is not open is the ability to reproduce a financial document. We must always be able to reproduce an Invoice so the Total should be stored, so that the reprint can be checked against the original. Also statements fall into this category.

Auditors would have a field day if these did not match. So there must be a way to lock periods so that adjustments cannot be backdated.

Another form of inventory is where each item is unique. Like Motor Vehicles. You may have 50 of the same type but each one has a unique identifer. (VIN) We really can't adjust stock levels if the stocktake does not agree with the paper work.

Sorry boss we are missing a $100,000 Merceds. But the program balances because I made an adjustment.

We need to get past the Monkies and realise that storing calculated values is not a sin.

Do we need a new set of rules?