Bill of Materials Query - One source table (so far) 7900 parts, Multiple hierarchies

[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)
3 - 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?

[June7]

Manufacturing type database is fairly common topic. Search forum or Google. Here is one for start https://www.accessforums.net/databas...ign-27791.html

A single table is bad idea. So is multi-value field.

[Poagman]

Manufacturing type database is fairly common topic. Search forum or Google. Here is one for start https://www.accessforums.net/databas...ign-27791.html

A single table is bad idea. So is multi-value field.

I'm not seeing anything on there advising me on what I should have on table-2 or how to log/query the parent/child items?

I was recently advised to setup a 2nd table as seen below, but am stuck now that they are setup and joined by PK's

tblPart
PartID
(PK)
SKU
Description
Etc...

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

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

What next?

[June7]

Maybe. You are linking PartID to ParentPartID and also to ChildPartID?

What do you mean you opened a new table? You opened tblPartPart? Need to enter data into ParentPartID and ChildPartID fields. Select the PartID to populate fields in each record. The linking will allow retrieval of the related part info from tblPart when tables are joined in a query

[rzw0wr]

Please read the following.

http://www.excelguru.ca/content.php?...-cross-posters

Dale

[Poagman]

Maybe. You are linking PartID to ParentPartID and also to ChildPartID?

What do you mean you opened a new table? You opened tblPartPart? Need to enter data into ParentPartID and ChildPartID fields. Select the PartID to populate fields in each record. The linking will allow retrieval of the related part info from tblPart when tables are joined in a query

I was advised to create a 2nd table with a PK/Parent ID/Child ID column and link as seen below.

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)