Multi-Level Query

[Steve Bray]

I have a table that I want to produce a query that will produce a
printable report.

tbl_product

product component1 component2 component3 component4 packaging

I query on a currently selected product easily enough, however, the
vast majority of products have components that in themselves a product.
Hence, a record will exist in the tbl_product table for them to.

I want to produce a query where when any given product is selected it
lists the components but also the components for each the selected
products components and on until all sub-components have been
discovered.

E.g.

Product A is made up of components 1,2,3,4.
Product B is made up of components 1,2,5,6 & 7.
Product C is made up of components 9,10 & 11.

Product B is also component 3.
Product C is also component 5.

I want to produce Product A so I run the query/report and it would
return results like:

To produce Product A you need the following components:

component 1
component 2
component 3
component 4

Component 3 is Product B and requires the following components:

component 1
component 2
component 5
component 6
component 7

Component 5 of Product B is Product C and requires the following
components:

component 9
component 10
component 11

This example was a simple one where it is nested twice - we do have
several where it can nest mutliple times.

I am fairly new to Access/SQL but assume I would need to do a quite
complex query for this - but tbh I would not know where to start.

All help is appreciated!

Steve

 

[Michel Walsh]

Hi,


If you have a fixed number of levels, you can use outer joins. As
example, if the table ParChi (Parent_Children) has two fields, Who, and
Parent then, to reach 4 levels, try:

SELECT ParChi.who, ParChi_1.who, ParChi_2.who, ParChi_3.who
FROM ((ParChi RIGHT JOIN ParChi AS ParChi_1
ON ParChi.who = ParChi_1.parent)
RIGHT JOIN ParChi AS ParChi_2
ON ParChi_1.who = ParChi_2.parent)
RIGHT JOIN ParChi AS ParChi_3
ON ParChi_2.who = ParChi_3.parent;


or switch into graphical representation to be much easier to grab (and to
expand to more levels).


I started with

ParChi
who parent qtyper
1 1 1
2 1 4
3 1 5
4 2 3
5 4 1
6 2 3



and the query returns:

Query50
ParChi.who ParChi_1.who ParChi_2.who ParChi_3.who
1 1 1 1
1 1 1 2
1 1 1 3
1 1 2 4
1 2 4 5
1 1 2 6



Two problems: it works for a limited pre-coded number of levels, and it is
not normal (data is horizontal).


A Nested-Sets approach does not have these two problems, but require some
"pre-compilation".

MS SQL Server 2005 (actually in beta) allows hiearchy search and recursion,
in general, in SQL.


If you are interested in any to these latter two alternatives, and if it
happens that a Google search does not bring your answer, please, be
specific.



Hoping it may help,
Vanderghast, Access MVP