Hello i am building a scheme for a multlevel marketing and i need to relate agents o other agents
so i am builfding the agents table using this scheme
AgentPK....AgentName....ReportsTo
1..............A..................null (this is a top agent)
2..............B..................null (this is a top agent)
3..............C..................1 (managed by A)
4..............D..................2 (managed by B)
5..............E..................1 (managed by A)
6..............F...................3 (managed by C)
7..............G..................3 (managed by C)
How do i relate, for example the agent 5 to agent 1?
thanks for help
Welcome to the Access World Forums! To get the best out of these forums, please do the following: INTRODUCE YOURSELF Introduce yourself - not this thread but by clicking the Post Thread button on the top right - and tell us about yourself. Are you new to Microsoft Access or an established...
I have had some people ask questions on working with Hierarchical data and Tree Views, which often involves using recursion. If not familiar with recursion this is where a procedure repeatedly calls itself until hitting a condition where it is told to stop. This is used often where you have to...
www.access-programmers.co.uk
This can be done with a self-referencing table. The parent ID foreign key simply points to a primary key in the same table. I discuss this in the thread. If you know the number of levels this can be done using standard query joins, but you are likely going to require some recursion to sum up values up the entire branch.
Anyways the royalty pyramid is going to require recursive code. I do not think this can be done in a query or at least not easily. It probably can be done with several queries unioned together, but that may be more confusing than writing code. Also if you write code, you will have to push the results to a temp table.
In order for me to demonstrate this can you tell me the rules?
So when the agent in the bottomline (the third) will make a sell, the upper line will get some royalties. When the agent on second line will make a sell, he and the first line will get royalties, when the top line make a sell only he will get royalties
In other words if level 3 makes a sale does level three get a different percent of the royalties then level 2 and the top. I would assume it gets smaller as it goes up.
Also does Level one get the same percent of royalties from a sale done at level 3 vs a sale done at level 2?
In other words can you fill out this table
LevelRoyalties LevelRoyalties
Level
LevelSold
Percent
1
1
1
2
1
3
2
2
2
3
3
3
So for the first row
If something is sold by level 1 what percent of the sale goes to Level 1
For the 3rd row
If something is sold at level 3 what percentage of the sale goes to level 1
For the 5th row
If something is sold by level 3 what percentage goes to level 2.
In the linked thread to the other forum @CJ_London suggests that this is a trivial problem, and I found it to be the opposite. I would be curious of other approaches since I may be reading too much into it. I also designed this to handle any level of agents. This makes the problem far more challenging.
Assume you have agents.
tblAgents tblAgents
AgentPK
AgentName
ReportsTo
AgentLevel
1
A
1
2
B
1
3
C
1
2
4
D
2
2
5
E
1
2
6
F
3
3
7
G
3
3
I added the AgentLevel but this is inputted through code in a recursive call. With a large database it is impossible to do that manually.
To show the relation then (since we know the absolute levels)
qryHierarchy qryHierarchy
TopLevel.AgentPK
TopLevel.AgentName
Level2.AgentPK
Level2.AgentName
Level3.AgentPK
Level3.AgentName
1
A
3
C
6
F
1
A
3
C
7
G
1
A
5
E
2
B
4
D
Assume there is a table showing the percentage of commission based on what level something is sold and what level an agent is at.
tblLevelRoyalties tblLevelRoyalties
ID
AgentLevel
LevelSold
Percent
1
1
1
0.1
2
1
2
0.05
3
1
3
0.025
4
2
2
0.1
5
2
3
0.05
6
3
3
0.1
Example. If an agent is at level 1 and someone in their "tree" is at level 3 then they get .025 of what the level 3 agent sells
To update the Agent Levels you can use this code.
Code:
Public Sub UpdateLevels()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblAgents", dbOpenDynaset)
Do While Not rs.EOF
rs.Edit
rs!AgentLevel = GetLevel(rs!agentPK)
rs.Update
rs.MoveNext
Loop
End Sub
Public Function GetLevel(ID As Long) As Integer
Dim ParentID As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblAgents where AgentPK = " & ID, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
If IsNull(rs!ReportsTo) Then
GetLevel = 1
Else
GetLevel = RecurseLevels(rs!ReportsTo)
End If
End If
End Function
Private Function RecurseLevels(ParentID As Long, Optional Level As Integer = 1)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where AgentPK = " & ParentID
' Debug.Print strSql
' Debug.Print "Level " & Level
Set rs = CurrentDb.OpenRecordset(strSql)
Level = Level + 1
Do While Not rs.EOF
ParentID = Nz(rs!ReportsTo, 0)
If ParentID <> 0 Then
RecurseLevels ParentID, Level
End If
rs.MoveNext
Loop
RecurseLevels = Level
End Function
Now I create a temp table and create a "Sales Tree" table. This is the real heavy lifting. It has every Agent and then every Agent below them in their tree.
tblSalesTree tblSalesTree
SalesTreeID
AgentID
AgentLevel
ChildID
ChildLevel
26
1
1
1
1
27
1
1
3
2
28
1
1
6
3
29
1
1
7
3
30
1
1
5
2
31
2
1
2
1
32
2
1
4
2
33
3
2
3
2
34
3
2
6
3
35
3
2
7
3
36
4
2
4
2
37
5
2
5
2
38
6
3
6
3
39
7
3
7
3
Example Agent 1 has child 3 and 5, and grand children 6,7
Agent 2 has child 4
Agent 3 has children 6,7
Code:
Public Sub UpdateSalesTree()
Dim rs As DAO.Recordset
Dim strSql As String
UpdateLevels
Set rs = CurrentDb.OpenRecordset("select * from tblAgents Order By AgentLevel, AgentPK")
Do While Not rs.EOF
Debug.Print rs!agentPK & " Agent"
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & rs!agentPK & ", " & rs!agentPK & ", " & GetStoredLevel(rs!agentPK) & ", " & GetStoredLevel(rs!agentPK) & ")"
CurrentDb.Execute strSql
UpdateAgentSalesTree rs!agentPK, rs!agentPK
rs.MoveNext
Loop
End Sub
Public Sub UpdateAgentSalesTree(AgentID As Long, StartingAgentID As Long)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where ReportsTo = " & AgentID
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
AgentID = rs!agentPK
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & StartingAgentID & ", " & AgentID & ", " & GetStoredLevel(StartingAgentID) & ", " & GetStoredLevel(AgentID) & ")"
Debug.Print strSql
CurrentDb.Execute strSql
UpdateAgentSalesTree AgentID, StartingAgentID
rs.MoveNext
Loop
End Sub
Public Function GetStoredLevel(AgentID As Long) As Long
If DCount("*", "tblAgents", "agentPK = " & AgentID) = 0 Then
Err.Raise 9999, , "No such Agent Exists with PK of " & AgentID
End If
GetStoredLevel = DLookup("AgentLevel", "TblAgents", "AgentPK = " & AgentID)
End Function
before i start to study your example let me answer
Yes the percentage will change according to who make the sale
If level 3 make the sale, the percentage is to be divided with all the superior level, so with main agency, level 1 and level 2 example m.a. 40% - level1 30% - level2 20% - level3 10%
il level 2 make the sale the percentage would change so that the 100% woul be among the main agency, level 1 and level 2 example m.a. 50% - level1 30% - level2 20%.
if level 1 make the sale the percentage would change so that he and the main agency would share all ..example m.a.60% - level1 40%
this should be cear now
What i am concerning now is ho to develop the whole thing in access..as you saying that it s not so easy..and i start to think the same. I can't write code..i don't know..so this could be a limitation..
do you think is still possible?
or i might consider doing it with excel..much less elegant and less automation but probably easier?
before i start to study your example let me answer
Yes the percentage will change according to who make the sale
If level 3 make the sale, the percentage is to be divided with all the superior level, so with main agency, level 1 and level 2 example m.a. 40% - level1 30% - level2 20% - level3 10%
il level 2 make the sale the percentage would change so that the 100% woul be among the main agency, level 1 and level 2 example m.a. 50% - level1 30% - level2 20%.
if level 1 make the sale the percentage would change so that he and the main agency would share all ..example m.a.60% - level1 40%
this should be cear now
What i am concerning now is ho to develop the whole thing in access..as you saying that it s not so easy..and i start to think the same. I can't write code..i don't know..so this could be a limitation..
do you think is still possible?
or i might consider doing it with excel..much less elegant and less automation but probably easier?
Good Lord, how on the earth do you manage to get agents?
A friend of mine worked for a firm like that for a while, but whilst his superiors took a cut, it was a a small slice, not most of the cake?
Good Lord, how on the earth do you manage to get agents?
A friend of mine worked for a firm like that for a while, but whilst his superiors took a cut, it was a a small slice, not most of the cake?
who tell you tha agents will be got? i just do the db..and knowing the people i reckon 4 or 5 agents will be there..And after that i don't see any sales from them ..but the want to be ready..and i do the db!
This is a complete working solution to the Royalty Pyramid. I know of no easier way, but curious if @CJ_London has an easier approach. Unlike him, I do not have any background in sales commission schemes so I may have overthought this. I do have more experience in Tree Views and recursive data in Access then anyone else on this forum. Or at least have have way more threads than anyone else on these topics.
This handles any royalty scheme. Handles any number of agents and levels. You can add, edit, delete employees.
Here is the complete code.
Code:
Public Sub UpdateRoyalties()
MsgBox "This clears the Sales Tree."
ClearSalesTree
MsgBox "This update the Agent Levels"
UpdateLevels
MsgBox "This Updates the Agent Tree"
UpdateSalesTree
End Sub
'------------------------------------------------ Update Levels ---------------------------------------------------------------------
Public Sub UpdateLevels()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblAgents", dbOpenDynaset)
Do While Not rs.EOF
rs.Edit
rs!AgentLevel = GetLevel(rs!agentPK)
rs.Update
rs.MoveNext
Loop
End Sub
Public Function GetLevel(ID As Long) As Integer
Dim ParentID As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblAgents where AgentPK = " & ID, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
If IsNull(rs!ReportsTo) Then
GetLevel = 1
Else
GetLevel = RecurseLevels(rs!ReportsTo)
End If
End If
End Function
Private Function RecurseLevels(ParentID As Long, Optional Level As Integer = 1)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where AgentPK = " & ParentID
' Debug.Print strSql
' Debug.Print "Level " & Level
Set rs = CurrentDb.OpenRecordset(strSql)
Level = Level + 1
Do While Not rs.EOF
ParentID = Nz(rs!ReportsTo, 0)
If ParentID <> 0 Then
RecurseLevels ParentID, Level
End If
rs.MoveNext
Loop
RecurseLevels = Level
End Function
'---------------------------------------------------------- Update Sales Tree ---------------------------------------------------------------
Public Sub ClearSalesTree()
CurrentDb.Execute "qryDeleteSalesTree"
End Sub
Public Sub UpdateSalesTree()
Dim rs As DAO.Recordset
Dim strSql As String
Set rs = CurrentDb.OpenRecordset("select * from tblAgents Order By AgentLevel, AgentPK")
Do While Not rs.EOF
Debug.Print rs!agentPK & " Agent"
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & rs!agentPK & ", " & rs!agentPK & ", " & GetStoredLevel(rs!agentPK) & ", " & GetStoredLevel(rs!agentPK) & ")"
CurrentDb.Execute strSql
UpdateAgentSalesTree rs!agentPK, rs!agentPK
rs.MoveNext
Loop
End Sub
Public Sub UpdateAgentSalesTree(AgentID As Long, StartingAgentID As Long)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where ReportsTo = " & AgentID
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
AgentID = rs!agentPK
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & StartingAgentID & ", " & AgentID & ", " & GetStoredLevel(StartingAgentID) & ", " & GetStoredLevel(AgentID) & ")"
Debug.Print strSql
CurrentDb.Execute strSql
UpdateAgentSalesTree AgentID, StartingAgentID
rs.MoveNext
Loop
End Sub
Public Function GetStoredLevel(AgentID As Long) As Long
If DCount("*", "tblAgents", "agentPK = " & AgentID) = 0 Then
Err.Raise 9999, , "No such Agent Exists with PK of " & AgentID
End If
GetStoredLevel = DLookup("AgentLevel", "TblAgents", "AgentPK = " & AgentID)
End Function
All of that does just two things.
1. Determines the Agent's Level and saves to the Agent Table. This would need to update when you add, edit, delete and employee
2. Creates what I call the Agent Tree. This is a table that has records showing every Agent and every Agent below them.
subFrmAgentTree subFrmAgentTree
AgentID
AgentName
AgentLevel
ChildID
ChildName
ChildLevel
1
A
1
1
A
1
1
A
1
3
C
2
1
A
1
5
E
2
1
A
1
6
F
3
1
A
1
7
G
3
2
B
1
2
B
1
2
B
1
4
D
2
3
C
2
3
C
2
3
C
2
6
F
3
3
C
2
7
G
3
4
D
2
4
D
2
5
E
2
5
E
2
6
F
3
6
F
3
7
G
3
7
G
3
Once the Agent Tree is created it only needs to be updated once you add, edit, delete an employee. Adding, editing sales or changing the Royalty percentages does not require an update. The button to run the code is on the last tab.
This is a complete working solution to the Royalty Pyramid. I know of no easier way, but curious if @CJ_London has an easier approach. Unlike him, I do not have any background in sales commission schemes so I may have overthought this. I do have more experience in Tree Views and recursive data in Access then anyone else on this forum. Or at least have have way more threads than anyone else on these topics.
This handles any royalty scheme. Handles any number of agents and levels. You can add, edit, delete employees.
Here is the complete code.
Code:
Public Sub UpdateRoyalties()
MsgBox "This clears the Sales Tree."
ClearSalesTree
MsgBox "This update the Agent Levels"
UpdateLevels
MsgBox "This Updates the Agent Tree"
UpdateSalesTree
End Sub
'------------------------------------------------ Update Levels ---------------------------------------------------------------------
Public Sub UpdateLevels()
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("tblAgents", dbOpenDynaset)
Do While Not rs.EOF
rs.Edit
rs!AgentLevel = GetLevel(rs!agentPK)
rs.Update
rs.MoveNext
Loop
End Sub
Public Function GetLevel(ID As Long) As Integer
Dim ParentID As Long
Dim rs As DAO.Recordset
Set rs = CurrentDb.OpenRecordset("Select * from tblAgents where AgentPK = " & ID, dbOpenDynaset)
If Not (rs.EOF And rs.BOF) Then
If IsNull(rs!ReportsTo) Then
GetLevel = 1
Else
GetLevel = RecurseLevels(rs!ReportsTo)
End If
End If
End Function
Private Function RecurseLevels(ParentID As Long, Optional Level As Integer = 1)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where AgentPK = " & ParentID
' Debug.Print strSql
' Debug.Print "Level " & Level
Set rs = CurrentDb.OpenRecordset(strSql)
Level = Level + 1
Do While Not rs.EOF
ParentID = Nz(rs!ReportsTo, 0)
If ParentID <> 0 Then
RecurseLevels ParentID, Level
End If
rs.MoveNext
Loop
RecurseLevels = Level
End Function
'---------------------------------------------------------- Update Sales Tree ---------------------------------------------------------------
Public Sub ClearSalesTree()
CurrentDb.Execute "qryDeleteSalesTree"
End Sub
Public Sub UpdateSalesTree()
Dim rs As DAO.Recordset
Dim strSql As String
Set rs = CurrentDb.OpenRecordset("select * from tblAgents Order By AgentLevel, AgentPK")
Do While Not rs.EOF
Debug.Print rs!agentPK & " Agent"
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & rs!agentPK & ", " & rs!agentPK & ", " & GetStoredLevel(rs!agentPK) & ", " & GetStoredLevel(rs!agentPK) & ")"
CurrentDb.Execute strSql
UpdateAgentSalesTree rs!agentPK, rs!agentPK
rs.MoveNext
Loop
End Sub
Public Sub UpdateAgentSalesTree(AgentID As Long, StartingAgentID As Long)
Dim rs As DAO.Recordset
Dim strSql As String
strSql = "Select * from tblAgents where ReportsTo = " & AgentID
Set rs = CurrentDb.OpenRecordset(strSql)
Do While Not rs.EOF
AgentID = rs!agentPK
strSql = "Insert INTO tblSalesTree (AgentID, ChildID, AgentLevel, ChildLevel) VALUES (" & StartingAgentID & ", " & AgentID & ", " & GetStoredLevel(StartingAgentID) & ", " & GetStoredLevel(AgentID) & ")"
Debug.Print strSql
CurrentDb.Execute strSql
UpdateAgentSalesTree AgentID, StartingAgentID
rs.MoveNext
Loop
End Sub
Public Function GetStoredLevel(AgentID As Long) As Long
If DCount("*", "tblAgents", "agentPK = " & AgentID) = 0 Then
Err.Raise 9999, , "No such Agent Exists with PK of " & AgentID
End If
GetStoredLevel = DLookup("AgentLevel", "TblAgents", "AgentPK = " & AgentID)
End Function
All of that does just two things.
1. Determines the Agent's Level and saves to the Agent Table. This would need to update when you add, edit, delete and employee
2. Creates what I call the Agent Tree. This is a table that has records showing every Agent and every Agent below them.
subFrmAgentTree subFrmAgentTree
AgentID
AgentName
AgentLevel
ChildID
ChildName
ChildLevel
1
A
1
1
A
1
1
A
1
3
C
2
1
A
1
5
E
2
1
A
1
6
F
3
1
A
1
7
G
3
2
B
1
2
B
1
2
B
1
4
D
2
3
C
2
3
C
2
3
C
2
6
F
3
3
C
2
7
G
3
4
D
2
4
D
2
5
E
2
5
E
2
6
F
3
6
F
3
7
G
3
7
G
3
Once the Agent Tree is created it only needs to be updated once you add, edit, delete an employee. Adding, editing sales or changing the Royalty percentages does not require an update. The button to run the code is on the last tab.
My sales table was very simple.
tblAgentSales tblAgentSales
SaleID
AgentID_FK
SaleAmount
10
1
$10.00
11
2
$10.00
12
3
$10.00
13
4
$10.00
14
5
$10.00
15
6
$10.00
16
7
$10.00
There is no date, no link to a product, etc. You would have to expand the sales table to include DateSold. I assume you would also add a foreign key to Product Sold.
If you have a date in the sales table then you add that date to the Royalty Details Query. Then in the Royalty Totals Query you could filter or group by a period of time.
This is a complex multi-layered scheme. The closest I've ever come to something like this was a genealogy database. It also required recursion and was definitely difficult, since my data source was subject to many inaccuracies caused by the limited accuracy and limited durability of the original recording medium. (Some churches that had held family birth/death/marriage records at one time or another before the age of computers might have burned down, for example.)
I had an approach to build family trees as junction tables based on parentage. Even then, when you had multiple marriages involving one of the parents, it became tricky sometimes. Trying to decide how you are related to some distant relative requires two searches to go up the parent paths (you and the potential distant relative) until the searches converge at the common ancestor. Then you can decide how (or even if) you are related.
The question of finding relationships in the family tree environment has to about as hard as the determination of royalty percentages. One issue I see is a natural (mathematical) limit for your level vs. royalty scheme. You can only go 3 levels deep because your 40%/30%/20%/10% scheme reaches 100% of the total at the level-3 bracket. Nothing is left for the level-4 bracket if you had one.
Excel would be very much harder because each cell in a worksheet is essentially independent of each other cell. The only way is to express some function of a targeted cell. That independence makes it harder to express and evaluate relationships among cells that are related by business. There is no simple way within a single cell to express multiple variable relationships (such as a flow-through from more than one higher level.)
I will not step into MajP's work, but the general answer to your question is that when you disburse some of these earnings, you just make a record of each such payout with the agent's name or ID in the payment records. You don't need to remember which branch of the tree led to that person being paid. Just look at the records that enumerate who got paid and now much with each payment. Not everything about this scheme depends on the pyramid. For example, the fact of a sale being made doesn't depend on the pyramid. Only the royalties that go to other people have that dependency.
i just do the db..and knowing the people i reckon 4 or 5 agents will be there..And after that i don't see any sales from them 