Tree structure in Relational DB design

I need to model a hierarchical, treelike relationship in Access. A simple example would be genealogy...lets say we have the following tables:-

tblPeople
ID Name other data

1      Paul        ...
2      Peter      ...
3      Steve      ...
4      Mary       ...
5      Emily      ...
6      Sarah     ...

tblRelationships
ParentID ChildID

3                    1
3                    5
1                    4
6                    1
5                    2

This seems to me to be a logical, normalised way of structuring the data. My question, however, is how do I write a query that will list the people by generation? So the output I'd like would be Steve or Sarah
Sarah or Steve ... the grandparents (order within the generation doesn't matter)
Paul or Emily
Emily or Paul ... parents
Peter or Mary
Mary or Peter ... children

I guess I need to somehow calculate a generation value and then simply order by that...it would be nice if I could get the database to calculate it instead of asking the user to input it. Or would I be better off with a different database structure? Any ideas please?

Thanks,
Sean Received on Fri Jun 07 2002 - 04:25:11 CDT