link record to another record in the same table

My sales table was very simple.
tblAgentSales tblAgentSales

SaleIDAgentID_FKSaleAmount
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.
ok i will see that in a second time, first need to learn this
help me to understand
like it is now every sales just add the amount of royalties to each agent(in case the agent is included) right?

imagine i have a report every month of the income genrated by agent 1, agent 2 ecc

And every mont i have to calculate the amount of royalties to share among agents..
for this case the db is not ready..right?
 
For demonstration I added a date sold field to show that you can make queries and get royalties per period. I simply did a query by month

ByMonth.png


So you can see for Agent 1 they go 2.50 total commissions 1.50 in Jun and 1.00 in July. Once you add the date you can get any period you want.
 
Last edited:
ike it is now every sales just add the amount of royalties to each agent(in case the agent is included) right?
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.
Only need to run the update button if employees add, reports to edited, or agent deleted

subFrmRoyaltyAssignments subFrmRoyaltyAssignments

IDAgentLevelLevelSoldPercent
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​
I am not sure I understand your rules, but the above rules tell you what percentage of the royalty each level gets based on the level sold. You need a record for level sold and then a record for each level above or equal.. Not sure what main agency is, but then would have to be added to the Agent table and the above table. In that way all level one agents report to Main Agency.
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%
 
Two points.

First, the new Microsoft Northwind Templates illustrate the use of a self-referencing PK/FK in the employee table. Each employee is assigned to a supervisor. You can see that in action in the template.

Second, I had a similar genealogy problem in a family database. I record two parents for each individual, if they are known. Fortunately, the back end is in SQL Server. I was able to create a recursive view in T-SQL that allows me to show each individual with as many of their direct ancestors as I have records for.
1685629055592.png

Here's how the recursive CTE works: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/

I doubt it's possible to replicate that with an Access query, unfortunately. However, if you can use a SQL Server or SQL Azure back end, recursive CTE's, like other CTE's, can be very powerful.
 
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.
Only need to run the update button if employees add, reports to edited, or agent deleted

subFrmRoyaltyAssignments subFrmRoyaltyAssignments

IDAgentLevelLevelSoldPercent
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​
I am not sure I understand your rules, but the above rules tell you what percentage of the royalty each level gets based on the level sold. You need a record for level sold and then a record for each level above or equal.. Not sure what main agency is, but then would have to be added to the Agent table and the above table. In that way all level one agents report to Main Agency.
main agency is the main office that employes all the agent
Main agency always get a percentage then
So in case there are 3 lines of agents, the income would be share for 4 subjects
In case there is only one line the income would be shared for 2 subjects

It's not easy to understand the db you made .. allow me some time. F>or example the level sold..i supose is a "trick" you made to manage the dirrent kind of shar eincase there ares,3, or 4 subjects to share..is it?
What confuse me is that percent you use..why all those small number?
 
Two points.

First, the new Microsoft Northwind Templates illustrate the use of a self-referencing PK/FK in the employee table. Each employee is assigned to a supervisor. You can see that in action in the template.

Second, I had a similar genealogy problem in a family database. I record two parents for each individual, if they are known. Fortunately, the back end is in SQL Server. I was able to create a recursive view in T-SQL that allows me to show each individual with as many of their direct ancestors as I have records for.
View attachment 108225
Here's how the recursive CTE works: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/

I doubt it's possible to replicate that with an Access query, unfortunately. However, if you can use a SQL Server or SQL Azure back end, recursive CTE's, like other CTE's, can be very powerful.
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.
This is why the need for somehting easy and small...
 
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 that is the case I would put Main Agency in the Agent Table, and have the level 1 Agents report to the Main Agencies.
I would modify the code to identify the Main Agency as Level 0

Your table for a Level 3 sale is populated below
subFrmRoyaltyAssignments subFrmRoyaltyAssignments

Recieving Agent LevelSelling Agent LevelPercent
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​
if 3 sells then
Ma level 0: 40%
lvl 1 Superior: 30%
lvl 2 Superio: 20%
Seller level 3: 10%

Adding level 2 rules
subFrmRoyaltyAssignments subFrmRoyaltyAssignments

Recieving Agent LevelSelling Agent LevelPercent
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​
If level 2 sells
MA: 50%
lvl1: 30%
lvl2 seller: 20%

So this shows if Level 3 makes the sale then MA gets .4, Level 1 gets .3, (


What confuse me is that percent you use..why all those small number?
Ahhh. Because percentages are normally less that 1 in most places. Is that a trick question.
 
..and i do the db!
The way I see it: @MajP takes all the fun out of you and does the work on your own.
He earns a percentage for that.
 
The way I see it: @MajP takes all the fun out of you and does the work on your own
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.
Sometimes if all you have is a hammer everything looks like a nail. I might be guilty of that.

For me this is pretty easy to formulate a recursive solution because I have done this lots. Few examples.

Here
Here
Here
Here
 
Last edited:
So I added "Main Agency" into the Agent table and modified the code to make this level 0.
main.png

I added your rules for sales at level 2,3. You did not give me a rule for level 1 so I gave Main Agency 60% and Level 1 40%.

The only thing I do not think is correct is the Commission. I assume when you sell something the commision is a percentage of the sale and then the commision is split up based on the rules in the table.
So if level 3 sells somthing then
MA gets .4 of the commission, lvl 1 gets .3 of the commission.
currently they are getting .4 and .3 of the sales.

So these Percents are probably multiplied by something else.
I Assume the total commision is some percentage of sales lets say 20% of the sale. Then each level gets a percentage of the percentage. So all values in the above table are multiplied by .2.
 
Two points.

First, the new Microsoft Northwind Templates illustrate the use of a self-referencing PK/FK in the employee table. Each employee is assigned to a supervisor. You can see that in action in the template.

Second, I had a similar genealogy problem in a family database. I record two parents for each individual, if they are known. Fortunately, the back end is in SQL Server. I was able to create a recursive view in T-SQL that allows me to show each individual with as many of their direct ancestors as I have records for.
View attachment 108225
Here's how the recursive CTE works: https://www.sqlservertutorial.net/sql-server-basics/sql-server-recursive-cte/

I doubt it's possible to replicate that with an Access query, unfortunately. However, if you can use a SQL Server or SQL Azure back end, recursive CTE's, like other CTE's, can be very powerful.

Recursive CTE's are one of those funny, humbling things for me. I use them, because the examples given to use them are pretty short and easy to replicate, but I have to admit. I've sometimes stared at those 10 lines of code for 5 minutes and still feel like my brain doesn't totally and completely comprehend "why" they work.
 
So I added "Main Agency" into the Agent table and modified the code to make this level 0.
View attachment 108229
I added your rules for sales at level 2,3. You did not give me a rule for level 1 so I gave Main Agency 60% and Level 1 40%.

The only thing I do not think is correct is the Commission. I assume when you sell something the commision is a percentage of the sale and then the commision is split up based on the rules in the table.
So if level 3 sells somthing then
MA gets .4 of the commission, lvl 1 gets .3 of the commission.
currently they are getting .4 and .3 of the sales.

So these Percents are probably multiplied by something else.
I Assume the total commision is some percentage of sales lets say 20% of the sale. Then each level gets a percentage of the percentage. So all values in the above table are multiplied by .2.
not sur i understand..
all the calculation we are doing inthis table is what is to be shared.
let's say we got yhe monthly report of 1000 euro generated from a customer, john doe, linked to an agent in level3
John Doe has bought from a DEALER for 1500 euro
So those 1000 euro are already, of course a percentage, that the MA receive from DEALER
And those 1000 euro are those that need to be shared according to a certain scheme
In this case, being generated form LEVEL3, royalties go to LEvel3, level2,level1 and MA
Is that clear?

Still it is not clear the royalties assignemnt table:
this should be the scheme of percentage according to the different possibilities of sales?( if it made by level 3, level2, or level1)?
 
all the calculation we are doing inthis table is what is to be shared.
let's say we got yhe monthly report of 1000 euro generated from a customer linked to an agent in level3
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.

Still it is not clear the royalties assignemnt table:
this should be the scheme of percentage according to the different possibilities of sales?( if it made by level 3, level2, or level1)
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.
Look at the generated results. Do they match the expected royalties given the following money brought in
tblAgentSales tblAgentSales

AgentID_FKSaleAmountDateSold
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​
SaleAmount is really Total Commission Generated.
In the table it shows every agent brought in $10.
 

Attachments

Last edited:
Here is a demo with a single sale to show if it is working.
Rules showing how much commision each level gets for sales at lower level


Recieving Agent LevelSelling Agent LevelPercent
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​
Here are the relations
qryHierarchy qryHierarchy

TopLevel.AgentPKTopLevel.AgentNameLevel1.AgentPKLevel1.AgentNameLevel2.AgentPKLevel2.AgentNameLevel3.AgentPKLevel3.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
or
Main Agency
-- A
----- C
--------- F
--------- G
----- E
-- B
----- D

With a single sale of $10
subFrmAgentSales subFrmAgentSales

SaleIDAgentSaleAmountDateSold
15​
F
$10.00​
7/1/2023​
F is level 3 under C, A, and Main

Commission details
subFrmRoyaltyDetails subFrmRoyaltyDetails

SaleIDSellerAgentSellerLevelSellerNameSaleAmountPercentRoyaltyRecievingAgentRoyaltyLevelRoyaltyRecievingAgentNameRoyaltyAmountDateSold
1563F$10.000.490 Main Agency$4.007/1/2023
1563F$10.000.311A$3.007/1/2023
1563F$10.000.232C$2.007/1/2023
1563F$10.000.163F$1.007/1/2023
Based on the rules
Main agency gets .4
Level 1 Superior (A) gets .3
Level 2 Superiod (C) gets .2
Level 3 Seller (F) gets .1

WORKS FOR ME!
 
ok did not have really time to study it properly yet..i have plenty of info to take and it is not my language...so i need a while to sit and convetrate on the db and all the info you provide me
i know it works..it s me that still can t make it work!:D
 
i know it works..it s me that still can t make it work!
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.
 
A note on recursive calls: If the data are stored in tables anyway, you could process them in "levels".

Example: UpdateLevels in RoyaltyTreeV4:

recursive code (code in example db):
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 = 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 Function

Replacement:
Code:
Public 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 Sub

Could perhaps even be designed as a trigger (data macro).
 
Last edited:
Recursive CTE's are one of those funny, humbling things for me. I use them, because the examples given to use them are pretty short and easy to replicate, but I have to admit. I've sometimes stared at those 10 lines of code for 5 minutes and still feel like my brain doesn't totally and completely comprehend "why" they work.
I didn't claim I understood it. ;)
 
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.
ok doing some test
may be i have found a couple of issues

1) i have deleted the third level of agents for better understanding..and now i want to create a new third level agent
I can set the name, "the reports to", but it seems i cannot set the "agent level" field", just cannot wirte in..and it i not even automatically set (as it looks like should be, being impossibile to write in that field

edit:
i have see now that the subform of the agent table has that field "agent level" disabled. DOn't understand why..you do for protection? S if i need to insert a new agent i have to disable the protection?

2)In the royalties table the Royalties by month table don't get updated..unless i close the form and reopen it
edit:
it just missed a requery code in the button routine..i have corrected

one thing
In the ADD EMPLOYEE table the agent pk for MAIN AGENCY is 9
In ROYALTIES ASSIGNEMENT the RECEIVING AGENT LEVEL for MAIn AGENCY is 0...
Probably this 2 number should match (i know it is just for a clearer understanding, not for calculation) just the other LEVEL/PK match..is it correct?

Claculation seems correct though..i have tried to change percentage and it is fine..canot test ye the 3rd level as cannot add it for now
 
Last edited:
write in another post for better undertanding
After first test, and having checked that those "two issues"..are not really issues..
it looks that everything is fine
Stil..that "issues of main agent being 0 and 9 can be solved only rewriting the whole structure righ?

A query in the Royalties forms wher i can select a start month and end month that filter the royatiestotals and ryalties by month would be really useful

LAst but not least..an important consideration
May happen that an agent leave
In this case that part of royalties would go to MAIN AGENCY
How can we mange this?
 

Users who are viewing this thread

Back
Top Bottom