MS ACCESS - Hierachical tree sort

Question

I'm struggling with a sorting problem.

I've got a table which is as follows:

aspect_id (int)
aspect_text (memo)
root_id (int) which has as a foreign key a aspect_id

I've got a non cyclic tree with the following dummy data:

aspect_id  aspect_text  root_id 

1          root         null
2          aspect1      1
3          aspect2      1
4          aspect3      2
5          aspect5      4

In the example the data is sorted correctly, in my database its not. I want to sort that it starts at the root element, then finds a child, output that child and does that recursively.

With CTE it is fairly doable. Access doesn't support this. With CTE it would be something like:

WITH aspectTree (aspect_id, root_id, Level#) AS 
(
        Select 
            aspect.aspect_id, 
            aspect.root_id,
            0
        FROM aspect
        WHERE aspect.aspect_id = 44
    UNION ALL
        SELECT 
            aspect.aspect_id, 
            aspect.root_id, 
            T.Level# + 1
        FROM aspect
        INNER JOIN aspectTree AS T 
            On T.aspect_id = aspect.root_id
)
SELECT * FROM aspectTree;

Can anyone help me out?

Answer 1

I don't know if the following will work for you but here you go using Bill of Materials algorithms.

• Bill Of Materials
• BOM, with Joe Celko Nested Sets