I also cannot use sets as per another example in this news group
because updating it causes a lot of problems.

Best regards,
Shripal.

I believe you're going to need to fill a temporary table with the desired
records, and then use a query to show all the records that are in that
temporary table. You can fill the table by running VBA code that 'walks
down' the heirarchy, using the data that are in the temporary table as you
fill it.

MS SQL Server 2005 has proprietary syntax to deal with that kind of problem.
But you said you use Jet.

With Jet, nothing already built-in, but you can do it by yourself in,
basically, 3 ways:

-  a temp table and you fill it with record by recursion from VBA code. Easy
to implement, medium execution speed. Easy to use.

- a query implying the table a finite number of time. Medium to implement,
potentially good execution speed, but limited to the fix number of 'levels'
your query handle, so medium to hard 'to use'.

- nested sets. Harder to implement than any of the other two, easy to use,
can't ask better execution speed.

So, I would try the temp table, and if execution speed is nice for you, will
keep that solution.  If you know you won't have more that 4 generations, as
example, you can use a query like:

SELECT b.boss, c.boss, d.boss, e.boss, e.name
FROM ((((SELECT * FROM mytable WHERE Name ='John' ) AS a
       LEFT JOIN myTable AS b ON b.boss=a.name)
       LEFT JOIN myTable AS c ON c.boss=b.name)
       LEFT JOIN myTable AS d ON d.boss=c.name)
       LEFT JOIN myTable AS e ON e.boss=d.name

Hoping it may help,
Vanderghast, Access MVP