By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,482 Members | 2,073 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,482 IT Pros & Developers. It's quick & easy.

pyramid commission

n8kindt
100+
P: 221
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.
Mar 17 '08 #1
Share this Question
Share on Google+
29 Replies


FishVal
Expert 2.5K+
P: 2,653
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.
Expand|Select|Wrap|Line Numbers
  1. 'the function called from query
  2. 'it has dblCommisions variable which will be passed by reference
  3. ' to recursive procedure to a accumulate commissions sum 
  4. Function GetCommisions(ByVal varPersonID As Variant) As Double
  5.  
  6.     Dim dblCommisions as Double
  7.  
  8.     dblCommisions=0
  9.     'run recursive procedure with initial values
  10.     ScanBranch(varRecruitID, 0, dblCommisions)
  11.     GetCommisions=dblCommisions
  12.  
  13. End Function
  14.  
  15. 'recursive (selfcalling) procedure
  16. ' iterating through the 1st level recruits,
  17. ' calling itself to do the same for each recruit found
  18. Sub ScanBranch(ByVal varPersonID As Variant, _
  19.                                    ByVal intNodesFromStart As Integer
  20.                                    ByRef dblIntermediateSum as Double)
  21.  
  22.     Dim varRecruitID as Variant
  23.  
  24.     'increment distance from start used in commission calculation
  25.     intNodesFromStart = intNodesFromStart+1
  26.  
  27.     Do
  28.         'get the next from 1st level recruits
  29.         varRecruitID=GetNextRecruit(varPersonID)
  30.         If <no more recruits> Then Exit Function
  31.         'add commissions depending on distance from start 
  32.         dblIntermediateSum=dblIntermediateSum + _
  33.              CalculateCommision(intNodesFromStart)
  34.         'do the same for this 1st level recruit to move down the branch
  35.         ScanBranch(varRecruitID, intNodesFromStart, dblIntermediateSum)
  36.     While True
  37.  
  38. End Sub
  39.  
Regards,
Fish
Mar 17 '08 #2

n8kindt
100+
P: 221
that's exactly what i was looking for!! you just made my day. thank you so much!
Mar 17 '08 #3

FishVal
Expert 2.5K+
P: 2,653
that's exactly what i was looking for!! you just made my day. thank you so much!
You are welcome.
Good luck.
Mar 17 '08 #4

n8kindt
100+
P: 221
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.
Mar 17 '08 #5

ADezii
Expert 5K+
P: 8,597
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.
Mar 18 '08 #6

n8kindt
100+
P: 221
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.
Mar 18 '08 #7

n8kindt
100+
P: 221
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?
Mar 18 '08 #8

ADezii
Expert 5K+
P: 8,597
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.
Mar 18 '08 #9

n8kindt
100+
P: 221
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? :-)
Mar 18 '08 #10

FishVal
Expert 2.5K+
P: 2,653
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Regards,
Fish
Mar 18 '08 #11

ADezii
Expert 5K+
P: 8,597
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!
Mar 18 '08 #12

n8kindt
100+
P: 221
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
Expand|Select|Wrap|Line Numbers
  1. Field; Type; IndexInfo
  2. StudentID; AutoNumber; PK
  3. Family; String; FK
  4. Name; String
  5. University; String; FK
  6. Mark; Numeric
  7. LastAttendance; Date/Time
Regards,
Fish
i will try my best to supply my table in the format you provided

Table Name=Table1
Expand|Select|Wrap|Line Numbers
  1. ID; AutoNumber;
  2. First Name; String
  3. Last Name; String
  4. Status; String
  5. Mgmt Level; Numeric
  6. Lineage; Numeric
  7. Recruited By; Numeric
  8. Supervisor; Numeric
  9. 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.
Mar 18 '08 #13

n8kindt
100+
P: 221
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
Mar 18 '08 #14

ADezii
Expert 5K+
P: 8,597
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.
    Expand|Select|Wrap|Line Numbers
    1. Dim nodX As Node
    2.  
    3. Me![TreeView1].LineStyle = 1    'Root lines
    4. Me![TreeView1].Style = 7        'TreelinesPlusMinusPictureText
    5.  
    6. '1-Relative, tvwChild-Relationship, "g"-Key, "George"-Text
    7. Set nodX = TreeView1.Nodes.Add(, , , "TreeView Demonstration (ROOT)")                       'Root of Treeview Control
    8.   Set nodX = TreeView1.Nodes.Add(1, tvwChild, "P_A", "Person A")                            'Directly under Root
    9.     Set nodX = TreeView1.Nodes.Add("P_A", tvwChild, "P_B", "Person B (no Children)")        'Recruit of Person A
    10.     Set nodX = TreeView1.Nodes.Add("P_A", tvwChild, "P_C", "Person C")                      'Recruit of Person A
    11.       Set nodX = TreeView1.Nodes.Add("P_C", tvwChild, "P_D", "Person D")                    'Recruit of Person C
    12.         Set nodX = TreeView1.Nodes.Add("P_D", tvwChild, "P_E", "Person E (no Children)")    'Recruit of Person D
    13.   Set nodX = TreeView1.Nodes.Add(1, tvwChild, "P_Z", "Person Z (no Children)")              'Directly under Root
    14.  
    15. 'nodX.EnsureVisible
    16.  
    17. 'Accentuate the Root by making Text Bold
    18. TreeView1.Nodes(1).ForeColor = QBColor(4)
    19.  
    20. For Each nodX In TreeView1.Nodes
    21.   nodX.Expanded = True          'Let's Expand all Nodes
    22.   If nodX.Children > 1 Then
    23.     nodX.Sorted = True
    24.   End If
    25. Next
  4. Copy and Paste the following code to the NodeClick() Event of the TreeView Control:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub TreeView1_NodeClick(ByVal Node As Object)
    2. On Error GoTo Err_TreeView1_NodeClick
    3. Dim Msg As String
    4.  
    5. Msg = "Selected Node: " & Node.Text & vbCrLf & "Path: " & Node.FullPath & vbCrLf
    6. Msg = Msg & "Number of Children: " & Node.Children & vbCrLf
    7. Msg = Msg & "Parent: " & Node.Parent
    8.   Me![lblDescription].Caption = Msg
    9.  
    10. Exit_TreeView1_NodeClick:
    11.   Exit Sub
    12.  
    13. Err_TreeView1_NodeClick:
    14.   MsgBox Err.Description, vbExclamation, "Error in TreeView1_NodeClick()"
    15.   Resume Exit_TreeView1_NodeClick
    16. 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.
Mar 18 '08 #15

n8kindt
100+
P: 221
once again, i appreciate all your help. i'll plug it in and see if it works. thank you! :)
Mar 18 '08 #16

n8kindt
100+
P: 221
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?
Mar 18 '08 #17

FishVal
Expert 2.5K+
P: 2,653
i will try my best to supply my table in the format you provided

Table Name=Table1
Expand|Select|Wrap|Line Numbers
  1. ID; AutoNumber;
  2. First Name; String
  3. Last Name; String
  4. Status; String
  5. Mgmt Level; Numeric
  6. Lineage; Numeric
  7. Recruited By; Numeric
  8. Supervisor; Numeric
  9. 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. ;)
Mar 18 '08 #18

n8kindt
100+
P: 221
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.
Mar 18 '08 #19

Expert Mod 2.5K+
P: 2,545
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
Mar 18 '08 #20

Scott Price
Expert 100+
P: 1,384
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
Mar 18 '08 #21

ADezii
Expert 5K+
P: 8,597
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.
Mar 18 '08 #22

FishVal
Expert 2.5K+
P: 2,653
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
Mar 18 '08 #23

n8kindt
100+
P: 221
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.
Mar 19 '08 #24

ADezii
Expert 5K+
P: 8,597
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.
Mar 19 '08 #25

FishVal
Expert 2.5K+
P: 2,653
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
Mar 19 '08 #26

ADezii
Expert 5K+
P: 8,597
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).
Mar 19 '08 #27

n8kindt
100+
P: 221
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.
Mar 27 '08 #28

P: 1
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.
Apr 9 '08 #29

n8kindt
100+
P: 221
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.
Expand|Select|Wrap|Line Numbers
  1. 'the function called from query
  2. 'it has dblCommisions variable which will be passed by reference
  3. ' to recursive procedure to a accumulate commissions sum 
  4. Function GetCommisions(ByVal varPersonID As Variant) As Double
  5.  
  6.     Dim dblCommisions as Double
  7.  
  8.     dblCommisions=0
  9.     'run recursive procedure with initial values
  10.     ScanBranch(varRecruitID, 0, dblCommisions)
  11.     GetCommisions=dblCommisions
  12.  
  13. End Function
  14.  
  15. 'recursive (selfcalling) procedure
  16. ' iterating through the 1st level recruits,
  17. ' calling itself to do the same for each recruit found
  18. Sub ScanBranch(ByVal varPersonID As Variant, _
  19.                                    ByVal intNodesFromStart As Integer
  20.                                    ByRef dblIntermediateSum as Double)
  21.  
  22.     Dim varRecruitID as Variant
  23.  
  24.     'increment distance from start used in commission calculation
  25.     intNodesFromStart = intNodesFromStart+1
  26.  
  27.     Do
  28.         'get the next from 1st level recruits
  29.         varRecruitID=GetNextRecruit(varPersonID)
  30.         If <no more recruits> Then Exit Function
  31.         'add commissions depending on distance from start 
  32.         dblIntermediateSum=dblIntermediateSum + _
  33.              CalculateCommision(intNodesFromStart)
  34.         'do the same for this 1st level recruit to move down the branch
  35.         ScanBranch(varRecruitID, intNodesFromStart, dblIntermediateSum)
  36.     While True
  37.  
  38. End Sub
  39.  
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 :)
May 21 '08 #30

Post your reply

Sign in to post your reply or Sign up for a free account.