link record to another record in the same table

[MajP]

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

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.

 

[MajP]

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

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​

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%

 

[GPGeorge]

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.

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.

 

[stefanocps]

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

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​

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?

 

[stefanocps]

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...

 

[MajP]

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 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​

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 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​

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.

 

[ebs17]

..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.

 

[MajP]

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.

Question - calculate inbreeding

I do have a difficult project that is similar to what you are on working, that is why I approach you. OK sounds interesting. I would just post it and people will reply. It may be an area that someone has expertise. Unless it is really COI because now I know more about that than ever wanted...

www.access-programmers.co.uk

Here

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...

www.access-programmers.co.uk

Here

Looking for some direction.

I can demo this if I can decipher it. Here is a cut away section to try to get some clarity. Each box is an assembly of serialized parts. The header contains: Assembly Part Number, Assembly Name, Record Document Part Number Below the header is the list of serialized components that make up...

www.access-programmers.co.uk

Here

Solved - Access VBA - Loop through files in Folders, Subfolders, Sub-Sub Folders....

Hi, Before I write my own nested nested nested loop, surely someone has something out there already.... I need to list all the files in a single 'base' folder, which includes subfolders of the base folder. and subfolders of the subfolder's subfolders. The nesting of the subfolders is assumed...

www.access-programmers.co.uk

Here

Is it possible to create a recursive sum in ms access by sql query or vba

t is impossible that there is no solution to this problem. In any way, I want to activate this query update accounts without disrupting the user Is it reasonable that not all Access programmers have a solution? I will say it one more time, not sure what is not clear. If you are stuck on doing...

www.access-programmers.co.uk

 

[MajP]

So I added "Main Agency" into the Agent table and modified the code to make this level 0.

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.

 

[Isaac]

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.

 

[stefanocps]

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)?

 

[MajP]

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_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​

SaleAmount is really Total Commission Generated.
In the table it shows every agent brought in $10.

 

[MajP]

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 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​

Here are the relations
qryHierarchy qryHierarchy

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	​

or
Main Agency
-- A
----- C
--------- F
--------- G
----- E
-- B
----- D

With a single sale of $10
subFrmAgentSales subFrmAgentSales

SaleID	Agent	SaleAmount	DateSold
15​	F	$10.00​	7/1/2023​

F is level 3 under C, A, and Main

Commission details
subFrmRoyaltyDetails subFrmRoyaltyDetails

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

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!

 

[stefanocps]

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!

 

[MajP]

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.

 

[Josef P.]

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):

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:

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).

 

[GPGeorge]

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.

 

[stefanocps]

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

 

[stefanocps]

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?

 

[MajP]

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

You do not do this manually. It is done via code to ensure it is correct. The code counts the number of supervisors in the chain. This way if any agent is added or removed the levels will automatically update.
In real life with lots of branches and levels it would be near impossible to do this manually. So this is much safer, faster, and more accurate to update in code.

May happen that an agent leave
In this case that part of royalties would go to MAIN AGENCY
How can we mange this?

I did not add a relationship diagram to this database, but I should. You can enforce referential integrity, which means every child must report to another record in the database. Assume you have

-Main
--A
----B
--------C
----D
----E

Assume Employee B leaves. If Referential Integrity is established then you cannot delete B since it has a child C. You need to first reassign C. I would assume C gets reassigned to A becoming a second level not to Main. You would choose a new "reports to" for C and then delete B. I would assume C gets reassigned to A becoming a second level not to Main.

But if you are suggesting that if B leaves C is still considered a 3rd level without a 2nd level supervisor then that cannot be done. This structure does not support where C is considered 3rd level but does not have a 2nd level supervisior.

-Main
--A (lvl 1)
--------C (lvl 3)
----D
----E

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..

Normally there is no reason to look at a PK. It is only used to relate records. Main is 9 because I added it after all the other records. The levels are calculated in code and has no relation to the PK. When you assign a record A to Main you go to the combo box and pick "Main". In the background it stores the key 9 in A's report to. All you see is reports to "Main", but in fact 9 is stored in the table.