MS Access Table Relationship Setup For Keeping Track of Replacement Parts Of a Product Maybe Many To Many Relationship

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!

Dustin StanleyEntrepreneurAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
It's not quite what you would think.   Take a look at:

 On how to to a BOM structure.

At its simplest, you need two tables to represent this Many-to-Many relationship which is called a BOM (bill of material).


EndItemFlg (used to indicate "top" level assemblies such as a Dell Laptop)

Assemblies has two foreign keys to the parts table.  One for the "parent" and one for the "child"  The quantity indicates how many instances of the "child" part are needed for the assembly.  For example, a car has four wheel assemblies.
Dustin StanleyEntrepreneurAuthor Commented:
Ok I am about to test this out PatHartman. One question or clarification first. I create these tables how would I link them back to my SKUs Table SkuID field. Basically back to the original table and products. Would that be the SKU field you spoke of in the first table...Back to my SkuID field in the tables SKUs?
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Ok I am about to test this out PatHartman>>

 Did you read the article?  it has everything you need, including a sample DB with code.

TblParts may not be new.  It might be your SKU table.  It is the assemblies table that you would need to build.  I couldn't figure out from the diagram what table or tables it would replace.

You probably need to separate what you "sell" from "how you build".  Are SKU's what you sell?  Then you might need a table that contains just the detail parts.  Then assemblies would join SKU to detail part rather than SKU to SKU.  If both the end items and detail parts are all in the same parts table, then you can use the EndItem indicator in the Parts table (sorry, i put it in the wrong table in the example above)
Dustin StanleyEntrepreneurAuthor Commented:
Well I read the beginning as I don't have a paid membership but it looks VERY INTERESTING as in where I am heading later. Funding is not my friend right now. I planned on subscribing later and reading it all.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
Use this link:

and if that doesn't work, I'll get the sample DB for you.  I can't believe they have it behind the paywall.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dustin StanleyEntrepreneurAuthor Commented:
OK I will explain this the best I can.....

SKUs are items or what most people would call products but we don't call them products YET!

Every SKU has 1 OF 4 conditions that we give to it. NSOP, NSSP, USOP, USSP basically new or used.

Every SKU can have one of each Condition.

Once we grade it with a condition it becomes a "PRODUCT"

SO lets say we have a Dell Laptop SKU  AVTEBKR30J7


Those are all 4 different PRODUCTS and all the same SKU....
We don't necessarily Assemble items as much as we dissemble Items.

We like to know when a SKU can be a parent and there are Children SKUs we can sell separate.

I hope that makes sense.
Dustin StanleyEntrepreneurAuthor Commented:
That link worked great! THANKS! I was able to download the Sample!
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and by the way, you can earn membership by answering a few questions a month if that's of any interest.

Also just a note; that article has been free for many, many years.   I'm not sure at what point it was put behind the paywall, but I will check into that with EE.

and regardless of that, it should have been free had you come in from a Google search, so I have no quams about posting the link.

 Members are also allowed to share links with content to non-members, so why it's behind the paywall I have no idea, but as I said, I will check into that.

Dustin StanleyEntrepreneurAuthor Commented:
I agree. But that is Business. I would answer some questions as soon as I feel comfortable to. This is been a quick hard Crash Course this last month just so I can learn, save money, and have a nicer system to grow with.
Dustin StanleyEntrepreneurAuthor Commented:
If both the end items and detail parts are all in the same parts table, then you can use the EndItem indicator in the Parts table

What would be an example for this. The End ITem and Details Parts are all in the same table as they both are SKUs. So Would I just say 0,1,2,3 ETC....

That article was amazing and I am going to be working on that one. As that will be very helpful. For now a "Get done quick and easy to hold me over until then" I am going to try this Assemblies table technique.
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<I am going to try this Assemblies table technique.>>

 It's the same technique.   You have a parts table and a BOM table.   Parts table is all the SKU's, with description, etc.  

 The "end indicator" is usually in the parts table;  "F"inished goods (a final assembly), "S"ub Assy, "R"aw material, and it can get more complicated then that.   You can have "Floor Stock", "Blow through Parts", "Phantom Assy" for items, etc.   There can be lots to this once you get into it (and depending on what your doing).

 But the BOM table is nothing but a list of:

  Parent    Child 1   QPA
  Parent    Child 2   QPA
  Parent    Child 3   QPA

 'QPA' is Qty Per Assembly and the above represents three records.  BOM's are typically entered "flat" (one level at a time).

 You then use some logic to generate the levels.

 That's why I pointed you to the article and said in my first comment "It's not quite what you would think."

 Doing this is more than just having a table structure.

Dustin StanleyEntrepreneurAuthor Commented:
GOTCHA! Yeah I guess the table setup is just that and the other part of the article is the code to expand and organize it all! I appreciate all the help! I am working on it now.
Dustin StanleyEntrepreneurAuthor Commented:
Ok if you could please look at this photo and tell me some advice. My tables involved here are SKUs, Assemblies, and ParChildDetails. I get that:

SKUs would be on the left
ParChildDetails in the middle
Assemblies On the Right

I am not 100% sure of what fields to put into ParchildDetails but that is on me and I will find out in the future upon what info we need. But anyways would SkuIDParent and SkuIDChild be in the ParChildDetails Table?

Also in ParchildDetails would there be a field for AssembliesID as a Foreign Key?

I just get my mind all bound up!
Dustin StanleyEntrepreneurAuthor Commented:
Hold on a minute. Seeing how we don't build items we just disassemble items. Do I even need the Assemblies table? Wouldn't this work Fine??? Also Would I need the EndItem field?
Dustin StanleyEntrepreneurAuthor Commented:
Could I use the Assemblies table and say Choose several records from the SKUs Table to create a Kit. Like a repair Kit???
Dustin StanleyEntrepreneurAuthor Commented:
Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
<<Hold on a minute. Seeing how we don't build items we just disassemble items. Do I even need the Assemblies table? Wouldn't this work Fine??? >>

The answer is, it depends.   What a BOM represents is the knowledge of what parts go into something.   In this case, a Dell laptop.

So you either have a BOM that has the list of what it has, or a person standing there saying "this came from a Dell laptop, and I can only have one of these"

Given what you've described, I would say yes, you could skip the BOM table for now.

All you need is for the dis-assembler to know to pick the correct SKU from your parts list as "the parent".

<<Also Would I need the EndItem field?>>

 Probably not as everything your dealing with could be considered the same (you don't have raw materials for example).

<<I am trying to setup a table for keeping track of which parts come from which items.>>

  and you would need another table (or two) for this.   "Parts" is the master list of what can be entered.    

  What you need now is the ability to track multiple instances of each part.  Typically this is an inventory "movement" table (one transaction for each in/out of an item) and in addition, you'd want lot tracking on top of that (records that a transaction was composed of specific instances of an item).

For example, I have two disk drives from two dell laptops, both the same model.

They both have the same SKU/Part#, and they both came from the same SKU/Part#.

Lot#      Part        Condition   Disassemble date
1           ABC        NSOP           10/01/16
2           ABC        NSSP            10/04/16

  ABC is the part that represents the disk drive "Seagate ST250" and in this case, also represents that they came from a Dell laptop  (ABC appears as a child item in a assy)

 If you wanted to dispense with the assy table, you could do this:

Lot#      Part        Parent Item  Condition   Disassemble date
1           ABC        DEF                NSOP           10/01/16
2           ABC        DEF                NSSP            10/04/16

'DEF' being a Dell Laptop.

<<Could I use the Assemblies table and say Choose several records from the SKUs Table to create a Kit. Like a repair Kit???>>

 Yes!  That's exactly it.

 "XYZ - Dell laptop repair kit",
 "ABC" - Disk drive Seagate ST250
 "JIK" - Video panel cable
"SCR" - Screw  1x25 mm

and then a BOM:

Parent    child     QPA
XYZ          ABC      1
XYZ          JIK         1
XYZ          SCR       10

"repair kit" has three items; disk drive, video cable, and 10 screws.

Jim Dettman (Microsoft MVP/ EE MVE)President / OwnerCommented:
and FYI, not sure what the goal here is (own use, resell, etc), but you might be better looking at something off the shelf like this:

 That happens to be written in Access, it reasonably priced, and I believe even offers source code now (may be wrong on that),  

 You may want to consider something like this as I think your going to quickly realize that doing a MRP system is no small feat and might take years to do well.

 But if your in it for the challenge/learning, then certainly keep going and ask as many questions as you need.

 Don't move forward though until you have the table design down rock solid.   Create the tables and relationships, enter some dummy data, and see if it answers all the questions you might ask:

"How many seagate ST250's are in stock?"  
"What are their conditions?"
"Which ones came from a Dell XPS laptop"


It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.