Search code examples
ms-accessrecursionhierarchy

Is it possible to create a recursive query in Access?

I have a job table

Id
ParentID
jobName
jobStatus

The root ParentID is 0.

Is it possible in Access to create a query to find a root for a given job? The database is MDB with no linked tables. The Access version is 2003. A job can be several levels grand children deep.

Solution

  • No, It isn't. Recursive queries are supported in SQL Server after SServer 2005, but not in Access.

    If you know the number of levels beforehand, you could write a query, but it wouldn't be a recursive one.

    In SQL Server, CTE (An SQL extension) is used for that : see http://blog.crowe.co.nz/archive/2007/09/06/Microsoft-SQL-Server-2005---CTE-Example-of-a-simple.aspx

    Regular SQL however does not have Recursivity support.