Inventory

[Sun_Force]

My first post here.
I'm a collage student, having a coarse on databases.
Our professor believes it's better to start with MS Access because the amount of learning tutorials, videos and help resources can be found much more than others.

I've read about the basics of Access databases, normalization etc. Have already written several simple databases in Access.
I've also watched almost all Steve Bishop's videos on youtube (Basics and advanced series).
I'm trying to learn the concepts and correct approaches for an inventory database in Access.
I also have seen Allen Browne's inventory page. His approach is a 10 years old one and I simply thought there may be a better one after so many years (considering Access' 3 new versions with new features since then)

Any reading material, step by step video, sample database or any other resource that you think may help me to have a better vision of an inventory database is appreciated.

 

[arnelgp]

there are Lots of inventory "models" here:

http://www.databaseanswers.org/data_models/

 

[CJ_London]

the principles of inventory management don't really change over time. What changes is the environment- and this can vary from organisation to organisation. One might be retail with all stock on the shelves, another has multiple warehouses whilst a third might also need to track stock in transit. One business philosophy might be 'just in time' another 'stack em high and sell em cheap'. Yet another might just sell product, whilst across the road they have components and products.

So I don't think you will find one 'go to' place for your vision and there is no 'correct approach' specific to inventory. Instead, understand the principles of normalisation, map out the business processes for your particular application and from there you can design your tables and relationships. Once done, you can move on to form and report design.

As an outline, the primary movements are stock comes in, stock goes out. Depending on the requirements these may be stored in two separate tables, or a single transaction table. The fields in that table will depend on what you need to know - where the stock came from, where it went and when would be pretty standard. Also don't forget, transactions need to include things like stock adjustments, (losses/writeoffs/returns etc) so you probably need a transaction type field as well.

These tables would then be linked back to other tables - perhaps a supplier table, a product table, a customer table. Maybe also a location table (might be shelf position, might be property location, might be both).

 

[Sun_Force]

@CJ_London
It seems the road is longer than I had imagined.
Let me ask one question at a time.

Which one of the following two methods is preferable?

1. Having two fields (both positive numbers) one for incoming , another one for outgoing stock
2. Having only one filed, positive numbers shows incoming quantity, negative numbers for outgoing

Thank you,

 

[CJ_London]

It is up to you. having both positive means when you want to sum to get current stock you need a calculation to deduct the outgoing. If using both positive and negative, then, assuming the user is physically entering outgoing stock you need a calculation to convert their positive entry to a negative. If transactions are coming from a file from another system then easy to convert to negative as part of the import process. Personally I prefer the positive/negative approach

 

[Uncle Gizmo]

I also have seen Allen Browne's inventory page. His approach is a 10 years old one and I simply thought there may be a better one after so many years (considering Access' 3 new versions with new features since then)

Having an inventory implies that you will have an audit. One of the main advantages of Allen Browne's code is that it is comprehensive, and handles an audit nicely. I realise this makes it look a bit complicated, but it would be well worth your while studying it and working out what it does. There have been many previous threads in this forum on Allen Browne's inventory code, threads that should help guide you through your study of it.

 

[jdraw]

I agree with the other responses in that Allen Browne's material is quite thorough and the concepts underlying inventory/stock management are quite stable. His is still the "go to example" for inventory management. A search (Google/Bing) will get some samples. Forum posts/dialogs/examples and the Similar Threads often lead to examples from which you may glean some insight.

There is a 26 minute youtube video by Software Matters on Stock Control that may help put some of the pieces into context.

Inventory management is not a trivial application.
See the Database Planning and Design link in my signature for other links to related articles on database and Access.
Good luck.

 

[Sun_Force]

Thanks for all inputs and advices. Each one of them is a great help.

@jdraw it takes a while to go through all the resources you suggested. I'm off to study each one of them and will be back if any more questions.
I really appreciate your help.