Recursive query of companies ownerships

[hunsnowboarder]

Hi there Everyone!

I have a relatively simple database and a biiiig problem.

My database consists of three important tables:

1.
Company list


______________
Company_ID
Company_name
Company_status


2.
Owner company
_______________
Owner_ID
Company_ID

3.

Ownership details
__________________
No_ID
OwnerID
Owned company ID
Direct ownership percentage
Indirect ownership percentage


Connections:
table 2. [Owner_ID] is connected to table 3 [OwnerID]
table 1 [Company_ID] is connected to table 3 [Owned company_ID]

Database description:
Basically this is a database which shows ownerships of companies in each other. The problem is that if company B has ownership in company C and company A has ownership in company B then company A has indirect ownership in company C.
And this is what I would like to find out and calculate.

I would like to have a query which shows all the direct and indirect ownerships of a company.
At this stage I have a query which shows all the dircet ownerships. But I would like the query to drill down the search till there is no company having indirect ownership.

I tried to google and I found out that this can be done with VBA and SQL commands but I really do not know how to start on.

I have attached the mdb file for better understanding.

Could someone please just give me some ideas what to do and how to start?

Thank you very much in advance!

[CJ_London]

As you have discovered, you need to use VBA for recursive actions.

Generally the vba is designed for a specific task rather rthan a catchall, so you first need to decide what it is you want to know however the basic structure will be something like

Private Function recAction(coParent as long) as long
if coParent<>0
    ... do something here
    coParent=recAction(coChild)
end if
end function

and needs to be called by another function to seed and tidy up

so say you want a list of companies owned by a parent returned as a string separated by semi colons to use as a valuelist for a listbox you might have something like

option compare database
option explicit

Public Owned as string

Private Function recCompaniesOwned(coParent as long) as long
dim rst as dao.recordset
dim coChild as long
if coParent<>0
    set rst=currentdb.openrecordset("SELECT top 1 C.CompanyName , C.CompanyID FROM tblCompanies C INNER JOIN tblOwners P ON C.CompanyID=P.CompanyID WHERE C.CompanyID=" & owner)
    if not rst.eof then 
        owned=owned & rst!CompanyName & ";" 
        coChild=rst!CompanyID
        set rst=nothing
        coParent=recCompaniesOwned(coChild)
    else
        recCompaniesOwned=0
    end if

end if
end function

Function getOwned(coParent as Long) as string
    Owned=""
    recCompaniesOwned coParent
    getOwned=Owned
end function

and in your calling form you might have

myListbox.rowsource=getOwned(me.companyID)


The code is not tested and is just a demonstration so once written, ensure you put a break in and step through the code until you are happy it is working as required

The above is not complete - it follows companyA>companyB>companyC...

if you want

companyA>companyB>companyC...
.................companyD>companyE>companyF...
.................companyG

you will need to modify the code

[hunsnowboarder]

Dear Ajax!

Thank You so much for your reply. It seems definetelly a starting point for me.

Right now I am just examinig Your code, before I'll start to write it. As I am new to Access and just used VBA for Excel, there are couple of things that I am confused about at this stage. If You have time and will please be so kind and clarify to me these points.

1) I would like the macro to search on ONE selected company. The company is selected by ID in a drop down combo list. In your code:

set rst=currentdb.openrecordset("SELECT top 1 C.CompanyName , C.CompanyID FROM tblCompanies C INNER JOIN tblOwners P ON C.CompanyID=P.CompanyID WHERE C.CompanyID=" & owner)

I see in this line the variable owner. My problem with that it is that I cant find this variable declared in the code. My second problem is that should the selection in the combo list trigger the function? In that case the value of the combolist should be the owner?

2) As I am new to Acces I am not sure where will be the result displayed? In a label? How to attach the result of this query to a label?

Thank You a lot in advance for your help!

[CJ_London]

sorry, typo on my part - 'owner' should be 'coParent'

as to whether the selection in the combo 'should' trigger the function that is up to you - if that is what you want put

myListbox.rowsource=getOwned(me.companyID)

in your combo afterupdate event - change names to suit

in my example, the results are displayed in a listbox called myListbox per the code above

[hunsnowboarder]

Thank You Ajax very much, now it is clearer!

I wrote the code, however I am unable to run it as I get a syntax error on the line:

if coParent<>0

Which is very odd... I can't really find the reason of this error...

[CJ_London]

without seeing your whole code as you have written it and know what the error actually is, regret I can't help - the code I provided is pseudo code to get you on your way

[hunsnowboarder]

Ajax, thank you for your guidance. Luckily I have overcome the problem. I tried to stick to your code so much that I didn't realize there is a missing 'then'.

The code now seems to work (the query part at least works) but there is a stack error (run time error: 28, out of stack space) I get for the line:

coParent = recCompaniesOwned(coChild)

I tried to debug it and placed a watch on the coParent and coChild variables, and it doesn't seem to change, it keeps running with the same variable over and over again.

Here is my full code:

Option Compare Database

Public Owned As String

Private Function recCompaniesOwned(coParent As Long) As Long


Dim rst As dao.Recordset
Dim coChild As Long

If coParent <> 0 Then
    Set rst = CurrentDb.OpenRecordset("SELECT top 1 Vallalatok.Ceg_neve , Vallalatok.Azonosító FROM Vallalatok INNER JOIN Tulajdonos ON Vallalatok.Azonosító=Tulajdonos.VallalatID WHERE Vallalatok.Azonosító=" & coParent)
    If Not rst.EOF Then
         Owned = Owned & rst!Ceg_neve & ";"
        coChild = rst!Azonosító
        Set rst = Nothing
        coParent = recCompaniesOwned(coChild)
    Else
        recCompaniesOwned = 0
    End If

End If
End Function

Function getOwned(coParent As Long) As String
    Owned = ""
    recCompaniesOwned (coParent)
    getOwned = Owned
End Function

I also attached the file if you would like to open it.

Thank you again for Your help!

[CJ_London]

hmm, don't have time myself so you'll have to keep playing with it. I suspect the line you have identified needs to be elsewhere and/or you need something to reverse the recursion - try googling 'vba recursive functions' or similar for other suggestions

[hunsnowboarder]

Thank you for your help.

I am making progress, I alredy can display the first find of the dataset. However I am stucked as now I would need to iterate through all the companies and find their children as well, and then iterate throught the children and so on...

So now I am at the recursive iteration but I am stucked...

This is how my code looks now:

Option Compare Database
Public Owned As String
Private Function recCompaniesOwned(coParent As Long) As Long

Dim rst As dao.Recordset
Dim coChild As Long

If coParent <> 0 Then
    Set rst = CurrentDb.OpenRecordset("SELECT Tulajdonlas.Ceg_ID, Vallalatok.Ceg_neve, Tulajdonlas.Közvetlen FROM Vallalatok INNER JOIN (Tulajdonos INNER JOIN Tulajdonlas ON Tulajdonos.Azonosító = Tulajdonlas.Tulajdonos_ID) ON Vallalatok.Azonosító = Tulajdonlas.Ceg_ID WHERE Tulajdonos.VallalatID=" & coParent)
    If Not rst.EOF Then
        rst.MoveFirst
        Owned = Owned & rst!Ceg_neve & ";" & rst!Közvetlen & ";"
        coChild = rst!Ceg_ID
        Set rst = Nothing
        coParent = recCompaniesOwned(coChild)
    Else
        recCompaniesOwned = 0
    End If
End If
End Function
Function getOwned(coParent As Long) As String
    Owned = ""
    recCompaniesOwned (coParent)
    getOwned = Owned
End Function

[CJ_London]

sorry - really tied up with work at the moment - in outline you need to add a way to 'back up' and then go down the next path.

I've dug out some code I've used in the past to build a tree which you can adapt - it does different things to what you require, but you can follow the logic of the process path - this the equivalent of recCompaniesOwned

Private Function BuildNodes(TreeID As Long, tblName As String, Optional ProgressBox As Control)
Dim SQLStr As String
Dim rst2 As Recordset
    While TreeLevel <> 0 'LftRgt < MaxLft
        'find children at next level
        SQLStr = "SELECT TOP 1 [" & tblName & "].EntityPK FROM (dtaRelationships INNER JOIN [" & tblName & "] ON dtaRelationships.EntityFK = [" & tblName & "].ParentFK) LEFT JOIN dtaRelationships AS dtaRelationships_1 ON [" & tblName & "].EntityPK = dtaRelationships_1.EntityFK WHERE dtaRelationships.[TLevel]= " & TreeLevel & " AND dtaRelationships_1.EntityFK Is Null"
        Set rst2 = CurrentDb.OpenRecordset(SQLStr, dbOpenSnapshot)
        LftRgt = LftRgt + 1
        If LftRgt Mod 100 = 0 Then
            If Not ProgressBox Is Nothing Then ProgressBox = Replace(ProgressBox, "Processed " & OldValue, "Processed " & LftRgt)
            OldValue = LftRgt
        End If
        If Not rst2.EOF Then
            SQLStr = "INSERT INTO dtaRelationships ( EntityFK, Lft, TLevel, TreeID) VALUES (" & rst2!EntityPK & ", " & LftRgt & ", " & TreeLevel - 1 & ", " & TreeID & ")"
            
            'sqlStr = "INSERT INTO dtaRelationships ( EntityFK, Lft, TLevel, TreeID) SELECT EntityPK, " & LftRgt & ", " & TreeLevel - 1 & ", " & TreeID & " FROM [" & tblName & "] WHERE EntityPK = " & rst2!EntityPK
            CurrentDb.Execute SQLStr, dbFailOnError
            TreeLevel = TreeLevel - 1
        Else
            SQLStr = "UPDATE dtaRelationships SET rgt= " & LftRgt & ", TLevel = -TLevel WHERE TLevel=" & TreeLevel
            CurrentDb.Execute SQLStr, dbFailOnError
            TreeLevel = TreeLevel + 1
        End If
        
        Set rst2 = Nothing
    Wend
    
End Function

and this the equivalent of getowned

Public Sub BuildNSM(Optional tblName As String = "dtaEntities", Optional ProgressBox As Control)
Dim SQLStr As String
Dim nsm As DAO.Recordset
Dim TreeID As Long
    'set the stage
    
    UpdateProgress "    Clearing Relationships" & vbCrLf, ProgressBox
    CurrentDb.Execute ("DELETE * FROM dtaRelationships")
    
    MaxLft = 2 * DCount("*", tblName)
    d = Now()
    
    On Error Resume Next
    UpdateProgress "    Start: " & MaxLft & "  " & Format(Now(), "hh:mm:ss") & vbCrLf, ProgressBox
    OldValue = LftRgt
    d = Now()
    
    'set ultimate parents
    Set nsm = CurrentDb.OpenRecordset("SELECT EntityPK FROM [" & tblName & "] WHERE ParentFK=0", dbOpenSnapshot)
    LftRgt = 1
    TreeID = 1
    While Not nsm.EOF
        TreeLevel = -1
        SQLStr = "INSERT INTO dtaRelationships (EntityFK, Lft, TLevel, TreeID) VALUES(" & nsm!EntityPK & ", " & LftRgt & ", " & TreeLevel & ", " & TreeID & ")"
        CurrentDb.Execute (SQLStr)
        UpdateProgress "    Processed " & LftRgt & vbCrLf, ProgressBox
        OldValue = LftRgt
        BuildNodes TreeID, tblName, ProgressBox
        TreeID = TreeID + 1
        nsm.MoveNext
    Wend
    UpdateProgress "    Finished: " & Format(Now(), "hh:mm:ss") & "  Elapsed time: " & Format(Now() - d, "hh:mm:ss") & vbCrLf, ProgressBox
    
    Set nsm = Nothing
    
End Sub

[rpeare]

One word up front, don't use multi value fields (status field in table Vallalatok, that will cause you no end of grief in the long run if you are doing anything with that field other than just using it as reference)

I'm curious about this and I am just picking it up so let me as a very direct question, are you performing the recursive search on every owner of a company regardless of whether they are direct or not or only for the indirect owners?

Let's say you choose Company A, based on your example data what would you expect to see as a result of your recursive search because all three owners are direct owners? Please be explicit in what you would expect to see (list the records)

What about company B where the same person has both direct and indirect ownership of the same company?

Also which form are you running your code on?

[hunsnowboarder]

Thank you for your comment.

One word up front, don't use multi value fields (status field in table Vallalatok, that will cause you no end of grief in the long run if you are doing anything with that field other than just using it as reference)

I am using this field only to show the status of a company. I am not doing any query on it, I just display it. Probably I will delete it because of the problems it might cause.

I'm curious about this and I am just picking it up so let me as a very direct question, are you performing the recursive search on every owner of a company regardless of whether they are direct or not or only for the indirect owners?)

Yes. My final goal is to produce a table which shows all the direct and indirect connections of a company. For the direct ones, it was easy by making a simple query. Now I am trying to solve for the indirect connections. Once I solve it I would like to also have a field which calculates the indirect ownership of a company.
Very basic example: There are four companies. McDonalds, Burger King, KFC and Pizza Hut. Let's say McDonalds has 20% of shares (ownership) in Burger King. Burger King has 50% of shares (ownership) in KFC and 30% in Pizza Hut.
Let's do now a search on McDonalds.

McDonalds has one direct ownership and two indirect ones.
1) McDonalds => direct ownership => Burger King => ownership percentage: 20%.
2) McDonalds => indirect ownership => KFC=> ownership percentage: 10% (20% * 50%); indirect ownership route: Burger King => KFC
3) McDonalds => indirect ownership => Pizza Hut => ownership percentage: 6% (20% * 30%); indirect ownership route: Burger King => Pizza Hut


The result I would like to get is a table in the similar format:

Main company: Mc Donalds

Ownership in __Direct ownership__Indirect ownership
-------------------------------------------------------------
Burger_King_________20%__________0%_________
KFC_________________0%_________10%_________
Pizza_Hut____________0%___________6%________


Of course if McDonalds has ownership in other companies than that also should be displayed. And if the other companies have ownership in other companies those companies shoulb be also displayed in the list.

Let's say you choose Company A, based on your example data what would you expect to see as a result of your recursive search because all three owners are direct owners? Please be explicit in what you would expect to see (list the records)

What about company B where the same person has both direct and indirect ownership of the same company?

Please see above.

Also which form are you running your code on?

I am running the code on 'Lekerdezes_urlap'

Thank you for your suggestions.

[hunsnowboarder]

Thank you Ajax.

I will check the code and get back soon.

Thank you again for helping me out with my questions.

[rpeare]

Please use an example from the data you uploaded, not a new example. Or alternately upload a fresh copy with the tables set up to match your example.