I am using Access 2007. I have a thread in the Query section because I was having trouble setting up an Append Query. The more I work on it the more I believe my problem is with the table structures I have. So, I am hoping someone can look at this and give me some ideas.
I am working on a database that has Units that are built with parts. I have a table with the various Unit numbers and a table with the various Part numbers. After the Unit is built, I will need to show the Unit that was built, assign it a serial number, and show the parts that were put in the unit. In addition for each unit I have a Bill of Materials that shows all the parts that can go in the unit. Each part in the BOM has a status of Mandatory or Discretionary. I know I need the following:
tblUnits
UnitNum_PK
Unit
tblParts
ParNum_PK
Part
I need a way to show what parts should be used in a unit when it is built. This is the BOM
Example
UnitA
Part123 qty 3 Mandatory
Part456 qty 2 Discretionary
Part789 qty 1 Mandatory
I also need a way to show when a unit is built and what parts went into that unit
So we build UnitA, give it a serial number, and use the following parts
Part123 qty 3
Part456 qty 1
Part789 qty 1
Currently I have
tblBOM
UnitNum_FK
PartNum_FK
BOMQty
ReplacementType_FK (if =1 mandatory, if=2 discretionary)
ReplacementNote
tblUnitAssy
UnitAssyNum_PK
UnitNum_FK
SerialNum
UnitAssyDate
UnitAssyQty
tblPartAssy
PartAssyNum_PK
PartNum_FK
PartAssyQty
Maybe I should split tblBOM into 2 tables with a PK joining the two?
Maybe add UnitAssyNum_FK to tblPartAssy to join the 2 tables?