chalange for system analyst (solution for complicated query)?

Question

there is a single table contain:
id, name, referal_id

note: referal_id is referenced to id

sample data:
id, name, referal_id
1 aa null
2 bb 1
3 cc 1
4 dd 2
5 ee 2
6 ff 3
7 gg 3

bb(2) and cc(3) is level 1 referal to aa(1)
dd(4) and ee(5) is level 1 referal to bb(2)

dd(4), ee(5), ff(6) and gg(7) is level 2 referal to aa(1)

the case:
i need a query (in ms access) that show level 1 to level 7 "referal to"
(preferably from level 1 to level unlimited)

sample output:
referal id, id, name, level
1 2 bb 1
1 3 cc 1
1 4 dd 2
1 5 ee 2
1 6 ff 2
1 7 gg 2
2 4 dd 1
2 5 ee 1
3 6 ff 1
3 7 gg 1

thanks

Answer 1

There's a few way this can be accomplished...the SQL only elagent solution is probably what your after, but there are faster and easier ways to create this by creating a tmp table or working table that has a "path" column, and simpling looping 7 times of until no records are found, building up this path.

If this is a actual problem you're attempting to solve, I'd go this route, especially in Access. Performance on the SQL only path could get murderous if the table has any significant amount of data, if it's implemented poorly.