Question Calculating stock level

[RonnieODIN]

Hi all,

I am trying to calculate the stock level for my spareparts. I am aware of that I am not to update the stock level in my table but calculate the total from the transactions.

My problem is that I am a newbie and have no idea of where to write the code, what to write etc. :banghead:

I have tried to have a look at both the Northwind database and the allenbrowne example without any further understanding.

I would be very pleased if you could help me out here, thanks.

-Ronnie.

 

[Uncle Gizmo]

I am trying to calculate the stock level for my spareparts.

I have tried to have a look at both the Northwind database and the allenbrowne example without any further understanding.

The Allen Browne Example you mention is HERE:

Inventory Control: Quantity on Hand

I would suggest as a good starting point that you create the tables as shown in the graphic and post the db here.

 

[RonnieODIN]

Here is a database containing the table I've used in my own database. As it is in Danish I added an explanation for the relevant parts in the tables. Hope this helps.



View attachment ODIN Storage 002.accdb

 

[Uncle Gizmo]

Hope this helps.

No it doesn't help, the code is written specifically for the tables shown in the graphic, it will not work in other tables.

A sample DB that uses that code is your best starting point.

 

[RonnieODIN]

Isn't there another way to calculate the stock level? I find it strange if the Allen Browne-method should be the only way to get this right.

I would rather have a solutions that fits my DB than spend a lot of time trying to make my DB fit the Allen Browne solution.

 

[Uncle Gizmo]

Isn't there another way to calculate the stock level? I find it strange if the Allen Browne-method should be the only way to get this right.

I would rather have a solutions that fits my DB than spend a lot of time trying to make my DB fit the Allen Browne solution.

I draw your attention to what Allen says :
One of the basic rules of data normalisation is not to store dependent values. Break the rule at your own peril: it takes more work than doing the job properly; it diminishes the queryability of your data; it leaves you without any certainty that your answer is correct.

You are quite right however, their is always more than one way of skinning a cat. You are quite welcome to find your own way.

I am thinking about the man who invented the square wheel, on being shown the round wheel he said well that's fine but I've got a square wheel, how do I make it work? Now would you like to explain to him how to make the square wheel work?

 

[RonnieODIN]

OK, got the message!

How much am I to alter?

Is it just to split up the two tables for sale and purchese each into the two separate tables or how much work do I have?

 

[Uncle Gizmo]

split up the two tables for sale and purchese

Well no, I don't think "sales" and "purchases" are in any way different in the sense of an item that should be stored in a table, the only thing you would need is an extra field to identify that that particular row was a "purchase" or a " sale"... But really this is a red herring, something to think about later and for now two tables will suffice. This just happens to be a particular pet project of mine, combining the two tables.

 

[Uncle Gizmo]

On viewing your provided database, it's of no help whatsoever, what I said earlier, I would start out by recreating the tables Allen Browne suggests and work from their. That's what I usually do, it's far simpler in the long run.

 

[RonnieODIN]

OK.

Still some questions though;

• Is the function running in the background and updating all the time or is it to be called from somwhere else?
• The "OnHand" value calculated, can I get this value shown in a table or how can I display it?