Tree-Like Query

[philbullock1223]

I have a query issue. I have a data-set that is somewhat tree-like. My data looks something like this:

# | ID | NEXT
01 | Leaf1 | Twig1
02 | Leaf2 | Twig2
03 | Leaf3 | Twig2
04 | Twig1 | Branch1
05 | Twig2 | Branch1
06 | Branch1 | Trunk1

I would like to create a query that will show all "down trunk" records in the data set. In other words, if I run the query on 02 I will get records 05 and 06. If I run the query on 01 I will get records 04 and 06.

I am pretty familiar with queries and SQL statements, but I have not found a way to create a query that will give me these results.

Any suggestions?

Phil

 

[spikepl]

I don't think you can do that in a single query (not in Access SQL, that is).

You can arrange the data http://stackoverflow.com/questions/...-parse-a-flat-table-into-a-tree/192462#192462 or write some recursive VBA .

 

[plog]

What do you want your results to look like? This code:

SELECT YourTableNameHere.ID, YourTableNameHere_1.ID AS Connection1, YourTableNameHere_2.ID AS Connection2, YourTableNameHere_3.ID AS Connection3
FROM ((YourTableNameHere LEFT JOIN YourTableNameHere AS YourTableNameHere_1 ON YourTableNameHere.Next = YourTableNameHere_1.ID) LEFT JOIN YourTableNameHere AS YourTableNameHere_2 ON YourTableNameHere_1.Next = YourTableNameHere_2.ID) LEFT JOIN YourTableNameHere AS YourTableNameHere_3 ON YourTableNameHere_2.Next = YourTableNameHere_3.ID;

Will return results like this:

ID, Connection1, Connection2, Connection3
Leaf1, Twig1, Branch1
Leaf2, Twig2, Branch1
Twig1, Branch1

You can scale it out as far as it needs to go by looking at that query in design view and expanding on the pattern I created. The problem is, you have to account for the longest connection it can't figure it out itself.