Guidance on a Parts Tree Database (1 Viewer)

[camerontaylor]

I downloaded a template parts tree database from the internet, just to try to get some intuition about how I might be able to create my own, or potentially modify the one that I downloaded. However, once I opened up the query, it completely goes over my head. I am hoping that someone can try to explain it to me in more layman terms, as I am still fairly new to SQL. I know the basics, but this is just beyond me.

Here is the SQL:

SELECT P1.PartName AS Part, [P2].[PartName]+(" (" & [PS1].[Quantity] & ")") AS Component1, [P3].[PartName]+(" (" & [PS2].[Quantity] & ")") AS Component2, [P4].[PartName]+(" (" & [PS3].[Quantity] & ")") AS Component3, [P5].[PartName]+(" (" & [PS4].[Quantity] & ")") AS Component4, [P6].[PartName]+(" (" & [PS5].[Quantity] & ")") AS Component5, [P7].[PartName]+(" (" & [PS6].[Quantity] & ")") AS Component6, [P8].[PartName]+(" (" & [PS7].[Quantity] & ")") AS Component7, [P9].[PartName]+(" (" & [PS8].[Quantity] & ")") AS Component8, [P10].[PartName]+(" (" & [PS9].[Quantity] & ")") AS Component9, P1.PartNum
FROM Parts AS P1 LEFT JOIN (((((((((((((((((PartStructure AS PS1 LEFT JOIN PartStructure AS PS2 ON PS1.MinorPartNum = PS2.MajorPartNum) LEFT JOIN PartStructure AS PS3 ON PS2.MinorPartNum = PS3.MajorPartNum) LEFT JOIN PartStructure AS PS4 ON PS3.MinorPartNum = PS4.MajorPartNum) LEFT JOIN PartStructure AS PS5 ON PS4.MinorPartNum = PS5.MajorPartNum) LEFT JOIN PartStructure AS PS6 ON PS5.MinorPartNum = PS6.MajorPartNum) LEFT JOIN PartStructure AS PS7 ON PS6.MinorPartNum = PS7.MajorPartNum) LEFT JOIN PartStructure AS PS8 ON PS7.MinorPartNum = PS8.MajorPartNum) LEFT JOIN PartStructure AS PS9 ON PS8.MinorPartNum = PS9.MajorPartNum) LEFT JOIN Parts AS P2 ON PS1.MinorPartNum = P2.PartNum) LEFT JOIN Parts AS P3 ON PS2.MinorPartNum = P3.PartNum) LEFT JOIN Parts AS P4 ON PS3.MinorPartNum = P4.PartNum) LEFT JOIN Parts AS P5 ON PS4.MinorPartNum = P5.PartNum) LEFT JOIN Parts AS P6 ON PS5.MinorPartNum = P6.PartNum) LEFT JOIN Parts AS P7 ON PS6.MinorPartNum = P7.PartNum) LEFT JOIN Parts AS P8 ON PS7.MinorPartNum = P8.PartNum) LEFT JOIN Parts AS P9 ON PS8.MinorPartNum = P9.PartNum) LEFT JOIN Parts AS P10 ON PS9.MinorPartNum = P10.PartNum) ON P1.PartNum = PS1.MajorPartNum
WHERE (((P1.PartNum)=[Forms]![frmPartsTree]![lstParts])) OR ((([Forms]![frmPartsTree]![chkAllParts])=True));

I have attached an image of the design view as well, as I find that that might help. Even if someone can explain what is happening in the first few cards on the left of the design view, it might be enough to trigger my understanding and allow me to understand how it works.

There is a table called Parts containing fields: PartNum, PartName, Cost, Category
There is a table called PartStructure containing fields: MajorPartNum, MinorPartNum, Quantity

Any help is appreciated, I just want to be able to understand the syntax and how everything is connected.

 

[MajP]

Any chance you can link or post that example? That is not a treeview because it is a brute force method. A tree view uses recursive calls. That is just drop the same table as many times as you think you need. However, I do not get their structure of major part / minor part. Not sure why that is not a just a single field. I think I need to see the data. I discuss self referencing and recursive calls here. After the first few threads it balloons into a very involved tree view application.

Hierarchical Data, Recursion, Tree-Views, and a Custom Class to Assist

Would monitor size or resolution have anything to do with it and/or having a modal form? I'll try again when I get back home.

www.access-programmers.co.uk

 

[camerontaylor]

Here is a copy of the database .zip file, which contains the access file. I'll take a closer look into that link and let you know if it helps my understanding. Thanks for responding so quickly!

 

[plog]

I don't know what you don't understand, so I will explain it very simply--it is not meant as talking down to you (nor was this sentence):

This database can hold an unlimited number of parts that can be comprised of other parts in the database. So, PartZ could be made of 5 PartW, 1 PartQ and a PartN. And PartW could be made of 2 PartJ and 1 PartF; PartQ could be made of 3 PartB and a PartA. And so on and so on until you reach part bedrock--at some point a part isn't made of any other parts and exists by itself. This query makes it seem that the furthest that Part trail can go is 10 parts deep. If it were to go further another P and PS table would be need be added to go deeper.

This query just lists what parts go into what parts and in what quantities.

PartZ: Door Set, made of 3 PartW, 1 PartX, 1 PartJ
PartW : Hinge Set, made of 2 PartR, 1 Part K
PartR: 1/2 inch screw
PartK: Hinge
PartX: Door Knob
PartJ: Door

 

[camerontaylor]

Thank you @plog for your speedy and efficient reply. That has helped my understanding significantly, I guess sometimes its just helpful to have a second voice who understands this stuff better to explain it. The likelihood that I would want to expand it is small, but none-the-less, it's good to know that the option is there. Thanks again!

 

[MajP]

This is basically a self referencing table but using an intermediary table. By self referencing the child field exists in the same table

Here is a simpler version to get the concept, but the idea is the same as the one you provided.
Here is the parts table
tblParts tblParts

PartID	PartName	ParentPart
1​	MainPart	​
2​	SubPartA	1​
3​	SubPartB	1​
4​	SubPartC	1​
5​	SubSubPartAA	2​
6​	SubSubPartAB	2​
7​	SubSubPartAC	2​
8​	SubSubPartBA	3​
9​	SubSubPartBB	3​
10​	SubSubPartCA	4​

As you can see the Main part does not have a parent. SubParts A,B,C belong to Part 1. SubSubParts AA, AB, AC belong to Part 2, and so on.

In this case you know how many levels. But as long as you make more levels than you need you are OK.
1. Drop the table in the query for as many levels (or more) than needed. In this case 3 levels, and I aliased the table

2. Then create the query

3. Results
Query1 Query1

MainPart	SubParts	SubSubPart
MainPart	SubPartA	SubSubPartAA
MainPart	SubPartA	SubSubPartAB
MainPart	SubPartA	SubSubPartAC
MainPart	SubPartB	SubSubPartBA
MainPart	SubPartB	SubSubPartBB
MainPart	SubPartC	SubSubPartCA

4. Once you put the above in a report you can put each field in its own header to get the effect
MainPart
---------- SubPartA
---------------------SubSubPartAA
---------------------SubSubPartAB
---------------------SubSubPartAC
------------SubPartB
......

This works fine for small trees and simple calculations. If you want to put in a treeview you can do a lot more. The class I provide makes it real easy to make a tree out of any table. If you start calculating totals within a tree there are lots of recursive examples on this site.

There is one more trick. For the very first table you need to put a filter. As you see I put "where ParentID is Null" to return the top level. In you example you pick the filter from the form which then makes that the top level and shows all the children.