Menu
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.
411
First, specify the name of the view that you want to create in the CREATE RECURSIVE VIEW clause. You can add an optional schema-qualified to the name of the view. Second, add the SELECT statement to query data from base tables. The SELECT statement references the view_name to make the view recursive.
A recursive query is one that is defined by a Union All with an initialization fullselect that seeds the recursion. The iterative fullselect contains a direct reference to itself in the FROM clause. There are additional restrictions as to what can be specified in the definition of a recursive query.
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
It is possible in Access to create a query to find the root of your given job. Don't forget the power of VBA functions. You can create a recursive function in a VBA module and use its result as an output field in your query.
Example:
Public Function JobRoot(Id As Long, ParentId As Long) As Long
If ParentId = 0 Then
JobRoot = Id
Exit Function
End If
Dim Rst As New ADODB.Recordset
Dim sql As String
sql = "SELECT Id, ParentID FROM JobTable WHERE Id = " & ParentId & ";"
Rst.Open sql, CurrentProject.Connection, adOpenKeyset, adLockReadOnly
If Rst.Fields("ParentID") = 0 Then
JobRoot = Rst.Fields("Id")
Else
JobRoot = JobRoot(Id, Rst.Fields("ParentID")) ' Recursive.
End If
Rst.Close
Set Rst = Nothing
End Function
You can call this recursive function from your query by using the query builder or by just typing in the function name with arguments in a query field.
It will yield the root.
(I recognize the OP is a year old now, but I'm compelled to answer when everyone says what's impossible is possible).
181
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.
22
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
