Select all "parents" of a child from self referencing table

[wvandenberg]

I have a self referencing table and I am trying to collect the selected record and all the records that are above it until I reach a ParentID that is null. The child could be at any level.

For example:

pkWaterUnitID,WaterUnit,ParentID
89,Wapasu Creek,79 <---Selected record
108,Wabasca River,NULL <---Selected record
79,Muskeg River,2
2,Athabasca River,NULL

So, of the two records I start with, one record does not have a parent (pkWaterUnitID=108). The other record(pkWaterUnitID=89) has a parent (pkWaterUnitID=79) that also has a parent (pkWaterUnitID=2).

Both the number and depth of the child will differ each time I run the query.

Should I build a select query for each level and then join them all in a union query? Is there a more efficient way? My head hurts %-)

Wendy

 

[MajP]

This works to return all of ID 7's parents. The code is a little sloppy

Basically the function tells if an ID is a parent of a given ID.

SELECT tblWaterUnit.pkWaterUnit, tblWaterUnit.waterUnit
FROM tblWaterUnit
WHERE ((isParent(7,[pkWaterUnit],"tblWaterUnit","pkWaterUnit","parentID")=True));

Public Function isParent(theChildID As Variant, theID As Variant, thetblName As String, theIDFldName As String, theParentFldName As String) As Boolean
  Dim rs As DAO.Recordset
  Dim theParentID As Variant
  Set rs = CurrentDb.OpenRecordset(thetblName, dbOpenDynaset)
  rs.FindFirst (theIDFldName & " = " & theChildID)
  If rs.NoMatch Then Exit Function
  theParentID = rs.Fields(theParentFldName)
  If IsNull(theParentID) Then Exit Function
  If theParentID = theID Then
    isParent = True
    Exit Function
  End If
  rs.MoveFirst
  Do
    rs.FindFirst (theIDFldName & " = " & theParentID)
    If Not rs.NoMatch Then
       theParentID = rs.Fields(theParentFldName)
        If theParentID = theID Then
          isParent = True
          Exit Function
       End If
    End If
  Loop Until IsNull(theParentID)
End Function

 

[wvandenberg]

Thank you, I'll give it a whirl

 

[MajP]

A self referencing table is a good opportunity to use a tree view.

http://support.microsoft.com/kb/209891

I have posted several threads on this topic, if you are interested.

 

[wvandenberg]

I've actually used the treeview extensively in my project thanks to your suggestion in an earlier post. You got me started! Thank you. I've added drag and drop, find and right-click to add/delete node.

Thanks,
Wendy