Tree traversing with recursive code ???

[ksor]

I have MANUALLY made a family tree i Excel like this:



Never mind you can't see the details - you can see a sort of columns representing generations.

The problem is to find of how many generations I need to have and which generation each person
should belong to.

The structure behind the tree is this table:



Where BarnID is an ID for a person and F1, F2 ... is id's for the parents of that person.
Sometimes a person has 1, 2 or 3 or more parents.
If a person has no parents he is NOT listed in the table at all.



I have this recursive code to traverse the tree from right to left starting with an Id.

        
Function findG(tb As DAO.Recordset, Id As Long) As Integer
    ' Using RECURSIVE call in this function
    ' If Id is not found -1 is returned
    Dim F As Long, n As Integer
    Dim G As Integer
    Dim bm As Variant
    G = -1
    With tb
        .Index = "PrimaryKey"
        .Seek "=", Id
        If .NoMatch = False Then
            bm = .Bookmark
            F = 1
            Do
                Id = .Fields("F" & F) ' now take THIS parentId
                n = findG(tb, Id)
                G = G + 1
                If n > G Then G = n
                F = F + 1
                .Bookmark = bm
            Loop Until .Fields("F" & F) = 0
        End If
        findG = G
    End With
End Function

The code is working fine BUT it's returning WRONG generation numbers !

Take the person in the upper right corner - the code runs through all the supposed
branches it should - including the long one which is defining the generation
number - the upper right person should have the generation number 7.

The code is returning 5 because it runs through a "shorter" branch as the
last run !

I know there is much more to do for EACH person to have the rcorrect generation
number but in the first place I need to find the "longest path".

How can I change the code so it can remember the LONGEST PATH through
the anchesters ?

[orange]

I suggest you post a table rather than a jpg of a table.
It would also help readers if you showed the code you are working with; the steps to reproduce what you have and a clear sample of what you want to achieve based on the sample data.

Readers are not familiar with your data.

?? Please explain 3 parents.
There is no BarnID 52???
Does BarnID 97 have only 1 parent???

[ksor]

3 parents .... biological and social mother/father ... maybe merried several times ;-))

I haven't got the data on many of the persons yet ... therefore some parents are missing

Person 52 is not yet registred with parent.

The code will run directly on the table in the immidiate window ... if I only could upload the table

I tried to upload the table as a TXT-file ... but how ?

[June7]

Upload file same way you have in other threads such as https://www.accessforums.net/showthread.php?t=68958

[orange]

Do you have test data to get actual results??
I look at BarnID 26 and see f1(52) f2 (53) f3 (107)

but there is no BarnId 52 in the table???
How is one suppose to read and interpret these f1/f2/f3... values?

[ksor]

3 parents .... biological and social mother/father ... maybe merried several times ;-))

I haven't got the data on many of the persons yet ... therefore some parents are missing

I tried to upload the table as a TXT-file ... but how ?

[ksor]

Here are the table as TXT attached

The last column is the WRONG calculated generation numbers

[orange]

I am unsure how to call your function. I have loaded your data into a table.
Please provide instructions on procedure for setting up to use your function.

[ksor]

Here is another function to open the table and keep record to minimize the overhead for opening tables.

Function GetTable(Name As String, _
                  Optional Index As String) As DAO.Recordset
    ' Maintains a collection of open tables (mcolTables) in order to avoid the
    ' overhead in re-opening the same table at each function call.
    ' is the table in the collection already?
    For Each GetTable In mcolTables
        If GetTable.Name = Name Then GetTable.Index = Index: Exit Function
    Next
    ' if not, open the table, and add it to the collection
    Set GetTable = CurrentDb(Name).OpenRecordset
    GetTable.Index = Index
    mcolTables.add GetTable, GetTable.Name
End Function

Set up the table with primary key on the first field and give it a name MYTABLE.

Insert the 2 functions and compile.

In the immidiate window you can now type

? findG(GetTable("MYTABLE", "PrimaryKey"),26)

and it will return 4 as the generation number - and it's correct ... but by chance !

Try 97 which should be 7, but it returns 5 because it CAN'T remember THE LONGEST PATH
through the tree - that's my original question: What's wrong with my recursive function
since it CAN'T remember that path ?

Be aware that it's very, very frustrating to step-analyze recursive code BECAUSE it's recursive ;-))

[ksor]

Oh, I think I have an error in the TABLE .... Shit !

What I MANUALLY made up in the tree structure is NOT mirrored in the TABLE data - that's why !

So sorry for taken your time !