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? :-)