Use an Array to find records in a hierarchy.

[qban21]

I am trying to find the best way to find records in a table that fall within the same hierarchy.

EX.

Update every customer that has a parent of XXX and propagate that down to all the child relationships.

# 100
# 101 (Child of 100) Cust. 102 (Child of 100)
# 120 (Child of 101) # 121 (Child of 101)

I can accomplish using several queries but would prefer to use arrays.

 

[xlbo]

Guess this depends on the relationship from Parent>Child fileds - if it is as simple as they all share the same 1st number then just create a new field using this number and test against that for whether to update or not... Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[TonyJollans]

Hi qban21,

Assuming you are doing this in Access and your Customer Table looks something like this (with a parent ID of zero for the top level):

ID Parent Data
100 0 Whatever data it is that you hold
101 100 Customer Name
102 100 Some other data
120 101 Pink elephants
121 101 etc. etc. etc.

Then the following code should do it for you. The Children routine is just recursively called all the way down the hierarchy. Note that element 0 in the array remains unused - I am sure you can adapt to your precise needs.

Type Hierarchy_Detail
    ID As Integer
    Level As Integer
    Parent As Integer
    Data As String
End Type

Dim Hierarchy() As Hierarchy_Detail

Sub Main()

    ReDim Hierarchy(0)

    Call Children(0, 1)
    
    
    Dim x As Integer
    Dim Msg As String
    
    For x = 1 To UBound(Hierarchy)
        With Hierarchy(x)
            Msg = Msg & .ID & " " & .Parent & " " & .Level & " " & .Data & vbNewLine
        End With
    Next
    
    MsgBox Msg
    
End Sub

Sub Children(Parent As Integer, ByVal Level As Integer)
              
    Dim Details As Recordset
    Dim Selection As String

    Selection = "Select Customer.Id, Customer.Data " & _
                "From   Customer " & _
                "Where  Customer.Parent = " & Parent & ";"

    Set Details = CurrentDb.OpenRecordset(Selection, dbOpenDynaset)
    
    Do While Not Details.EOF
        
        ReDim Preserve Hierarchy(UBound(Hierarchy) + 1)
        
        Hierarchy(UBound(Hierarchy)).ID = Details!ID
        Hierarchy(UBound(Hierarchy)).Level = Level
        Hierarchy(UBound(Hierarchy)).Parent = Parent
        Hierarchy(UBound(Hierarchy)).Data = Details!Data
    
        Call Children(Details!ID, Level + 1)
        
        Details.MoveNext
        
    Loop
    
    Details.Close
    
End Sub

Enjoy,
Tony