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

[i]Field; Type; IndexInfo[/i]
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

[i]Field; Type; IndexInfo[/i]
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.
   [CODE=vb]
   Dim nodX As Node
   
   Me![TreeView1].LineStyle = 1 'Root lines
   Me![TreeView1].Style = 7 'TreelinesPlusM inusPictureText
   
   '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.EnsureVis ible
   
   '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[/CODE]
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.