Hi
I have a table with two columns: Parent_ID and Child_ID
I want to create a query that will expand the tree structure of these relationship. How do I know the depth or how many levels a given Parent_ID will have?
One of my objectives is to obsolete some parents with their children (providing that the children are not part of any other parent's tree structure)

Thanks,
Karim

What do you mean by Levels? Do you mean ChildIDs that might also be ParentIDs? Or do you mean how many ChildIDs to a ParentID? If the latter, just do a Group By query, Grouping on ParentID and COUNTing ChildIDs.

It is not as simple as a group by query
Some children will also be parents and if I want to see a complete structure end to end
So the level or depth means how many parts are involved in one structure. See example below:

Part_1
Part_2
Part_3
Part_4
Part_5

The depth or levels here are 4 an the table will look like this
Parent_ID Child_ID
Part_1 Part_2
Part_2 Part_3
Part_3 Part_4
Part_3 Part_5

What I want is to create a table like this:

Parent_ID Child_ID Depth (level)
Part_1 Part_2 1
Part_2 Part_3 2
Part_3 Part_4 3
Part_3 Part_5 3

How can I create a query that calculates the level and goes through thousands of records?

It sounds like you need a recursive query but I don't believe you can do recursive queries in Access.

If your data resides in some other RDBMS that supports recursive queries you might be better off using a pass-through query to the other RDBMS.

see the comments at this site re recursive function call in vba/access
http://stackoverflow.com/questions/763016/...query-in-access