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!

[June7]

I think manufacturing (product assembly) is one of the more complex database structures (family tree is another) to tackle. Has been discussed numerous times in this forum.

Review https://www.accessforums.net/databas...oin-28334.html