I am trying to setup a table for keeping track of which parts come from which items.
Tables involved so far are:
This is part of our operation. We have a item that is a Parent and the parts that come off that Parent are called Children.
Example: a Dell Laptop would be the Parent and the Internal Parts are called the Children. Monitor, Keyboard, HDD, Etc....
I have a table called "SKUs" and that table has a AutoNumber called field called "SkuID".
Each "SkuID" is a record of an item just like above but there is a "SkuID" for both Parent and each Children (Child).
"SkuNm" / "SkuID"
Dell Laptop / 235
HDD / 236
Monitor / 237
Keyboard / 238
Just what I have came up with so far is I made a table called "ParentParts" It has a AutoNumber Field called "ParentPartsID" and a field called "SkuID"
I have made "SKUs.SkuID" a Primary Key and "SkuID.ParentParts" a Foreign Key
I can't have 2 "SkuID" fields in "ParentsParrts" to Link the "SkuID" back to themselves.....So what should I do?
By the way a Parent "SkuID" can have Many Children "SkuID" and the Children "SkuID" can have many Parents "SkuID"
So would this be a Many to Many Relationship with a Conjunction Table?
After this I was needing to run a query or something to get the list of Children "SkuID" for the current Parent "SkuID".
Thank You All!