I want to build a database for production.  I have 5 tables:



Primary Tables:

1. Materials

MaterialID (Primary Key), MaterialName, Price, ReorderQuantity

2. Products

ProductID (Primary Key), ProductName, ProductPrice

3. WorkOrder

WorkOrderID (Primary Key), WorkOrderDate



Join Tables:

4. BOM --> Bill of Materials

ProductID, MaterialID, Quantity (with ProductID and MaterialID as the 

primary key)

5. WODetails

WorkOrderID, ProductID, WODQuantity (WorkOrderID and ProductID as PK)



Relationships:

Material --> BOM (1 to many), Products --> BOM (1 to many), WorkOrder --> 

WODetails and Products --> WODetails (1 to many) All relationships are 

using Enforce Referential Integrity with Cascade Update and Delete Records.



Problem:

I want to track the material usage that should be automatically computed 

through products quantity in work order times the material needs in BOM 

for a specific product. I need to know the materials on hand (assuming 

that once work order is made, then the materials are automatically 

deducted).  I tried to build one more table to store the transaction 

details, but I can't figure out how to automatically insert records based 

on products selected on workorder and quantity in the bill of materials.

1. Do I need to create one more table to store this information? 

2. Is the relationship correct?

3. Where should I store the beginning inventory balance? (In the materials 

table or separate table?)

4. How do I design the form in order to do this?



Thanks a bunch... any advice would be appreciated!



-Gege-