Inventory- hold stock level or not

[ChrisLeicester]

Hi All

Some may know, I am building my stock inventory database and learning as I go.

I always believed, or read somewhere that good design is not to hold unnecessary data and to let access calculate data as required.

With this in mind, I haven't got a current stock field in my products table, access knows what I have bought and what I have sold, so in theory, I can calculate what my stock should be.

I am also a member of a reddit access group and someone also building their inventory asked how to calculate stock and another user gave them vba to calculate it on each event.

If x is sold then stock = stock minus x, and when y is bought then stock = stock plus Y

This means they keep in their table a field containing running stock levels.

Obviously, no real right or wrong way, and everybody will design their database how they want, but as a general rule, which is the preferred method.

Are there any real advantages to keeping stock levels totaled in a table?

How do the access Guru's prefer to do it.

Thanks

 

[mike60smart]

Hi
I was always directed to this Link

 

[ChrisLeicester]

Hi
Yes, that was always my logic.

 

[Minty]

I think it depends on scale and number of transactions.

If you are only dealing with a few hundred stock movements a month, then you should always calculate.
If you are dealing with tens of thousands then I would probably have a routine to calculate and store it, to keep processing speeds sensible.
Or think about storing your data in a more robust backend, that can calculate the values very quickly.

 

[GPGeorge]

Hi All

Some may know, I am building my stock inventory database and learning as I go.

I always believed, or read somewhere that good design is not to hold unnecessary data and to let access calculate data as required.

With this in mind, I haven't got a current stock field in my products table, access knows what I have bought and what I have sold, so in theory, I can calculate what my stock should be.

I am also a member of a reddit access group and someone also building their inventory asked how to calculate stock and another user gave them vba to calculate it on each event.

If x is sold then stock = stock minus x, and when y is bought then stock = stock plus Y

This means they keep in their table a field containing running stock levels.

Obviously, no real right or wrong way, and everybody will design their database how they want, but as a general rule, which is the preferred method.

Are there any real advantages to keeping stock levels totaled in a table?

How do the access Guru's prefer to do it.

Thanks

There are risks involved in maintaining a running stock total field -- a calculated field that has to be updated constantly. And that opens the door to error and incorrect data.

The alternative, which is the Allen Browne method involving periodic stock takes, has at least two advantages.

1. Calculations involving the stock take can involve fewer records, so queries are faster.
2. The stock take allows you to adjust for non-business changes in stock: shrinkage, damaged goods, unrecorded returns, etc.

In the Northwind Developers Edition template, we incorporated this method. You can see it in action by downloading and instantiating the template.

 

[gemma-the-husky]

There are risks involved in maintaining a running stock total field -- a calculated field that has to be updated constantly. And that opens the door to error and incorrect data.

The alternative, which is the Allen Browne method involving periodic stock takes, has at least two advantages.

1. Calculations involving the stock take can involve fewer records, so queries are faster.
2. The stock take allows you to adjust for non-business changes in stock: shrinkage, damaged goods, unrecorded returns, etc.

In the Northwind Developers Edition template, we incorporated this method. You can see it in action by downloading and instantiating the template.

That would make sense in any event. A stock take adjusts the calculation for any errors, and gives a sensible base point for future calculations.

 

[Pat Hartman]

This means they keep in their table a field containing running stock levels.

Obviously, no real right or wrong way, and everybody will design their database how they want, but as a general rule, which is the preferred method.

most people consider keeping a running total of stock wrong for a variety of reasons. the three most important are
1. there is no way to audit the current stock level
2. it violates second normal form
3. you can't see any history of adjustments so you have no idea how much stock you are losing due to 'shrinkage'

to address minty's comment - i would never use a straight running count under any conditions. if i needed to speed up processing and couldn't move to sql server, i would add summaries at the end of each month but probably with a 2 month delay so that 2 months of detail always remain. then summarize the older data by month and move the details to an archive database. as long jet/ace work as your be, you probably should leave the records as details.

 

[GPGeorge]

Spoiler:

most people consider keeping a running total of stock wrong for a variety of reasons. the three most important are
1. there is no way to audit the current stock level
2. it violates second normal form
3. you can't see any history of adjustments so you have no idea how much stock you are losing due to 'shrinkage'

to address minty's comment - i would never use a straight running count under any conditions. if i needed to speed up processing and couldn't move to sql server,

i would add summaries at the end of each month but probably with a 2 month delay so that 2 months of detail always remain. then summarize the older data by month and move the details to an archive database.

Spoiler:

as long jet/ace work as your be, you probably should leave the records as details.

I like that approach as a more advanced version of a basic Stock Take. Although the Stock Take approach also allows for non-transactional adjustments for thinks things like shrinkage and breakages, etc.

 

[gemma-the-husky]

You don't have to adjust inventory on all items simultaneously. The product can carry a stock take date, and the transaction records can store the opening stock count (and maybe value) for individual items. So you can then sum the movements since the last stocktake for each item.

 

[ChrisLeicester]

That was my view.
I initially wondered if I was missing something when the Reddit group suggested it

 

[gemma-the-husky]

^^^
I don't think you are missing anything. It adds a little complexity but it also allows you to do rolling and selective stock checks.

 

[The_Doc_Man]

When preparing for an inventory, there is always the question of whether you had other things in mind as well. Using a "sum of individual transactions" method, for example, may have to hit a lot of records when in a large business - but those transactions support secondary functions such as analysis of customer preferences, trends in supplier prices, etc. So the question of how you keep track of inventory HAS to include whether you have other ulterior motives besides a simple stock level. I.e. inventory is usually just one FACET of a business. Tracking inventory is hardly ever done in a vacuum.