1 table of parts, Need to build relationships between them

[Poagman]

Knowledge:
Novice. Can build basic tables, queries, and forms using point & click options OK. No coding so far.

Source Data:
- One Table with 7900 rows and 20 columns
- List of [PartNumber] and columns of specifications
- Can add column "Reports to" or "Parent of" if needed (would have numerous parts listed)

Goal:
1 - Search by [PartNumber] (I understand concept)
2 - Initiate OpenQuery(?) (I have "on-click" event understood)
3 - That updates sub-form (without manually refreshing whole page)
4 - Containing....
* [PartNumber]'s searched [PartNumber] reports to (Parents)
* [PartNumber]'s searched [PartNumber] consists of (Components, can also be top-level item)
* [PartNumber]'s that report to components (Sub-Assembly)
* I would also like the "Supplier" pulled from the Table to view (easy add)

How to Display: Tabular?
Search: [PARTNUMBER]
Get:
+Parent Items
- Parent [PartNumber]-1 / Description / Supplier
- Parent [PartNumber]-2 / Description / Supplier
+ BOM Items
- Component-1 / Description / Supplier
- Component-2 / Description / Supplier
- Subassembly-1 / Description / Supplier
- Subassembly-1 / Description / Supplier
- Component-3 / Description / Supplier

I don't know where to begin.
Opt-1: Using just one table (the way I know)
I thought having a column that has each included PN separated by commas, then have a DLOOKUP find searched [PartNumber] in that column. Problem: Some part numbers may be part of other part numbers (i.e. 201 is in 510-0201-00). This would may cause incorrect relationships to display.

Opt-2: Create "ID's" to associate items
- Use a 2nd table?
- Create a relationship from my table to itself?
- I don't know what tables to have, how to link, ect...

 

[MarkK]

First, don't rely on complex codes or formatted part numbers like "100-ABC-12345" as keys for storage or relatedness between records. You don't want to have to parse and construct that kind thing for each basic data operation. Each record should have a unique, preferably "Autonumber" ID field.

Then, if one part can only ever have one parent then you can do this . . .

tblPart
PartID (Primary Key (Autonumber))
ParentID (Foreign Key (Matches a PartID in a different record in the same table))
SKU
Description
Etc...

 

[Poagman]

Thank you for replying!!

I do have auto-number key on table
I do have more than one parent for most items (i.e. Muffler can be part of 3 cars or a stand-alone replacement item)

If I had a column referencing the ID# of the parent items I would need query criteria to DLOOKUP the ID as *ID* to find it in the cell since the cell may have numerous ID's, but this causes problems when a part number is within another (201 is part of 52012).

What do you advise?

 

[MarkK]

If one part can have many parents (and it's assumed one parent can have many parts) then you need two tables to model that relationship, which is called many-to-many.

tblPart
PartID (PK)
SKU
Description
Etc...

tblPartPart
PartPartID (PK)
ParentPartID (FK)
ChildPartID (FK)

 

[Poagman]

Thanks again. I really appreciate it and am trying these as we go (not a wasted effort).

I have two tables:
T_PartsMaster:
- ID: PK
- Part Number
- Description
- Ect...

T_ParentChild:
- ID: PK
- ParentPartID
- ChildPartID

Next Steps?
I only linked ID to ID (is that correct?)
I opened new table with ID, ParentID, and ChildID and it is blank.

What next?

 

[MarkK]

I only linked ID to ID (is that correct?)

No. The PartIDs in the ParentChild table are ParentPartID and ChildPartID. Those are the foreign key fields on which we will link Child parts from the Parts table to Parent parts, also from the Parts table. The ID field in the ParentChild table is not related to the Parts table in any way.

Next steps are to create a user interface such that Parent-Child relationships between parts can be assigned by a user, and to do that you create a form based on the ParentChild table. Use a ComboBox on each of the foreign keys, which point you back to human readable information about each part in the parts table. Then, use that form as a subform on the Part form, and depending how you link it, it can show children of the current part (when linked Part.PartID to ParentChild.ParentPartID), or it can show parents of the current part (when linked Part.PartID to ParentChild.ChildPartID). Does that make sense?

 

[Poagman]

I just opened up the T_PartsMaster table and see it has "expanders" to view ParentPartID and ChildPartID on same row.

I really need a Parent category and a Child category that can be expanded separately.

ParentID
+ ParentPartID
- Main Item-1
- Main Item-2
+ ChildPartID
- Child Item-1
- Child Item-2

Should I change to a 3-table setup and link them to the main table?
T_PartsMaster
T_ParentSetup
T_ChildSetup

To enter the hierarchy, do I open the Parent or Child table and add a new ROW with parent ID (have to lookup each time) and then add the related ItemID in the "ChildPartID" field?

i.e. a product with 10 included items will be listed 10 times. Each row with its own included item?

 

[Poagman]

RE: I only linked ID's.
Reply: I linked Primary keys from each table, not parent/child ID (Sorry).

Those are the foreign key fields on which we will link Child parts from the Parts table to Parent parts, also from the Parts table. The ID field in the ParentChild table is not related to the Parts table in any way.

I'm still too new to this to really get what this is saying.
- Should main tables PK link to ParentID and separately to ChildID columns on the new tables or is PK to PK correct?

Next steps are to create a user interface such that Parent-Child relationships between parts can be assigned by a user, and to do that you create a form based on the ParentChild table.

I think I know how to have a subform show query/table values.

Use a ComboBox on each of the foreign keys, which point you back to human readable information about each part in the parts table. Then, use that form as a subform on the Part form, and depending how you link it, it can show children of the current part (when linked Part.PartID to ParentChild.ParentPartID), or it can show parents of the current part (when linked Part.PartID to ParentChild.ChildPartID). Does that make sense?

I know how to make a combo-box using table/query data. The rest is a little unclear still. I have 8,000 part numbers. That could make a long combo-box.

 

[MarkK]

- Should main tables PK link to ParentID and separately to ChildID columns on the new tables or is PK to PK correct?

No, not PK to PK. PK is by definition a unique ID. You want the FK in one table to match the PK in another. The PK is the parent record in a table, and some other related table may have many rows with a matching FK, and those are the child records.

 

[Poagman]

You want the FK in one table to match the PK in another.
The PK is the parent record in a table, and some other related table may have many rows with a matching FK, and those are the child records.

Bada-Bing Baby!!!
That's the missing link I have been stuck on!

PK=Searched part number (resides on the master table)
FK(1)=Foreign table data to show as sub-item for searched part (PK) within subform
i.e. Parts the searched item reports to
FK(2)=Foreign table data to show as sub-item for searched part (PK) within subform
i.e. Parts that report to the searched item.

I'm gonna split up some tables and give this a shot. Hopefully the subform refreshes after I execute the background query on the searched part number (fingers crossed)