Access does not explicitly support recursive queries of *arbitrary* depth --
Oracle is the only relational database I know of that does this (through the
START WITH and CONNECT BY...PRIOR keywords).
However:
1. Access does support recursive queries of *fixed* depth.
For example, suppose you had a table of employees:
Employee ID, Manager Employee ID, Employee Name
1, , Amelia
2, 1, Bob
3, 1, Charles
4, 2, Debra
5, 3, Ed
6, 3, Fred
So Amelia is Bob and Charles' manager, Bob is Debra's manager, and Charles
is Ed and Fred's manager.
To get a list of employees with their manager's name, you might use a query
whose SQL looks something like this:
SELECT
[Employees].*,
[Self].[Employee Name] AS [Manager Employee Name]
FROM
[Employees]
LEFT JOIN
[Employees] AS [Self]
ON
[Employees].[Manager Employee ID] = [Self].[Employee ID]
2. By structuring your data in a certain way (as nested sets), and
maintaining some additional information, you can do recursive queries of
*arbitrary* depth in plain SQL. You can find an Access example at:
http://www.mvps.org/access/queries/qry0023.htm
and an explanation of the approach at:
http://www.intelligententerprise.com/001020/celko.shtml
Otherwise, you would need to consider approach like the one Tom suggested.
