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

Posted on 2016-10-05
Medium Priority
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
  • 11
  • 8
  • 2
LVL 58
ID: 41830051
It's not quite what you would think.   Take a look at:


 On how to to a BOM structure.

LVL 40

Assisted Solution

PatHartman earned 1000 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?
NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

LVL 58
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 40

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 58
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 58

Accepted Solution

Jim Dettman (Microsoft MVP/ EE MVE) earned 1000 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 58
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 58
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 58
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 58
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

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

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

Backups and Disaster RecoveryIn this post, we’ll look at strategies for backups and disaster recovery.
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

807 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