Recursive Query

[Jason]

Does Access 2002 support recursive queries? I am a new
Access user but would like to use one if possible. If
so, what command should I investigate?

Thank you,

Jason

 

[Tom Ellison]

Dear Jason:

If you are using Jet, you would need to write the recursion in a
module. If you are using MSDE, you can use cursors in a Stored
Procedure. In either case, you can store your results in a temporary
table to achieve multi-user independence of the operation.

Does Access 2002 support recursive queries? I am a new
Access user but would like to use one if possible. If
so, what command should I investigate?

Thank you,

Jason

Tom Ellison
Microsoft Access MVP
Ellison Enterprises - Your One Stop IT Experts

 

[Brian Camire]

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.