Stock Control / Maths

[Chaz]

Hi,

Looked everywhere an I cannot find a workable solution.

I am trying to allocate stock and once someone assigns the stock to a job, the stock available simply decreases by 1 each time.

Sounds easy - perhaps it is but I dont know where / how to do it.

We are told not to store values in tables - but not certain how you will start with 50, then 49, then 48, each time 1 is removed.

I can get a query to do a subtraction sum of 50-1 = 49 but dont know how to refer to that answer on a form. After that - I need to update the table perhaps and now know that 49 units are available.

Northwind is confusing and I dont understand how its done there. Im stuggling to understand the best way to do this calculation and how the forms / tables / queries should interace. Should I use the same table with fields like stock_qty_available, stock_qty_updated etc or somehow manage different tables and do a count on each record of the same to know what qty is now available?

Thanks

 

[Galaxiom]

We are told not to store values in tables - but not certain how you will start with 50, then 49, then 48, each time 1 is removed.

Don't store derived values in tables. Your stock quantity is a master value. You would not record your initial stock and then seach for every sale ever made every time you wanted to know how many you had on the shelf.

But you would not record the total value of widgets in stock because you can derive that from the quantity and their value. Total value is derived.

 

[Chaz]

Don't store derived values in tables. Your stock quantity is a master value. You would not record your initial stock and then seach for every sale ever made every time you wanted to know how many you had on the shelf.

But you would not record the total value of widgets in stock because you can derive that from the quantity and their value. Total value is derived.

Thanks. Any advise as to how to go about this?

So I have table_master with Widget_QTY_Start = 50, then where do I go from there?

Thanks for your time.

 

[Chaz]

Should I be adding records to a table each time something is sold and the simply count the number of records with a certain filter per widget type? Is that one way to do it?

 

[Galaxiom]

A Control such as a combo or textbox can be set to the value in a query just like it is set to a table. Your query could include the field Original stock and Updated stock. This allows you to display the Updated stock on the form as well as the original stock.

However what you are really looking for is to update the Stock table with the Updated quantity when you process the Invoice. I expect you have some kind of transaction records such as lines in an invoice in a table. Lets call it InvoiceLine. This table will be the Record Source for your Invoice Entry Form.

Create an Update query. Join the InvoiceLine table to the Stock table on ItemID.
In the Uptate to: of tblStock.Quantity enter something like:
tblStock.Quantity-tblInvoiceLine.Quantity

When you run this query the stock level will be decremented as required.

You are also going to have to deal with ensuring the query is only run once. This can be done by flagging the Invoice as processed. Include a field on the invoice table that holds a setting to indicate the status. You might have 1 for an Estimate, 2 for a quote, 3 for an invoice. So when you process the invoice you would update this field to three.

When you load the invoice into a form to look at later, test for the status. If it is 3 then disable the process button and Entry property. You will need to use VBA for this. You also use VBA to run the queries on the Process button and disable it once the query is run.

Hopefully this gives you enough of an idea to get your head around the basics.

 

[Galaxiom]

Sorry my explanation is so all over the place.
It is 12:40 am here. Got to go to bed. I do hope you can decipher what I have posted.

 

[Chaz]

Sorry my explanation is so all over the place.
It is 12:40 am here. Got to go to bed. I do hope you can decipher what I have posted.

No problem - thanks.

I thik I tried similar to what you have put but will work through the details to see if I can make sense of it.

 

[gemma-the-husky]

chaz - the normal way with stock is NOT to store the actual stock value.

what you do is store all the stock movements in a table - you calculate the balance when needed by adding together all the appropriate movements.

occasionally, you might roll all the movements up into a single figure - say at a stock take, and start again, or maybe once a month -

 

[Galaxiom]

chaz - the normal way with stock is NOT to store the actual stock value.

what you do is store all the stock movements in a table - you calculate the balance when needed by adding together all the appropriate movements.

occasionally, you might roll all the movements up into a single figure - say at a stock take, and start again, or maybe once a month -

This is certainly NOT the case in any of the commercial inventory packages I have been intimately involved in working with. They have invariably included the current stock quantity in an inventory master file.

To derive the current quantity by querying the invoices and purchase orders every time a line was added to an invoice would be a hideous processing overhead.

Moreover it would preclude any opportunity to verify the database integrity by comparing the quantity recorded in the inventory file against the movements.

I would be curious to know which commercial packages use the technique described by Gemma-the-Husky so I can be sure to adamantnly recommend against anyone choosing them for their system.

 

[Atomic Shrimp]

Some commercial packages do store stock as a discrete value, some don't. I've worked with both.
The ones that don't store current stock still tend to have an 'opening stock' value (that starts at zero when the system is first implemented, or for any newly added item), with the current stock being calculated from the opening stock plus or minus any transactions. When transactions are archived, the opening stock is recaculated to include them.

The ones that do store the value sometimes have problems where the recorded stock figure doesn't tally with the figure you would get if you added up all the stock increments and took away all the stock decrements (and this can happen because the stock figure was updated but the transaction details failed to append into a transaction log, or something like that).

Gemma is right that this is how it really should be done, but in my experience, this has been one of the hardest bits of normalized database design for people to intellectually accept and then implement.

Discretely stored stock values can become unreliable in a multi-user system, (i.e. when two people try to create an order for the same thing at ths same time) if the ins and outs of transaction processing are not handled with absolute rigour.

 

[Galaxiom]

Gemma is right that this is how it really should be done, but in my experience, this has been one of the hardest bits of normalized database design for people to intellectually accept and then implement.

Well I am guilty there for sure. I can see your point and realise now that my treasured integrity check is nothing more than testing the potentially unreliable decremented value against the verifiable derived value.

I had thought since some stock quantity must be recorded at the start there was little to gain by deriving it and at the cost of quite a lot of calculation.

Now I understand better I would be inclined to reinitialise the stock quantity during the end of day routines so that the calculation only looked back over one day's stock movements.

 

[Chaz]

Thanks. Understood both views.

Assuming Gemma's is the best - how do you start with nothing. Surely a starting value is saved? Then again - this could be 0 and then stock of 50 is added as a purchase and its worked from that?

Secondly - can anyone help me with a 2 table, 1 form database or point me to one that might exist to get an idea on how to go about doing this? Ill upload something later but thus far havent been able to get it to work.

Was down with flu on the weekend so didnt get much done in fairness.

Thanks all once again for your time.

Regards,

 

[Chaz]

Well I am guilty there for sure. I can see your point and realise now that my treasured integrity check is nothing more than testing the potentially unreliable decremented value against the verifiable derived value.

I had thought since some stock quantity must be recorded at the start there was little to gain by deriving it and at the cost of quite a lot of calculation.

Now I understand better I would be inclined to reinitialise the stock quantity during the end of day routines so that the calculation only looked back over one day's stock movements.

On that thought - is it possible for MS Access to do 'daily' activities like a Cron job? Perhaps im niave, but cant see how MS Access can do anything unless its open and running. That doesnt mean that it cant be done perhaps if a working backend was always open?

 

[Galaxiom]

Everyone would have to be out of the system or the update could get messed. You would just run a query with the calculation and update the stock figure.

Certainly Access has to be open and running but I wouldn't be inclined to let it do something like this unsupervised anyway. Would be sad to come in next morning to a disaster.

You can set a macro to run automatically on startup. Just call it autoexec.
Access can be run by the Windows scheduler. You would have a front end devoted to the automated tasks.

 

[DCrake]

Remember some systems allow you to have zero stock in hand and still deduct a quantity from it. How do you ask? Well lets say at some obsure point in time that was overlooked a transaction did not go through. Lets asimilate it to a toffy shop. You EPOS system says you do not have any Mars bars in stock and little Freddy walks up to the counter with one in his hand, you scan the barcode, and by rights the EPOS would then say to itself do I have any in stock? if so, how many is being bought, take this away from what's in stock to give me a new balance. So if I start with 0 I can go under that amount. If the till said sorry you can't buy a Mars bar 'cause there are none in stock it would be lying.

Also items can be deducted from stock totals that do not enter the transaction tables. Items such as:
Display goods
Stolen Items
Faulty stock
etc.

Unless you have a rigorious system in place then stock levels can be quite flexible and unreliable.

David

 

[Atomic Shrimp]

Well I am guilty there for sure. I can see your point and realise now that my treasured integrity check is nothing more than testing the potentially unreliable decremented value against the verifiable derived value.

I had thought since some stock quantity must be recorded at the start there was little to gain by deriving it and at the cost of quite a lot of calculation.

Now I understand better I would be inclined to reinitialise the stock quantity during the end of day routines so that the calculation only looked back over one day's stock movements.

I haven't entirely accepted the idea myself on an emotional level - my background has been mostly in maintaining systems that other people/agencies created, and they did make the choice to store current stock and I still can't help entertaining the idea that if everything breaks, the possibility of grabbing the stock table and starting again might in some cases be a win.

I do know and accept that calculating it is considered the correct way by the more experienced and better programmers - I guess I'm really just a bit hung up on the fear of the system getting a little bit slower every day, as the stock figures require more and more transactions to be summed to derive their value.

Assuming Gemma's is the best - how do you start with nothing. Surely a starting value is saved? Then again - this could be 0 and then stock of 50 is added as a purchase and its worked from that?

Yes, you could store the opening stock value in a field in your products table, with a default value of 0. Then every time you add a product you have to perform a transaction to add stock - which is a good idea anyway - anything that changes stock in any way should be a recorded transaction.

Remember some systems allow you to have zero stock in hand and still deduct a quantity from it.

I've seen this behaviour in both kinds of system - in systems where stock is a stored value, it was achieved either by allowing the stock to go into a negative state (which frightens some people), or by performing an automatic adjustment transaction to put the item in stock before selling it (as and when the actual cause of the stock discrepancy is understood, it can be corrected and the adjustment transaction reversed).


One distinct advantage of calculated stock is ease of calculations for stocktaking. If you know that stock of any item at any time is composed of opening stock plus or minus any transactions up to that moment, it means you don't have to store a 'snapshot' of the stock at the precise moment of stocktake. It also means that if you find discrepancies at stocktake that turn out to be the result of, say, a booking in error, you can just post a corrective transaction back to the right date and leave the physical stock count data alone.

 

[neileg]

One distinct advantage of calculated stock is ease of calculations for stocktaking. If you know that stock of any item at any time is composed of opening stock plus or minus any transactions up to that moment, it means you don't have to store a 'snapshot' of the stock at the precise moment of stocktake. It also means that if you find discrepancies at stocktake that turn out to be the result of, say, a booking in error, you can just post a corrective transaction back to the right date and leave the physical stock count data alone.

I agree 100%. In addition, it forces the user to enter a transaction to correct the balnce and record a reason. The reason may be 'unkown difference', but it gives management information on the perfomance of that part of the operation becasue you have a record of both the volume and cause of the corrections.

If your system is so inefficient that it can't calculate stock balances on the fly, you need a better system!

 

[Atomic Shrimp]

If your system is so inefficient that it can't calculate stock balances on the fly, you need a better system!

For some systems, it could be the sheer number of transaction records per item that can impact performance - the overall database size and scale might still be small, making it hard to argue for an upgrade to a proper RDBMS - for example, recording sales of a range of only five different products, but each with sales of a multiple hundreds of items per day could still end up being clunky on the stock calcs.

I guess if it's acceptable to reconstitute the opening stock periodically though, it's not a problem.

 

[Chaz]

In fairness, we dont generally do large stock volumes so dont expect any calculation issues. I only need to figure out how to do it, theres the challenge ;p

Im glad I inspired this great debate Pity I dont know the answer yet .

 

[gemma-the-husky]

to extend this a bit

domain aggregate functions are something that dbs's are optimized for, so i would have thought calculating a balances (as opposed to storing it) is something access would be quick at. however, if there are very many transactions, then from time to time, you would discard or flag the historic calculations, and calcluate a new bf balance.

Now I wouldnt be at all surprised if systems DO store the calculated quantity, although it does move the problem, to maintaining the integrity of the calculated figure, given the number of places in which the quantity could change

perhaps it depends just how big your system is, and how many transactions you do have

One last thought - if you are building your OWN system, then you have the opportunity to do it the way you want - if for particular reasons you WANT to denormalise then do so - its just important to realise that doing so may actually make your project harder.

Note also that quantity of stock is one thing - value is another altogether, and inordinately complex

-------
i suspect this is similar to (eg) a sales/receivable ledger - where most systems seem to store an overall control total, indivdual totals for each account, as well as store indivdiual transactions - and they verify that all is in order by a reconciliation check each month/period end