Query to show Children

[Guest]

I have a table with three columns:

ID Part NHL
1 30 20
2 40 30
3 50 30
4 60 20
5 20 10

I need a query that will return "children records". For example; for ID 1,
the query needs to return ID 2, 3 & 5. ID 2 & 3 are direct children of ID 1
and ID 5 is like a grandchild since Part 20 is a child of Part 30. I've
tried a union query and a self-join query, but not having any luck. If need
be, I'm not opposed to using code to accomplish this task. Thank you very
much.

Alex

 

[Guest]

ID 2 & 3 are direct children of ID 1
I do not follow your reasoning - what is the relation between these?

 

[Guest]

Thanks Karl. Part is Part Numbers. NHL is Next Level Parts. For every Part
Number, there is a Next Level Part. I need the query to show the Next Level
Part for a Part and then in another column, show the Next Level Part for the
first Next Level Part and so on. So, given the original query I show below,
the query results would look like the following:

ID Part NHL NHL1 NHL2
1 30 20 10
2 40 30 20 10
3 50 30 20 10
4 60 20 10
5 20 10

Thanks again -


Original Query
ID Part NHL
1 30 20
2 40 30
3 50 30
4 60 20
5 20 10

 

[Guest]

Try this --
SELECT Alex.Part, Alex.NHL, Alex_1.NHL AS NHL1, Alex_2.NHL AS NHL2
FROM (Alex LEFT JOIN Alex AS Alex_1 ON Alex.NHL = Alex_1.Part) LEFT JOIN
Alex AS Alex_2 ON Alex_1.NHL = Alex_2.Part
GROUP BY Alex.Part, Alex.NHL, Alex_1.NHL, Alex_2.NHL;

Alex_1 and Alex_2 are the same table added to the design grid multiple
times. Access adds a suffix. You might need more if you have more layers.