I am trying to develop a database but I'm not sure if I have it designed correctly.
So far I have 4 tables.
tblBOM
- kNum (primary key, autonumber)
- BOM_Num (text)
- Rev (text)
- Rev_Date (short date)
- Comments (text)
tblJunction_BOM_Comp
- kNum (primary key, autonumber)
- BOM_FK (long int)
- Comp_FK (long int)
tblComponents
- kNum (primary key, autonumber)
- PartNumber (text)
- Description (text)
- Value (text)
- Comments (text)
tblRefDes
- kNum (primary key, autonumber)
- RefDes (text)
- Comments (text)
- RefDes_id (long int).
I have the following relationships set.
One-to-Many from tblBOM (primary key) to tblJunction_BOM_Comp (BOM_FK).
One-to-Many from tblComponents (primary key) to tblJunction_BOM_Comp (Comp_FK).
(The above 2 relationships gives me a Many-to-Many relationship between tblBOM and tblComponents.)
One-to-Many from tblComponents (primary Key) to tblRefDes (RefDes_id).
What I am striving for is to make a BOM for widget X (BOM number 1-A) that will contain Component PartNumber 101-001 (a capacitor) and
have multiple RefDes of C1, C2, C3 and C4. My current design will accomodate that. Now I want to add another BOM for widget Y (BOM
number 1-B) that will contain the same Component PartNumber 101-001 but now the component will have RefDes of C9 and C17. This is where
I can't get my thinking straight.
Do I need to modify the design of my database and add another Many-to-Many relationship between tblRefDes and tblBOM or between tblRefDes
and tblComponents?
Also, when I create a data entry form I would like to have displayed on the main form tblBOM.BOM_Num, tblBOM.Rev, tblBOM.Rev_Date, and
tblBOM_Comments. Then in a subform (from tblComponents - datasheet style) I want to display tblComponents.PartNumber,
tblComponents.Value and tblComponents.Description. I want to have the field tblComponents.PartNumber as a list box so when I hit the
dropdown it will display all of the components (which are already in the database) and when I select one of them it will populate all of
the other fields in the subform. I'm not quite sure how to do this. Also, I want to have a subform (from tblRefDes - datasheet style)
below the subform (from tblComponents). That subform would just contain one field - tblRefDes.RefDes and then I could enter in multiple
records of C1, C2, etc.
I know how to create forms with subforms with subforms, but only with One-to-Many relationships between each of them. I don't know how
to do it with Many-to-Many relationships (especially with a list box / datasheet style).
So I figure I first need to get my tables/relationships figured out and then move on to the forms.
I have been searhing forums and doing trial and error for so long now I just wind up going off on tangents.
Kind of like searching the internet for replacement parts for the broken lawn mower and wind up watching videos of raccoons stealing
peoples trash, then you wonder, how did I get here?
If anyone can help it would be greatly appreciated.