Solved

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

Posted on 2016-10-05
21
62 Views
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:
"SKUs"
"ParentParts"

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).

Example:
"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!

db74.jpg
0
Comment
Question by:Dustin Stanley
  • 11
  • 8
  • 2
21 Comments
 
LVL 57

Expert Comment

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

https://www.experts-exchange.com/articles/19/Expanding-a-Hierarchical-Data-Structure.html

 On how to to a BOM structure.

Jim.
0
 
LVL 34

Assisted Solution

by:PatHartman
PatHartman earned 250 total points
Comment Utility
At its simplest, you need two tables to represent this Many-to-Many relationship which is called a BOM (bill of material).

tblParts
PartID
SKU
Nomenclature
Manufacturer
UnitCost
....

tblAssemblies
AssemblyID
EndItemFlg (used to indicate "top" level assemblies such as a Dell Laptop)
PartID_Parent
PartID_Child
Quantity

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.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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?
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
0
 
LVL 34

Expert Comment

by:PatHartman
Comment Utility
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)
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
Use this link:

  https://rdsrc.us/G5uxuJ

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.
0
 
LVL 57

Accepted Solution

by:
Jim Dettman (Microsoft MVP/ EE MVE) earned 250 total points
Comment Utility
correction:

 http://rdsrc.us/G5uxuJ

Jim.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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

that Dell Laptop can then become AVTEBKR30J7NSOP, AVTEBKR30J7NSSP, AVTEBKR30J7USOP,AVTEBKR30J7USSP.

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.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
That link worked great! THANKS! I was able to download the Sample!
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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.

Jim.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Jim.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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.
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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!
db75.jpg
0
 

Author Comment

by:Dustin Stanley
Comment Utility
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?
76.jpg
0
 

Author Comment

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

Author Closing Comment

by:Dustin Stanley
Comment Utility
OK THANK YOU GUYS FOR EVERYTHING!
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
<<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.

Part/SKU
 "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.
0
 
LVL 57

Expert Comment

by:Jim Dettman (Microsoft MVP/ EE MVE)
Comment Utility
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:

http://www.e-z-mrp.com/

 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"

etc.

Jim.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Entering time in Microsoft Access can be difficult. An input mask often bothers users more than helping them and won't catch all typing errors. This article shows how to create a textbox for 24-hour time input with full validation politely catching …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
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 …

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now