Question Inventory for a Manufacturing Plant

[xcrx]

I work at a manufacturing plant and I am currently working on getting an inventory system set-up to track parts. I have it all done and functioning except for one part.

My plant builds large farm equipment out of several hundred different parts. My current system keeps track of these parts individually. I have a table with all the parts with how many are used per unit. I have another table with the different equipment that we built. This table is linked to the parts table so each part knows which piece of equipment it goes to.

I need a way for to mark the inventory that has been used. I can do this by individual parts like I am doing now but it causes alot to time to be devoted to marking parts as used. I want to be able to tell access that We build one Trailer Tender for example and have access remove all the parts that go into a Trailer Tender.

Let me know if I missed any information that you may need.

Any Ideas?

 

[Scooterbug]

Off the top of my head, you will need 2 tables. One to list the Items that you build, and one to store the parts for each Item.

tblItemsManufactured
ItemID (AutoNumber, Primary Key)
ItemDesc
Other Item Information

tblItemParts
ItemPartID (AutoNumber, PK)
ItemID (Foreign Key)
PartID (Foreign Key)
Quantity


Also, Inventory should be calculated on the fly, as storing calculated totals can lead to headaches. You should be recording both the Inventory as it comes in and the Inventory as it is being used. You then sum up all the Incoming and subtract the sum of the outgoing.

 

[MarkK]

What'll really bake your noodle though, is the possibility that you manufacture an item--some kind of sub-assembly--which you then inventory and consume as a part.

 

[xcrx]

The inventory is calculated on the fly and my database is set us very similar to how you described. I just can't figure out how to make it subtract all the parts when one item is built. That is the problem I am having.

At lagbolt - that is the next step that my boss wants...

 

[speakers_86]

Maybe you could try this:

tblItems
ItemID
ItemName
Other item info
This is what you build

tblParts
PartID
PartName
Other part info
These are parts that build your items and your components

tblComponents
ComponentID
ComponentName
Other component info
These are components of the items you build; motors, transmissions, etc.

tblComponentBuild
ComponentBuildID
ComponentID (FK)
PartID (FK)
Quantity
These are parts necessary to build your components

tblItemBuild
ItemBuildID
ItemID (FK)
PartorComponentID (FK - You can use an update/append query to keep a separate table of parts and components together.)
These are parts/components necessary to build an item.
Quantity


edit- This assumes that you only have components, no subcomponents.

 

[speakers_86]

The inventory is calculated on the fly and my database is set us very similar to how you described. I just can't figure out how to make it subtract all the parts when one item is built. That is the problem I am having.

At lagbolt - that is the next step that my boss wants...

Use one query to calculate how many of each type of part was purchased. Another to compute number of times built * quantity of each part needed. Another to take purchased-used+growth-shrinkage.

Don't get too far into this without solving the component issue. If you have not considered this issue, your design is probably wrong.

 

[irish634]

Thank heaven's for Fishbowl!



(Disclaimer: I don't work for them nor do I represent them )

 

[xcrx]

Use one query to calculate how many of each type of part was purchased. Another to compute number of times built * quantity of each part needed. Another to take purchased-used+growth-shrinkage.

Don't get too far into this without solving the component issue. If you have not considered this issue, your design is probably wrong.

I think I understand what you are talking about. I am fairly new to access. I will give it a try and see what I come up with.

Thanks