Method for Multilevel (cascading) grouping?

[peteywhit]

I need some help with trying to figure out how to group multiple records based on multiple levels of parents.

What I currently have is the original dataset and SQL report are based on the "Parent" column, however the structure gets lost and makes it hard to read as the subgroups of one assembly may be on a different page from the parent assembly.
It looks like this.


I'll try to show the existing situation and what I've done so far with the data and what result I'm trying to achieve.

Existing data structure is as below.

• The ID KEY is just an incremental number in a rolling database. As a new design is entered, it's entire BOM is entered in order based on the ItemKey structure.
• The ItemKey structure actually starts at 1 and then sub levels branch off based on the category the components belong to, but I filter it down to this level based on user selected "Assemblies" and "Subassemblies".
• We group the Parent fields but this leads to the poor structure in the report as parent components won't be listed directly with their sub components.

ID KEY	Design	ItemKey	ItemNo	Article No	Material	Description	Variable	Quantity	UOM	Parent	ParentQuantity	BOMComponent	Total Qty
11115278	CS01060	1.2.1	1	1LUC000058EWX	REFERENCE	WINDING VISUAL		3	PC	1LUC002738AAX	1	Active_Part	3
11115279	CS01060	1.2.2	2	1LUC002739AAX		CORE CLAMP ASSY		1	PC	1LUC002738AAX	1	Active_Part	1
11115280	CS01060	1.2.2.1	1	1050013126	REFERENCE	BOLT - HEX GRADE 8		6	PC	1LUC002739AAX	1	Active_Part	6
11115281	CS01060	1.2.2.2	2	1050021026	REFERENCE	BOLT-HEXHD_GR5_PLAIN	0.63-11UNCx2.00in	16	PC	1LUC002739AAX	1	Active_Part	16
11115282	CS01060	1.2.2.3	3	1060001019	REFERENCE	WASHER-PLAIN_0.63 NOM	1.31x0.66x0.09	16	PC	1LUC002739AAX	1	Active_Part	16
11115283	CS01060	1.2.2.4	4	1064238008	REFERENCE	BELLVILLE SPRING WASHER		6	PC	1LUC002739AAX	1	Active_Part	6
11115284	CS01060	1.2.2.5	5	1230144002	1040059006	COVER PLATE-FLTCHPLT	T:0.375 5.00X5.0	12	PC	1LUC002739AAX	1	Active_Part	12
11115285	CS01060	1.2.2.6	6	1230155002	1040057012	STIRRUP-YOKEBAND LARGE		8	PC	1LUC002739AAX	1	Active_Part	8
11115286	CS01060	1.2.2.7	7	4230173903		JACKSCREW_ASSY-1.25-7UNC		12	PC	1LUC002739AAX	1	Active_Part	12
11115287	CS01060	1.2.2.7.1	1	1060002017	1060002017	NUT-HEX 1.25-7UNC	SAE J429Gr5	1	PC	4230173903	12	Active_Part	12
11115288	CS01060	1.2.2.7.2	2	4230172003	4230172003	JACKSCREW - 1.25-7UNC	1.25-7UNC	1	PC	4230173903	12	Active_Part	12
11115289	CS01060	1.2.2.7.3	3	1LUC009151DCCH	1060007017	NUT-HEX JAM 1.25-7UNC	SAE J429Gr5	1	PC	4230173903	12	Active_Part	12
11115290	CS01060	1.2.2.7.4	4	1LUC070170DAI	4230165008	INSUL-CUP JACKSCREW	PRESSBOARD - FORMED	1	PC	4230173903	12	Active_Part	12

Current Structure Layout	Desired Structure Layout
Level 1 Level 2 Level 2 Level 2 Level 3 Level 4 Level 4 Level 4 Level 5	Level 1 Level 2 Level 2 Level 3 Level 3 Level 4 Level 2 Level 3 Level 3 Level 1 Level 2 Level 3

The only way I can see to obtain the desired structure is by splitting the "Parent" column into multiple columns based on the level that row is on.
From this I've arrived at the following subquery.

ID KEY	PrevID	Design	ItemKey	ItemKeyMod	Key1	Key2	Key3	Key4	Key5	Key6	ItemKeyTotal	ItemNo	Article No	Material	Description	Variable	Quantity	UOM	Parent	Parent1	Parent2	Parent3	Parent4	ParentQuantity	BOMComponent	Total Qty
11115278	11115277	CS01060	1.2.1	1	1						4	1	1LUC000058EWX	REFERENCE	WINDING VISUAL		3	PC	1LUC002738AAX	1LUC002738AAX				1	Active_Part	3
11115279	11115278	CS01060	1.2.2	2	2						5	2	1LUC002739AAX		CORE CLAMP ASSY		1	PC	1LUC002738AAX	1LUC002738AAX				1	Active_Part	1
11115280	11115279	CS01060	1.2.2.1	2.1	2	1					6	1	1050013126	REFERENCE	BOLT - HEX GRADE 8		6	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	6
11115281	11115280	CS01060	1.2.2.2	2.2	2	2					7	2	1050021026	REFERENCE	BOLT-HEXHD_GR5_PLAIN	0.63-11UNCx2.00in	16	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	16
11115282	11115281	CS01060	1.2.2.3	2.3	2	3					8	3	1060001019	REFERENCE	WASHER-PLAIN_0.63 NOM	1.31x0.66x0.09	16	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	16
11115283	11115282	CS01060	1.2.2.4	2.4	2	4					9	4	1064238008	REFERENCE	BELLVILLE SPRING WASHER		6	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	6
11115284	11115283	CS01060	1.2.2.5	2.5	2	5					10	5	1230144002	1040059006	COVER PLATE-FLTCHPLT	T:0.375 5.00X5.0	12	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	12
11115285	11115284	CS01060	1.2.2.6	2.6	2	6					11	6	1230155002	1040057012	STIRRUP-YOKEBAND LARGE		8	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	8
11115286	11115285	CS01060	1.2.2.7	2.7	2	7					12	7	4230173903		JACKSCREW_ASSY-1.25-7UNC		12	PC	1LUC002739AAX		1LUC002739AAX			1	Active_Part	12
11115287	11115286	CS01060	1.2.2.7.1	2.7.1	2	7	1				13	1	1060002017	1060002017	NUT-HEX 1.25-7UNC	SAE J429Gr5	1	PC	4230173903			4230173903		12	Active_Part	12
11115288	11115287	CS01060	1.2.2.7.2	2.7.2	2	7	2				14	2	4230172003	4230172003	JACKSCREW - 1.25-7UNC	1.25-7UNC	1	PC	4230173903			4230173903		12	Active_Part	12
11115289	11115288	CS01060	1.2.2.7.3	2.7.3	2	7	3				15	3	1LUC009151DCCH	1060007017	NUT-HEX JAM 1.25-7UNC	SAE J429Gr5	1	PC	4230173903			4230173903		12	Active_Part	12
11115290	11115289	CS01060	1.2.2.7.4	2.7.4	2	7	4				16	4	1LUC070170DAI	4230165008	INSUL-CUP JACKSCREW	PRESSBOARD - FORMED	1	PC	4230173903			4230173903		12	Active_Part	12

I split the "ItemKey" column and create new columns with those split values to then adjust the placement of the "Parent" values under Parent1, Parent2...

The report looks a little better but still an issue with the grouping because of all the empty values in the "Parent1", "Parent2"... columns.


Since the order is correct by default, I need a way to fill in the blanks in these columns with the previous actual value.
For example, all of "Parent1" should be "1LUC002738AAX" because that is the highest level that all other components belong to.
"Parent2" should remain empty as long as there is no value in "Key2", otherwise it should replicate down until the next existing value and continue until all records in the query are filled.
"Parent3" should remain empty as long as there is no value in "Key3", otherwise it should replicate down until the next existing value and continue until all records in the query are filled.
Same for "Parent4" then "Parent5" and "Parent6", even though I don't have all of those columns there yet.

Then I should be able to group by "Parent1", "Parent2", "Parent3" and then "Parent4".
Is there a way to group by more than 4? I think I have a maximum of 6 sub groups in any design.


Thanks for your help!
peteywhit

[ArviLaanemets]

I had to get the Excel report of BOM structure read from ERP program, where the structure was described in a table, where for every part belonging to some BOM list was given only it's parent part (+ some additional info like the quantity, the row number it this part in parent part production order, etc.).

As for about 10 years I work with SQL Server as back-end for any of my applications, I used recursive query there, to convert the ERP structure info into BOM table usable for Excel, like
BomTableID, ProductID, StructureString, StructureLevel, ParentPartID, PartID, PartType, PartUnit, PartQty, ...
, where StructureString was 50 character string composed of 25 (i.e. 25 structure levels) 2-character 32 bit numbers (used a schema which allows the alphabetic order). The reason using 32-bit numbers instead numbers was, that this allowed to have over 1000 components for any part in BOM list at any structure level, and in structures I had to cope with were cases of couple of hunders (i.e. more than 99) components applied.

I.e the product itself had
StructureString = '00', StructureLevel=0, both PartID and ParentPartID = ProductID, and PartQty = 1
The last part used in production had (let's assume id didn't have any components)
StructureString = '01', StructureLeve = 1, PartID=Part1ID and ParentPartID = ProductID, and PartQty = SomeValue
The part before Part had (let's assume this part did have 2 components)
StructureString = '02', StructureLevel = 1, PartID=Part2ID and ParentPartID = ProductID, and PartQty = SomeValue
StructureString = '0201, StructureLevel = 2', PartID=Part21ID and ParentPartID = Part2ID, and PartQty = SomeValue
StructureString = '0202', StructureLevel = 2, PartID=Part22ID and ParentPartID = Part2ID, and PartQty = SomeValue
...

To get a BOM list of specific product, you queried for ProductID, and ordered by StructureString.

Btw, in addition of PartQty, I also caldulated a field ProductPartQty, where all quantityes of parts in structure chain starting from top were taken into account (i.e. when for StructureString = '02' the PartQty=2, and for StructureString = '0202' PartQty=3, then for StructureString = '0202' ProductPartQty=6

[CJ_London]

trying to follow your explanation. My suggestion would be from your subquery table create additional 'Level' columns Level1, Level2 etc for as many levels as you require (perhaps these are your Key columns?). Populate them with the PK (Key ID?) of the item which is at that level. Then using a top down approach with your recursion populate with the appropriate PK so for example

PK	Item	Level	L0	L1	L2
1	Product 1	0	1
2	part 1 (to product 1)	1	1
3	sub assembly 1 (to product 1)	1	1
4	part 2 (to sub assembly 1)	2	1	3
5	part 3(to sub assembly 1)	2	1	3
6	sub assembly 2 (to sub assembly 1)	2	1	3
7	part 4 (to sub assembly 2)	3	1	3	6
8	part 5 (to sub assembly 2)	3	1	3	6
9	part 6 (to product 1)	1	1
10	Product 2	0	10
11	part 1 (to product2)	1	10
12	sub assembly 3 (to product 2)	1	10
	etc

then you just sort on these columns

You may need an additional sorting column(s) if the PK's are not in the order you require (for example you may want to display sub assemblies to the parent before displaying simple parts.

[Gicu]

I suggest you try to follow MajP on the other main Access forum, he is the master of tree view\recursion:
https://www.access-programmers.co.uk...8#post-1913657
Cheers,

[Edgar]

Take a look at the attached file that I created for someone in another forum. This should give you what you need. Let me know if you have issues understanding the approach. There are two forms in the file, one uses a table as source for the tree view, the other uses a query for the tree view. You can adapt it to your dataset. Populating the tree view is very easy, I just use this:

' #############################################################################################
' Email: edgarfreelancing@gmail.com
' Last update: 10/03/2023
'
' Description:
' Populates a TreeView control with hierarchical data from a Recordset without recursion.
'
' Parameters:
'   rs: Recordset containing hierarchical data.
'   tvw: TreeView control to populate (Microsoft treeview is preferred)
'   primaryKeyName: Name of the primary key field in the Recordset.
'   nodeText: Name of the field in the Recordset to be displayed as node text.
'   parentFieldName: Name of the field in the Recordset containing parent node IDs.
'
' Example usage:
' PopulateTreeView myRS, Me.MyTreeViewControl.Object, "EmployeeID", "FullName", "SupervisorID"
' #############################################################################################

Public Sub PopulateTreeView(rs As Recordset, tvw As TreeView, primaryKeyName As String, nodeText As String, parentFieldName As String)

    ' 1st step: add all nodes
    Do While Not rs.EOF
        tvw.Nodes.Add , , "k" & rs.Fields(primaryKeyName).Value, rs.Fields(nodeText).Value
        rs.MoveNext
    Loop
    
    ' 2nd step: assign parents to all nodes
    rs.MoveFirst
    Do While Not rs.EOF
        If rs.Fields(parentFieldName).Value <> 0 Then
            Set tvw.Nodes("k" & rs.Fields(primaryKeyName).Value).Parent = tvw.Nodes("k" & rs.Fields(parentFieldName).Value)
        End If
        rs.MoveNext
    Loop
End Sub

The code just adds all of the nodes and then it gives them parents. You can include an additional column to your data for sorting the records in the way you need.