Inventory Design Question

[emmetm1]

Hello,

I am new to this site so please feel free to direct me where I need to be or correct my posting style. Thank you in advance.

I am tasked with creating the inventory system for the company I work for. For this project I am using Access 2010 in the .mbd file format. I have a pretty good understanding of visual basic, but I have no experience with Access.

I am currently struggling with setting up my tables and relationships. Our inventory is comprised of completed assemblies with their own unique part numbers, sub-assemblies with unique part numbers, and component pieces with unique part numbers.
If I understand correctly there will be a many-to-many relationship because complete assemblies can use multiple components and components go to multiple assemblies. I don’t understand how to factor in sub-assemblies correctly.

I need to keep track of the amount we have in stock at each level, and where something is in processing. For example, let’s say part number 123 is comprised of components, 1, 2, and 3.
I need to know how many completed 123 parts I already have, what components 123 is made up of, and how many components I have in each classification of stock. I also want it set up so that if I process more 123 parts, the components are automatically removed from the system.
Below is a very basic take on what I think my tables need to be. I understand that these tables are almost definitely incorrect but I hope they convey at least a basic understanding of what I am trying to accomplish.

In summary, I need to know how to relate all this information to create a solid base for our inventory.


tbl_completed_parts:
(PKEY) part_number
rev
quantity
notes

tbl_components:
(PKEY) part_number
rev
unit_of_measure
raw_stock
stock_in_process
processed_stock
notes
used_in

tbl_subassy
(PKEY) part_number
quantity
used_in

Thanks!

 

[BigHappyDaddy]

First, I would change up your tables. Your tables need to be normalized or this will cause huge headaches down the road. This is a simple example, but maybe it will get you started:

PK = Primary Key
FK = Forgein Key

tblPartNumber

PartNumber (PK)
OnHand
UnitOfMeasure
​

tblPartNumberAssembly

PartNumber (PK, FK from tblPartNumber.PartNumber)
AssemblyTypeID (FK from tblAssemblyType.AssemblyTypeID)
​

tblAssemblyType

AssemblyTypeID (PK)
AssemblyTypeDescription
​

tblAssemblyComponents

AssemblyID (PK, FK from tblPartNumber.PartNumber)
ComponentID (PK, FK from tblPartNumber.PartNumber)
Quantity
​

First, tblPartNumber will contain all of your part numbers and basic attributes of the part number.

Second, tblAssemblyType contains 3 records (could be expanded). They would be Final Assembly, Sub Assembly and Component.

Next would be tblPartNumberAssembly, which combines tblPartNumber and tblAssemblyType. This allows for a part number to be both a sub assembly and final assembly, if needed.
Example

PartNumber: A - Sub Assembly
PartNumber: A - Final Assembly
PartNumber: B - Final Assembly
​

This could happen if (A) is a final assembly, but is also a sub assembly used in part number B.

tblAssemblyComponents lists all the components of AssemblyID and the count of each component needed to complete the assembly.

This type of approach allows for greater flexibilty than trying to define a table for components, sub assemblies and final assemblies.

 

[gemma-the-husky]

I have to say that if you have no experience of access, you really need some help on this. being good at excel is no help, and may even be a negative influence in some cases.

inventory is one of the hardest things you can do with databases. having a production system to manage assemblies adds to this. no doubt you want to include costs as well.

this is a serious project, and needs a serious budget, and an appreciation that this will take a considerable amount of time to get completed.

i would talk to your bosses, and get some guidelines about their expectations in this area (ie a full project specification, ideally), and then get some professional help.

the size of your business may help a little but no so much.you still need the same functionality irrespective of the size.

 

[emmetm1]

Thanks for the advice BigHappyDaddy. I will try out your suggestion this morning.

gemma-the-husky: It is quickly becoming obvious that this project is much larger and more difficult than I imagined but I am ready to take it on. As long as I can find help in places like this I should be able to make it.

 

[BigHappyDaddy]

Gemma is spot on about the complexity of this type of project. My previous position dealt with exactly the same type of issues you are facing. The difficultly increases with every variable you add.


For example:

• Multiple plant locations
• Component Costs
• Labor costs (for time spent in Subassembly and Final assembly)
• Multiple storage locations of same part number within same plant
• Work in Progress - WIP (sub assemblies / final assemblies not yet completed)

Then to accurately track WIP, you need to tie into the production schedule, order tables, account for any production that doesn't meet quality standards (decrement componets, but don't increase available final assembly count), etc.

These are just the varaiables I could think off in a minute or two. I left that position about 9 months ago, so I know I am forgetting some other considerations.

If you are still willing and able to take on such a huge project, try to get clearly defined expectations from your management. Or this will continue to grow faster than you can say "Scope creep".

 

[Rabbie]

Just to add to the excellent advice you have already been given I would suggest you google Data Normalization to get to grips with that. Also google for Inventory databases to see if you can find a design template.

 

[spikepl]

One other thing - a reality check, if you will. People who discover Access sometimes go off to reinvent the wheel. Specifically, the question is this: What is so unique about your business, among the millions of existing ones, which requires development of a custom inventory system? You think this will be cheap? If the ppl developing and testing do it for free, and are not distracted from anyhting else, then perhaps.

Because another thing which has not been mentioned is that the path from idea to a verified working system is long and full af pitfalls. Thousands have already stepped on all kinds of landmines in that path, so why repeat their experience instead of just reaping the results? What are the requiremenets that you cannot fulfil with commercial systems? Systems that have been tested, and verified, and are documentedd and supported and integrated with ERP, MRP, billing, payments etc etc?

 

[The_Doc_Man]

Spikepl gives excellent advice. Why invent when you can purchase something that gives you 90% of the function for 10% of the cost?

But here is another bit of advice - the "Old Programmer's Rules"

1. You can't do it in Access until you can do it on paper.

Access is an IMPLEMENTATION tool, not a design tool. If you don't have a thorough understanding UP FRONT of what you want to do, you will get lost in the weeds so fast that a machete will be useless.

2. Access can't tell you anything you don't tell it first.

If you wanted to know the dates on which certain parts will probably run out if you don't get more parts in, you have to include dates of consumption of those parts so that you can do time-oriented projections - i.e. consumption rates. Can't do a rate if time isn't there to provide the X axis to go with the part-count that will be your Y axis.

This resource has maybe several thousand posts regarding inventory "do" and "don't" advice. Use the forum's SEARCH feature to see those posts and see what folks often find as a pitfall. The more you read, the more experienced you become. You do know the Ambrose Bierce definition of experience, don't you...? Experience is that which lets you recognize your mistakes when you make them again.

Seriously, this is a more excellent resource than you might imagine because of all the mistakes that show up here, get diagnosed, and and eventually get solved. Don't ignore that aspect of the forum as a valuable reference. Bad case studies make good examples - of what NOT to do.

 

[Lightwave]

emmetm1

The irony of some of the becareful advice?

Most of us were given exactly the same advice when we started out chose to ignore it and went ahead and created our own systems anyway.

Whatever happens trying to create your own is the best way of learning about databases and even if you are not succesful ( and it is important to have a back up plan for this ) you will learn so much that even the purchase of a new system subsequently is likely to be far more succesful as you will have a much better grounding in the problems.

This is a complicated database task that will take time to get right but it is not a complicated software task.

Go for it.

PS you are struggling with the concept of many to many relationships. This is a way of using something called a junction table to create a many to many relationship. A junction table is an ordinary bog standard table but is linked to the parent tables by one to many relationships. In this way it is classic computer science as in a simple relationship one to many is used recursively to create a more complicated relationship. Honestly normalization is very easy once you get the hang of it. Most of the time is spent creating pretty forms that flow.

 

[spikepl]

@Lightwave

..and REAL men catch their own dinner

 

[jdraw]

You have been given a lot of advice here based on experience. If you are working for a company that needs an inventory system and they are knowingly going to have that system created in house by a person who admits to having no database design, operation or maintenance experience, then go for it. I just can't see any viable business undertaking such a project under these conditions.

Recognizing some of the difficulty/complexity of inventory control ( even more so if interfaced with Finance), I would also advise you (or your company's decision makers) to consider your requirements against available commercial packages and do some cost/benefit review.

If the decision is for you to proceed, I would suggest some training in database design, normalization and Access in general. Even contracting a resource experienced and familiar with these subjects to act as a mentor to you and other members of the "data base group" may be a sound approach.

My guess/suspicion is that the full requirements of such an inventory system have not yet been documented and agreed upon. I hope I'm wrong, but yours would not be the first project with these attributes that has passed through this forum.

Good luck with whatever is decided. It will be useful experience.