ok i will see that in a second time, first need to learn this
NO. You just add sales. The royalties are calculated based on the rules in this table. Once the agents are added, and the Royalty Assignments are correct then you simply add sales.
| 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 |
main agency is the main office that employes all the agent
no..a acces file is mnore than enough...we ar enot going to talk about many agents included in the whole structure..i think form 10 to 15 is more than what i could reasonable expect.
If that is the case I would put Main Agency in the Agent Table, and have the level 1 Agents report to the Main Agencies.
| Recieving Agent Level | Selling Agent Level | Percent |
|---|---|---|
0 | 1 | |
0 | 2 | |
0 | 3 | 0.4 |
1 | 1 | |
1 | 2 | |
1 | 3 | 0.3 |
2 | 2 | |
2 | 3 | 0.2 |
3 | 3 | 0.1 |
| Recieving Agent Level | Selling Agent Level | Percent |
|---|---|---|
0 | 1 | |
0 | 2 | 0.5 |
0 | 3 | 0.4 |
1 | 1 | |
1 | 2 | 0.3 |
1 | 3 | 0.3 |
2 | 2 | 0.2 |
2 | 3 | 0.2 |
3 | 3 | 0.1 |
Ahhh. Because percentages are normally less that 1 in most places. Is that a trick question.
Again I might be over thinking this but I doubt anyone could talk the OP though a potential solution, and I imagine few people on this forum could even formulate a potential solution. Again, I open this up to a potential alternate solution.
not sur i understand..
If the value in the sales table is already a total commission generated then it should work as designed. I called it a sales Amount but if it is a Total Generated Commission then all is good.
Not sure how to be any clearer. This table shows exactly the scheme for sales at any level. What specifically do you not understand? I walked you though this with examples.
| AgentID_FK | SaleAmount | DateSold |
|---|---|---|
1 | $10.00 | 6/1/2023 |
2 | $10.00 | 6/1/2023 |
3 | $10.00 | 6/1/2023 |
4 | $10.00 | 7/1/2023 |
5 | $10.00 | 7/1/2023 |
6 | $10.00 | 7/1/2023 |
7 | $10.00 | 7/1/2023 |
| Recieving Agent Level | Selling Agent Level | Percent |
|---|---|---|
0 | 1 | 0.6 |
0 | 2 | 0.5 |
0 | 3 | 0.4 |
1 | 1 | 0.4 |
1 | 2 | 0.3 |
1 | 3 | 0.3 |
2 | 2 | 0.2 |
2 | 3 | 0.2 |
3 | 3 | 0.1 |
| TopLevel.AgentPK | TopLevel.AgentName | Level1.AgentPK | Level1.AgentName | Level2.AgentPK | Level2.AgentName | Level3.AgentPK | Level3.AgentName |
|---|---|---|---|---|---|---|---|
9 | Main Agency | 1 | A | 3 | C | 6 | F |
9 | Main Agency | 1 | A | 3 | C | 7 | G |
9 | Main Agency | 2 | B | 4 | D | | |
9 | Main Agency | 1 | A | 5 | E | |
| SaleID | Agent | SaleAmount | DateSold |
|---|---|---|---|
15 | F | $10.00 | 7/1/2023 |
| SaleID | SellerAgent | SellerLevel | SellerName | SaleAmount | Percent | RoyaltyRecievingAgent | RoyaltyLevel | RoyaltyRecievingAgentName | RoyaltyAmount | DateSold |
|---|---|---|---|---|---|---|---|---|---|---|
| 15 | 6 | 3 | F | $10.00 | 0.4 | 9 | 0 | Main Agency | $4.00 | 7/1/2023 |
| 15 | 6 | 3 | F | $10.00 | 0.3 | 1 | 1 | A | $3.00 | 7/1/2023 |
| 15 | 6 | 3 | F | $10.00 | 0.2 | 3 | 2 | C | $2.00 | 7/1/2023 |
| 15 | 6 | 3 | F | $10.00 | 0.1 | 6 | 3 | F | $1.00 | 7/1/2023 |
It works as far as I can tell on the way I interpreted it. However, I may not have interpreted this correctly. Make sure to download Version 4. The other version was giving royalties to everyone in the level above not just the superior.
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 = 0
Else
GetLevel = RecurseLevels(rs!ReportsTo)
End If
End If
End Function
Private Function RecurseLevels(ParentID As Long, Optional Level As Integer = 0)
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 FunctionPublic Sub UpdateLevels()
Dim db As DAO.Database
Set db = CurrentDb
' init 1. level:
db.Execute "update tblAgents set AgentLevel = iif(ReportsTo Is NULL, 0, null)"
' calc next levels:
UpdateAgentLevel db, 0
End Sub
Private Sub UpdateAgentLevel(ByVal db As DAO.Database, ByVal Level As Long)
Dim qdf As DAO.QueryDef
Dim RecCnt As Long
'Const UpdateSql As String = "Parameters ParentLevel long;" & _
" UPDATE tblAgents AS C INNER JOIN tblAgents AS P ON P.AgentPK = C.ReportsTo" & _
" SET C.AgentLevel = [P].[AgentLevel]+1" & _
" where P.AgentLevel = [ParentLevel]"
'Set qdf = db.CreateQueryDef("", UpdateSql)
' ... or use saved query:
Set qdf = db.QueryDefs("qryAgentLevelUpdate")
Do
qdf.Parameters("ParentLevel").Value = Level
qdf.Execute dbFailOnError
RecCnt = qdf.RecordsAffected
Level = Level + 1
Loop Until RecCnt = 0
End SubI didn't claim I understood it.
ok doing some test
