pyramid commission

[n8kindt]

hey guys,

i'm using access 2007. i'm not happy with my solution to the following problem so i'm hoping one of you guys has a better idea. my solution would require use of multiple valued fields and i know union queries and multivalued fields don't get along with each other very well. so.....


this is the typical data i'm working with:

Person A

Person B (a recruit of Person A)

Person C (a recruit of Person A)

Person D (a recruit of Person C)

Person E (a recruit of Person D)

(Person A,B,C,D,&E are all recordsets in a table)

this is basically your typical pyramid scheme. let's say PersonA recruits PersonB and PersonC. PersonA receives commission based on what PersonB & PersonC sells and a lower percentage commission of what their recruit(s) sells.

now, if a person recruits 10 ppl, they become a manager. i have created a table with fields for "recruited by" and "manager" for each person. let's assume PersonA is a manager. this works fine under this current scenario. Person D's "recruited by" field would read PersonC and the "manager" field would read PersonA. now here's my main problem. let's now assume that PersonC has just recruited 10 people and is now a manager. under this scenario, PersonD's field for "recruited by" would read PersonC and their "manager" field also will read PersonC. now there is no data to link PersonD and PersonA together even though PersonA still receives commission on PersonD's sales.

see where my problem is? i could create another field and place PersonA in that field but if PersonE were to become a manager, i'd have to create a new field AGAIN. this would occur everytime one of these person's recruits became a manager.

the only thing i can think of is to make the manager field a multivalued field so i can store more than one manager. my thinking is there has to have been someone else who has had to deal with this before (there are plenty of pyramid schemes out there). could someone give me any ideas for a simple and efficient way of tracking recruits, managers, and the base manager?

i would appreciate a quick reply b/c time is a factor in completing this system. we used to use excel to crunch this data but we had to fire the person who did this. we need to have this system going in about a week's time.

[FishVal]

Hi, there.

This is a classical case to use recursive code.
You need to write a VBA function scanning down the tree (let use this commonly accepted (in programming at least) word instead of "pyramid") from a predefined node (person ID in your case).

The logic is described below in pseudocode.

'the function called from query
'it has dblCommisions variable which will be passed by reference
' to recursive procedure to a accumulate commissions sum 
Function GetCommisions(ByVal varPersonID As Variant) As Double
 
    Dim dblCommisions as Double
 
    dblCommisions=0
    'run recursive procedure with initial values
    ScanBranch(varRecruitID, 0, dblCommisions)
    GetCommisions=dblCommisions
 
End Function
 
'recursive (selfcalling) procedure
' iterating through the 1st level recruits,
' calling itself to do the same for each recruit found
Sub ScanBranch(ByVal varPersonID As Variant, _
                                   ByVal intNodesFromStart As Integer
                                   ByRef dblIntermediateSum as Double)
 
    Dim varRecruitID as Variant
 
    'increment distance from start used in commission calculation
    intNodesFromStart = intNodesFromStart+1
 
    Do
        'get the next from 1st level recruits
        varRecruitID=GetNextRecruit(varPersonID)
        If <no more recruits> Then Exit Function
        'add commissions depending on distance from start 
        dblIntermediateSum=dblIntermediateSum + _
             CalculateCommision(intNodesFromStart)
        'do the same for this 1st level recruit to move down the branch
        ScanBranch(varRecruitID, intNodesFromStart, dblIntermediateSum)
    While True
 
End Sub
 

Regards,
Fish

[n8kindt]

that's exactly what i was looking for!! you just made my day. thank you so much!

[FishVal]

that's exactly what i was looking for!! you just made my day. thank you so much!

You are welcome.
Good luck.

[n8kindt]

ok, i still need some help on this one. after looking it over, i realize this is only going to calculate the commission. in addition to this, i need a breakdown of the commissions being distributed in a report form. so, how do i display the entire tree on one report and have the commission earned displayed next to each person's name? keep in mind that i would need to have total all these figures using a sum() function.

[ADezii]

hey guys,

i'm using access 2007. i'm not happy with my solution to the following problem so i'm hoping one of you guys has a better idea. my solution would require use of multiple valued fields and i know union queries and multivalued fields don't get along with each other very well. so.....


this is the typical data i'm working with:

Person A

Person B (a recruit of Person A)

Person C (a recruit of Person A)

Person D (a recruit of Person C)

Person E (a recruit of Person D)

(Person A,B,C,D,&E are all recordsets in a table)

this is basically your typical pyramid scheme. let's say PersonA recruits PersonB and PersonC. PersonA receives commission based on what PersonB & PersonC sells and a lower percentage commission of what their recruit(s) sells.

now, if a person recruits 10 ppl, they become a manager. i have created a table with fields for "recruited by" and "manager" for each person. let's assume PersonA is a manager. this works fine under this current scenario. Person D's "recruited by" field would read PersonC and the "manager" field would read PersonA. now here's my main problem. let's now assume that PersonC has just recruited 10 people and is now a manager. under this scenario, PersonD's field for "recruited by" would read PersonC and their "manager" field also will read PersonC. now there is no data to link PersonD and PersonA together even though PersonA still receives commission on PersonD's sales.

see where my problem is? i could create another field and place PersonA in that field but if PersonE were to become a manager, i'd have to create a new field AGAIN. this would occur everytime one of these person's recruits became a manager.

the only thing i can think of is to make the manager field a multivalued field so i can store more than one manager. my thinking is there has to have been someone else who has had to deal with this before (there are plenty of pyramid schemes out there). could someone give me any ideas for a simple and efficient way of tracking recruits, managers, and the base manager?

i would appreciate a quick reply b/c time is a factor in completing this system. we used to use excel to crunch this data but we had to fire the person who did this. we need to have this system going in about a week's time.

Just as a side note, I think that an excellent way to graphically display this hierarchical structure is to embed a Microsoft Treeview Control in a Form. Values would have to be programmed manually and the coding is a little complicated, but the end results would be worth it.

[n8kindt]

Just as a side note, I think that an excellent way to graphically display this hierarchical structure is to embed a Microsoft Treeview Control in a Form. Values would have to be programmed manually and the coding is a little complicated, but the end results would be worth it.

sounds good to me. where do i start? from what i can understand from the way you worded it, it sounds like the Microsoft Treeview Control is something i can download and then modify? i appreciate your help thus far

so far, what i have done to graphically display is using two queries and it feels very primitive lol. simply put, the first query uses the second query as a subdatasheet linked by master/child id's. this works fine except i've noticed that there is a limit on how deep this works. for me it has worked down 6 recruits and then it stops. so it's not very useful other than a quick look at seeing who recruited who.

[n8kindt]

Just as a side note, I think that an excellent way to graphically display this hierarchical structure is to embed a Microsoft Treeview Control in a Form. Values would have to be programmed manually and the coding is a little complicated, but the end results would be worth it.

is THIS what you're talking about? an activex control?

if so, and i'm showing my true newbie colors here, can i calculate my commissions through this process and use a append/update/maketable query to make the data available in a table in access?

[ADezii]

sounds good to me. where do i start? from what i can understand from the way you worded it, it sounds like the Microsoft Treeview Control is something i can download and then modify? i appreciate your help thus far

so far, what i have done to graphically display is using two queries and it feels very primitive lol. simply put, the first query uses the second query as a subdatasheet linked by master/child id's. this works fine except i've noticed that there is a limit on how deep this works. for me it has worked down 6 recruits and then it stops. so it's not very useful other than a quick look at seeing who recruited who.

First of all, let's see if this ActiveX Control exists on your PC. In any Form Design View:

1. Click Insert ==> ActiveX Control.
2. Scroll down to see if the Microsoft TreeView Control is visible.
3. Let me know how you make out.

[n8kindt]

First of all, let's see if this ActiveX Control exists on your PC. In any Form Design View:

1. Click Insert ==> ActiveX Control.
2. Scroll down to see if the Microsoft TreeView Control is visible.
3. Let me know how you make out.

yes it worked! now what? :-)

[FishVal]

ADezii is quite right - treeview could greatly enhance your db interface. Personally I use it quite frequently as an excellent alternative to cascading combo/listboxes.

The code to "grow up" a tree is a recursive procedure similar to that I've posted above.
The first non-recursive procedure gets all persons that have no person recruited by - the root nodes and calls a recursive procedure to "grow up" a branch for each one.

You should post your table metadata to make our conversation more particular.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

[ADezii]

yes it worked! now what? :-)

Give me a little time to work up the code for you, given your scenario. As previously stated, the TreeView Control can be complex at times. I will return!

[n8kindt]

ADezii is quite right - treeview could greatly enhance your db interface. Personally I use it quite frequently as an excellent alternative to cascading combo/listboxes.

The code to "grow up" a tree is a recursive procedure similar to that I've posted above.
The first non-recursive procedure gets all persons that have no person recruited by - the root nodes and calls a recursive procedure to "grow up" a branch for each one.

You should post your table metadata to make our conversation more particular.
Here is an example of how to post table MetaData :
Table Name=tblStudent

Field; Type; IndexInfo
StudentID; AutoNumber; PK
Family; String; FK
Name; String
University; String; FK
Mark; Numeric
LastAttendance; Date/Time

Regards,
Fish

i will try my best to supply my table in the format you provided

Table Name=Table1

ID; AutoNumber;
First Name; String
Last Name; String
Status; String
Mgmt Level; Numeric
Lineage; Numeric
Recruited By; Numeric
Supervisor; Numeric
Manager; Numeric

as you can see, it is fairly straightforward. Recruited By, Supervisor, and Manager all refer to an ID in a combobox. Lineage is the number we have used to designate how far down the tree they are.

[n8kindt]

Give me a little time to work up the code for you, given your scenario. As previously stated, the TreeView Control can be complex at times. I will return!

thank you so much! i appreciate all your help.

cheers,
nate

[ADezii]

yes it worked! now what? :-)

1. Insert the TreeView Control on a Form and rename it to TreeView1 for code compatibility.
2. Create a Label on this Form and name it lblDescription.
3. Copy and Paste the following code to the Form's Load() Event.
   

   Dim nodX As Node
    
   Me![TreeView1].LineStyle = 1    'Root lines
   Me![TreeView1].Style = 7        'TreelinesPlusMinusPictureText
    
   '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text
   Set nodX = TreeView1.Nodes.Add(, , , "TreeView Demonstration (ROOT)")                       'Root of Treeview Control
     Set nodX = TreeView1.Nodes.Add(1, tvwChild, "P_A", "Person A")                            'Directly under Root
       Set nodX = TreeView1.Nodes.Add("P_A", tvwChild, "P_B", "Person B (no Children)")        'Recruit of Person A
       Set nodX = TreeView1.Nodes.Add("P_A", tvwChild, "P_C", "Person C")                      'Recruit of Person A
         Set nodX = TreeView1.Nodes.Add("P_C", tvwChild, "P_D", "Person D")                    'Recruit of Person C
           Set nodX = TreeView1.Nodes.Add("P_D", tvwChild, "P_E", "Person E (no Children)")    'Recruit of Person D
     Set nodX = TreeView1.Nodes.Add(1, tvwChild, "P_Z", "Person Z (no Children)")              'Directly under Root
    
   'nodX.EnsureVisible
    
   'Accentuate the Root by making Text Bold
   TreeView1.Nodes(1).ForeColor = QBColor(4)
    
   For Each nodX In TreeView1.Nodes
     nodX.Expanded = True          'Let's Expand all Nodes
     If nodX.Children > 1 Then
       nodX.Sorted = True
     End If
   Next

4. Copy and Paste the following code to the NodeClick() Event of the TreeView Control:
   

   Private Sub TreeView1_NodeClick(ByVal Node As Object)
   On Error GoTo Err_TreeView1_NodeClick
   Dim Msg As String
    
   Msg = "Selected Node: " & Node.Text & vbCrLf & "Path: " & Node.FullPath & vbCrLf
   Msg = Msg & "Number of Children: " & Node.Children & vbCrLf
   Msg = Msg & "Parent: " & Node.Parent
     Me![lblDescription].Caption = Msg
    
   Exit_TreeView1_NodeClick:
     Exit Sub
    
   Err_TreeView1_NodeClick:
     MsgBox Err.Description, vbExclamation, "Error in TreeView1_NodeClick()"
     Resume Exit_TreeView1_NodeClick
   End Sub

5. To access common Properties of the treeView Control: ==> Select Control ==> Right Click ==> TreeCtrl Object ==> Properties
6. I've made my Test Database for this Thread available to you as an Attachment, I would strongly suggest you download it, since a Picture is worth a thousand words, and it will definitely be easier for you to see what is going on rather than creating everything from scratch. ActiveX Controls can be quirky, the Database may/may not work, but at least you have the option.

[n8kindt]

once again, i appreciate all your help. i'll plug it in and see if it works. thank you! :)

[n8kindt]

once again, i appreciate all your help. i'll plug it in and see if it works. thank you! :)

ok, ummm one question i have so far is:

i have about 1000+ people on this tree. am i going to have to input everyone one by one into this control? i don't immediately see how i am supposed to link data from a table to this?

[FishVal]

i will try my best to supply my table in the format you provided

Table Name=Table1

ID; AutoNumber;
First Name; String
Last Name; String
Status; String
Mgmt Level; Numeric
Lineage; Numeric
Recruited By; Numeric
Supervisor; Numeric
Manager; Numeric

as you can see, it is fairly straightforward. Recruited By, Supervisor, and Manager all refer to an ID in a combobox. Lineage is the number we have used to designate how far down the tree they are.

Ok.

It is not the simplest example of tree organized data.
Actually (according to table design without getting into how this going in real life) I see 3 trees in this table.
On the one hand it is possible to build such an interface with triple tree where each tree node has 3 predefine nodes - recruiters, managed persons and supervised persons, but I think it may complicate the whole interface (or maybe not, whats your opinion).

I think I will provide you first a sample with "single" tree reflecting recruiter/recruiters hierarchy, then will see where will we proceed.

One point I should bring to your attention just now. You should be aware that storing [Lineage] field is obvious information duplication. It may be acceptable from performance reasons but you need to have a mechanism to synchronize recruit/recruiters hierarchy with lineage value. Though it is not supposed to change, things like that should be taken in consideration as they often strikes back in pretty nice manner.

Regards,
Fish

P.S. Actually [Manager] and maybe [Supervisor] are fields that contain duplicate information too as long as person gets this status according to a certain rules. Am I right? You should better explain this point clearer to let me make consideration of the whole table design.

P.S.S. If you provide me a sanitized sample of the table w/o any sensitive information but with quantity of records sufficient for design purposes (30-40 records with hierarchy depth something about 4-5) you will make my task much easier. ;)

[n8kindt]

Ok.

It is not the simplest example of tree organized data.
Actually (according to table design without getting into how this going in real life) I see 3 trees in this table.
On the one hand it is possible to build such an interface with triple tree where each tree node has 3 predefine nodes - recruiters, managed persons and supervised persons, but I think it may complicate the whole interface (or maybe not, whats your opinion).

I think I will provide you first a sample with "single" tree reflecting recruiter/recruiters hierarchy, then will see where will we proceed.

One point I should bring to your attention just now. You should be aware that storing [Lineage] field is obvious information duplication. It may be acceptable from performance reasons but you need to have a mechanism to synchronize recruit/recruiters hierarchy with lineage value. Though it is not supposed to change, things like that should be taken in consideration as they often strikes back in pretty nice manner.

Regards,
Fish

P.S. Actually [Manager] and maybe [Supervisor] are fields that contain duplicate information too as long as person gets this status according to a certain rules. Am I right? You should better explain this point clearer to let me make consideration of the whole table design.

P.S.S. If you provide me a sanitized sample of the table w/o any sensitive information but with quantity of records sufficient for design purposes (30-40 records with hierarchy depth something about 4-5) you will make my task much easier. ;)

here comes the can of worms:

yes, i understand about the lineage duplication. right now, it's hard to change b/c the table is also a backend database for an additional database that is simply used for customer lookup and this number is referred to quite often. nothing i can really do about that until this new system is locked in place as the method of choice.

you are correct, the supervisor and manager are often the same. i'd have to say 70% of the time, this is the case. a person is declared a manager after recruiting 10 people. and declared a supervisor upon recruiting 10. as i mentioned about status, some of these recruits can go inactive and deduct from the total recruits. here's another exeption: let's say a manager has 10 recruits and one goes inactive. even though their technical pay scale status changes to supervisor, they are still considered a manager until they themselves become inactive or is formally demoted by the ceo.

i will work on providing the sanitized sample as you requested. in the meantime, i just want to make sure you aren't going to all this trouble simply for a graphical display OR a commission calculation. it needs to be for BOTH purposes. preferably in a report form.

i realize this is asking a lot. the following is one more thing that i have to consider: i am on a deadline for friday and even though the database will not perfect from an efficiency standpoint (possibly even functional standpoint), as long as it can limp along it will be acceptable. i have a laundry list of additional projects i am coordinating with this. i can clean up and go into efficiency/troubleshoot mode immediately thereafter. given the timeline, i cannot expect too many smooth edges. i just need something to show for myself.

[Stewart Ross]

i will work on providing the sanitized sample as you requested. in the meantime, i just want to make sure you aren't going to all this trouble simply for a graphical display OR a commission calculation. it needs to be for BOTH purposes. preferably in a report form.

Hi. I would not try to speak for Fish, here, but I do think that your request IS overstepping the mark for a forum designed to help people to help themselves. Fish has provided code samples and spent time on this already - to suggest that further help has to be as per your requirements or not at all is going too far in my opinion.

MODERATOR

[Scott Price]

I don't mean to throw a monkey wrench into your lines of thought... Just as an added option, have a look at Self Joins from Allen Browne. It illustrates something remarkably similar to what you are describing.

Regards,
Scott

[ADezii]

ok, ummm one question i have so far is:

i have about 1000+ people on this tree. am i going to have to input everyone one by one into this control? i don't immediately see how i am supposed to link data from a table to this?

You can pro-grammatically populate a TreeView Control from a Recordset but the data must be clearly defined, and the Joins between Parent/Child Fields must be precise and not ambiguous. The code would be fairly complex, and frankly, may be above your skill level since you stated you are a relative Newbie.

[FishVal]

To: n8kindt

Treeview control is not supposed to serve as report. It is for interface only.
At least it is my own humble opinion.
As you've not specified the report layout you are going to build, I will make a guess that an important part of it will be query returning records with the following fields: [RecruiterID], [RecruitID], [Distance].

The suggestion made by Scott Price though not applicable as is has a promising idea - I mean table self join.

I would suggest a hybrid SQL/VBA solution.

SQL: full outer join of the table with itself to get all possible combination of [ID] field values, query fetches both ID's and calculates [Distance] based on them using VBA function.
VBA: function gets both [ID] values and scan recruiter/recruit hierarchy backward from one [ID] value to meet another thus determining [Distance]

Then records with undefined [Distance] are omitted by WHERE criteria or by INNER JOIN with table defining earning percentage for distance value.

Thus obtained recordset may be joined with person earnings table or whatever you have and grouped to obtain sums.



To: Stewart Ross Inverness

Not a problem, Stewart.
I just find this case to be quite interesting. Sure I'm not going to sacrifice all my time for the sake of Friday's Deadline. Moreover that is not a problem for me even to provide working treeview control driven by table data as soon as I have the similar implemented in almost all of my projects. Trees are a kind of addiction for me. LOL.

Regards,
Fish

[n8kindt]

To: n8kindt

Treeview control is not supposed to serve as report. It is for interface only.
At least it is my own humble opinion.
As you've not specified the report layout you are going to build, I will make a guess that an important part of it will be query returning records with the following fields: [RecruiterID], [RecruitID], [Distance].

The suggestion made by Scott Price though not applicable as is has a promising idea - I mean table self join.

I would suggest a hybrid SQL/VBA solution.

SQL: full outer join of the table with itself to get all possible combination of [ID] field values, query fetches both ID's and calculates [Distance] based on them using VBA function.
VBA: function gets both [ID] values and scan recruiter/recruit hierarchy backward from one [ID] value to meet another thus determining [Distance]

Then records with undefined [Distance] are omitted by WHERE criteria or by INNER JOIN with table defining earning percentage for distance value.

Thus obtained recordset may be joined with person earnings table or whatever you have and grouped to obtain sums.



To: Stewart Ross Inverness

Not a problem, Stewart.
I just find this case to be quite interesting. Sure I'm not going to sacrifice all my time for the sake of Friday's Deadline. Moreover that is not a problem for me even to provide working treeview control driven by table data as soon as I have the similar implemented in almost all of my projects. Trees are a kind of addiction for me. LOL.

Regards,
Fish

thanks for all your help, fish. lol wow i got a lot of information all at once. my plan of action right now is to try scott's suggestion (to me, this looks like a winner) and see what i am lacking from there. i definitely don't have a strength in vba. i grasp the concept, but the language is what kills me. i have messed around with union queries enough to know my way around SQL. so i'll have to see exactly what vba i will need once i get the structure set up. hopefully, i won't have to ask anymore of you guys' time and i can figure it out on my own.

thanks a million to everyone who has had input thus far on this thread.

[ADezii]

To: n8kindt

Treeview control is not supposed to serve as report. It is for interface only.
At least it is my own humble opinion.
As you've not specified the report layout you are going to build, I will make a guess that an important part of it will be query returning records with the following fields: [RecruiterID], [RecruitID], [Distance].

The suggestion made by Scott Price though not applicable as is has a promising idea - I mean table self join.

I would suggest a hybrid SQL/VBA solution.

SQL: full outer join of the table with itself to get all possible combination of [ID] field values, query fetches both ID's and calculates [Distance] based on them using VBA function.
VBA: function gets both [ID] values and scan recruiter/recruit hierarchy backward from one [ID] value to meet another thus determining [Distance]

Then records with undefined [Distance] are omitted by WHERE criteria or by INNER JOIN with table defining earning percentage for distance value.

Thus obtained recordset may be joined with person earnings table or whatever you have and grouped to obtain sums.



To: Stewart Ross Inverness

Not a problem, Stewart.
I just find this case to be quite interesting. Sure I'm not going to sacrifice all my time for the sake of Friday's Deadline. Moreover that is not a problem for me even to provide working treeview control driven by table data as soon as I have the similar implemented in almost all of my projects. Trees are a kind of addiction for me. LOL.

Regards,
Fish

Hello FishVal, just curious, do you use ImageList Controls in conjunction with your TreeViews? They can really put a nice finishing touch on the basic TreeView functionality, and also make them more visually appealing.

[FishVal]

Hello FishVal, just curious, do you use ImageList Controls in conjunction with your TreeViews? They can really put a nice finishing touch on the basic TreeView functionality, and also make them more visually appealing.

Hello, ADezii.

Yes I'm using it, though to a somewhat minor extent so far.
Just four images for node, expanded node, leaf and new node slot.

Regards,
Fish

[ADezii]

Hello, ADezii.

Yes I'm using it, though to a somewhat minor extent so far.
Just four images for node, expanded node, leaf and new node slot.

Regards,
Fish

That's considered a minor extent? (LOL).

[n8kindt]

Ok.

One point I should bring to your attention just now. You should be aware that storing [Lineage] field is obvious information duplication. It may be acceptable from performance reasons but you need to have a mechanism to synchronize recruit/recruiters hierarchy with lineage value. Though it is not supposed to change, things like that should be taken in consideration as they often strikes back in pretty nice manner.

Regards,
Fish

P.S. Actually [Manager] and maybe [Supervisor] are fields that contain duplicate information too as long as person gets this status according to a certain rules. Am I right? You should better explain this point clearer to let me make consideration of the whole table design.

wow fish, this totally makes a lot more sense AFTER my project is nearly polished up. yeah, i quickly abandoned those lineage values for multiple reasons (as i'm sure you are aware of) and used vba to calculate the lineage based on that relationship link that scott had listed. the basics of vba was a lot easier to learn than i thought it would be. if anyone is interested in how i accomplished my task feel free to reply and i will sit down and do my best to explain the process.

[spacethefinalfrontier101]

Tables...

recruits
1
2
3
4
.
.

managers
1
2
3
.
.

managersrecruits
managerid, recruitid

discountdef
1, "10 or more recruits"
2, "Less than 10 recruits"


discounts
1, .20
2, .10

Add recruit:
insert recruits
insert managerrecruits

Remove recruit:
delete recruits
delete managersrecruits (if necessary)

The managersrecruits table is used for anyone who hires someone else.

[n8kindt]

Hi, there.

This is a classical case to use recursive code.
You need to write a VBA function scanning down the tree (let use this commonly accepted (in programming at least) word instead of "pyramid") from a predefined node (person ID in your case).

The logic is described below in pseudocode.

'the function called from query
'it has dblCommisions variable which will be passed by reference
' to recursive procedure to a accumulate commissions sum 
Function GetCommisions(ByVal varPersonID As Variant) As Double
 
    Dim dblCommisions as Double
 
    dblCommisions=0
    'run recursive procedure with initial values
    ScanBranch(varRecruitID, 0, dblCommisions)
    GetCommisions=dblCommisions
 
End Function
 
'recursive (selfcalling) procedure
' iterating through the 1st level recruits,
' calling itself to do the same for each recruit found
Sub ScanBranch(ByVal varPersonID As Variant, _
                                   ByVal intNodesFromStart As Integer
                                   ByRef dblIntermediateSum as Double)
 
    Dim varRecruitID as Variant
 
    'increment distance from start used in commission calculation
    intNodesFromStart = intNodesFromStart+1
 
    Do
        'get the next from 1st level recruits
        varRecruitID=GetNextRecruit(varPersonID)
        If <no more recruits> Then Exit Function
        'add commissions depending on distance from start 
        dblIntermediateSum=dblIntermediateSum + _
             CalculateCommision(intNodesFromStart)
        'do the same for this 1st level recruit to move down the branch
        ScanBranch(varRecruitID, intNodesFromStart, dblIntermediateSum)
    While True
 
End Sub
 

Regards,
Fish

here i am revisiting this again. i found a minor (yet major) flaw in my previous SQL approach. the problem being, it's not a perfect pyramid. more like a tree as you said. so a couple of people ended up being omitted when a certain condition was present. so, i'm scrapping the SQL approach for now.

... i had posted more earlier but i figured it out. i'm sure i'll be back for more questions tho :)