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

Posted on 2016-10-05
Last Modified: 2016-11-22
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!

Question by:Dustin Stanley
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 8
  • 2
LVL 57
ID: 41830051
It's not quite what you would think.   Take a look at:

 On how to to a BOM structure.

LVL 36

Assisted Solution

PatHartman earned 250 total points
ID: 41830191
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.

Author Comment

by:Dustin Stanley
ID: 41830708
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?
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

LVL 57
ID: 41830734
<<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.

LVL 36

Expert Comment

ID: 41830739
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)

Author Comment

by:Dustin Stanley
ID: 41830740
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.
LVL 57
ID: 41830746
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.

LVL 57

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
ID: 41830754


Author Comment

by:Dustin Stanley
ID: 41830760
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.

Author Comment

by:Dustin Stanley
ID: 41830768
That link worked great! THANKS! I was able to download the Sample!
LVL 57
ID: 41830769
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.


Author Comment

by:Dustin Stanley
ID: 41830779
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.

Author Comment

by:Dustin Stanley
ID: 41830897
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.
LVL 57
ID: 41830907
<<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.


Author Comment

by:Dustin Stanley
ID: 41830913
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.

Author Comment

by:Dustin Stanley
ID: 41830923
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!

Author Comment

by:Dustin Stanley
ID: 41830953
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?

Author Comment

by:Dustin Stanley
ID: 41830956
Could I use the Assemblies table and say Choose several records from the SKUs Table to create a Kit. Like a repair Kit???

Author Closing Comment

by:Dustin Stanley
ID: 41830996
LVL 57
ID: 41831614
<<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.

LVL 57
ID: 41831637
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"



Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes two methods for creating a combo box that can be used to add new items to the row source -- one for simple lookup tables, and one for a more complex row source where the new item needs data for several fields.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

749 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question