Inventory issue making subsets that reference the main inventory table
Hopefully someone can help on this question. I have a database with a list of individual parts that I have in stock. I have 400 some records that keeps part number, supplier, amount, reorder qty, type, etc.
What I want to do now is somehow associate those parts with a product. So "Product A" may contain 100 parts. I would like to set up all the parts needed and be able to see what is in stock and what is needed to be ordered. I can easily connect the part to the amount in stock, calculate the difference, etc.
What I need help on is how do I set up the list of parts that are needed? I would like to have a drop down to select from the Inventory table. Not a problem. But how do I enter in multiple parts? Do I need to have a separate field for every part that is referenced from the Inventory table? If so, is there a macro to "enter new part" and have a new blank field, including an amount tab and amount in inventory and amount needed (to order) created?