link record to another record in the same table

[stefanocps]

The structure does not change, but when you change the agent table (add, edit, delete an agent) it archives the organization. So there are now archive tables. This is all done automatically. The user only selects "reports" to and add new agents.
NEVER delete an agent. Move them under inactive

The query to process the royalties is now very complex. It determines the royalties based on the organization at the time of sale. It is complex for me, but it appears to work correctly.

Here is another update that allows you to view the archived organizations.

View attachment 108311

As I said in the beginning, I believe to do this correctly it is complicated. There are few people who can do this. There are a lot of different pieces that have to come together for this to work.
This structure can do the following
1. Allow any number of levels
2. easily assign and reassign Agents
3. handles any royalty scheme
4. handles changes to organization and assigns royalties based on the organization at time of sale.

as you know it is really complicateted..imagine how it is for me
The things i am focusesing now is when somethign chag en the sctrucutre. You says it is automatically archived ok
But data are still available if igo to query the time when the structure was not changed right?
And at the same time te nowdays queires are referring to the new structure..
is it?

 

[MajP]

But data are still available if igo to query the time when the structure was not changed right?
And at the same time te nowdays queires are referring to the new structure..

If Modified the organization on 4/1 and 5/1. I would archive these two organizations.

The royalties for a sale done on 4/30 would calculate using the organization structure that was in place on 4/1
The royalties for a sale done on 6/5 would use the organization structure that was in place on 5/1

They are pushed to the "Processed" table. All reports are then based off of processed royalties.

 

[stefanocps]

If Modified the organization on 4/1 and 5/1. I would archive these two organizations.

The royalties for a sale done on 4/30 would calculate using the organization structure that was in place on 4/1
The royalties for a sale done on 6/5 would use the organization structure that was in place on 5/1

They are pushed to the "Processed" table. All reports are then based off of processed royalties.

yes ok it is what i meant...the royalties are calculate according to the current structure, and are shwon according to the structure at moment of sale

 

[MajP]

yes ok it is what i meant...the royalties are calculate according to the current structure, and are shwon according to the structure at moment of sale

We may be saying the same thing, but lost in translation.

Assume F was under C on 4/1 and then F was moved under E on 5/1
Assume F sold something on 4/15.
Assume the sale is processed on 6/5.
(For this problem assume C is active but F moved to E)

The royalties would go up to C the supervisor at the time of the sale, not to E the current supervisor.

If that is not the case and the new supervisor should get royalties instead, then need to know that.

 

[stefanocps]

We may be saying the same thing, but lost in translation.

Assume F was under C on 4/1 and then F was moved under E on 5/1
Assume F sold something on 4/15.
Assume the sale is processed on 6/5.
(For this problem assume C is active but F moved to E)

The royalties would go up to C the supervisor at the time of the sale, not to E the current supervisor.

If that is not the case and the new supervisor should get royalties instead, then need to know that.

yes it is like this
It's even difficult to make a test of this scenery on th eDB now... but i trust it works !

 

[MajP]

but i trust it works

"Trust but verify." This is complex but so far my initial tests appear valid

 

[MajP]

Fixes query on sales on only show unprocessed sales.

 

[stefanocps]

so it should be more or less completed and working now..i think 1 important report is still needed..the one of sales by month..with month to be choosen before outputting the record..what you think?

 

[MajP]

one of sales by month..with month to be choosen before outputting

If you build that report then I will show you how to make it filterable by time period.

Probably want others. You can make a base report first without grouping and then make others grouped in different ways.

Sales by month
Sales un grouped
Sales grouped by Seller, by month
Sales grouped by Seller

Royalties by Agent by month
Royalties by Agent

 

[stefanocps]

to buil dthese records i need to use the queries as data source?for example the query royalties by month is ht eo ne i should use for the report with filtering which month..right?

 

[MajP]

All of the existing queries are very important to make this application work. I would not use the existing queries to be safe. Instead you can make a copy and use them as a starting point. I would do the following.

1. Right click on a query and select "Copy"
2. Save your copied query with a new name. Since these are going to be for reports you can name
qryRoyaltyDetails to r_qryRoyaltyDetails. Then you only work with the queries starting with r_ to be safe
3. Edit your new query if you want
4. Use the report wizard to build a report'.
5. Once built you will need to clean it up to make it look nice. The wizard helps but it will still need editing

Building good reports is an "art form". You can do a lot of fancy formatting and presentation using "Sorting and Grouping". I would spend some time looking at internet videos.

 

[stefanocps]

All of the existing queries are very important to make this application work. I would not use the existing queries to be safe. Instead you can make a copy and use them as a starting point. I would do the following.

1. Right click on a query and select "Copy"
2. Save your copied query with a new name. Since these are going to be for reports you can name
qryRoyaltyDetails to r_qryRoyaltyDetails. Then you only work with the queries starting with r_ to be safe
3. Edit your new query if you want
4. Use the report wizard to build a report'.
5. Once built you will need to clean it up to make it look nice. The wizard helps but it will still need editing

Building good reports is an "art form". You can do a lot of fancy formatting and presentation using "Sorting and Grouping". I would spend some time looking at internet videos.

sure..what i meant was..start from queies and not from table!

 

[MajP]

sure..what i meant was..start from queies and not from table!

Most reports are based off queries in a normalized database. Because to be useable information normally comes from multiple tables.

For Example here is the table tblProcessedRoyalties
tblProcessedRoyalties tblProcessedRoyalties

ID	SaleID_FK	OrganizationEffectiveDate	SellerLevel	RoyaltyAgent_ID_FK	RoyaltyAgentLevel	RoyaltyAgentPercent	RecievingAgentActive	RoyaltyNote
61​	15​	4/1/2023​	3​	9​	0​	0.4​	No​
62​	15​	4/1/2023​	3​	1​	1​	0.3​	No​
63​	15​	4/1/2023​	3​	9​	0​	0.2​	No​	Royalty from Inactive Agent: C
64​	15​	4/1/2023​	3​	6​	3​	0.1​	No​
65​	18​	5/1/2023​	3​	9​	0​	0.4​	No​
66​	18​	5/1/2023​	3​	1​	1​	0.3​	No​
67​	18​	5/1/2023​	3​	5​	2​	0.2​	No​
68​	18​	5/1/2023​	3​	7​	3​	0.1​	No​
69​	19​	6/6/2023​	3​	9​	0​	0.4​	No​
70​	19​	6/6/2023​	3​	13​	1​	0.3​	No​
71​	19​	6/6/2023​	3​	4​	2​	0.2​	No​
72​	19​	6/6/2023​	3​	7​	3​	0.1​	No​

Not very useable by itself, because of all the foreign keys. Here is the query linking the other tables and properly sorted.

qryProcessedRoyalties qryProcessedRoyalties

SellingAgentName	SellerLevel	SaleAmount	DateSold	RoyaltyAgentName	RoyaltyAgentLevel	RoyaltyAgentPercent	DateRoyaltiesProcessed	RoyaltyAmount	RoyaltyNote	OrganizationEffectiveDate
F	3​	$10.00​	4/2/2023​	Main Agency	0​	0.4​	6/6/2023​	$4.00​		4/1/2023​
F	3​	$10.00​	4/2/2023​	A	1​	0.3​	6/6/2023​	$3.00​		4/1/2023​
F	3​	$10.00​	4/2/2023​	Main Agency	0​	0.2​	6/6/2023​	$2.00​	Royalty from Inactive Agent: C	4/1/2023​
F	3​	$10.00​	4/2/2023​	F	3​	0.1​	6/6/2023​	$1.00​		4/1/2023​
G	3​	$20.00​	5/2/2023​	Main Agency	0​	0.4​	6/6/2023​	$8.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	A	1​	0.3​	6/6/2023​	$6.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	E	2​	0.2​	6/6/2023​	$4.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	G	3​	0.1​	6/6/2023​	$2.00​		5/1/2023​
G	3​	$30.00​	6/7/2023​	Main Agency	0​	0.4​	6/6/2023​	$12.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	B	1​	0.3​	6/6/2023​	$9.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	D	2​	0.2​	6/6/2023​	$6.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	G	3​	0.1​	6/6/2023​	$3.00​		6/6/2023​

A well designed database is like a puzzle which each piece a table. Looking at one piece provides little information. It is not until they are all together and put in the correct order that you can make out all of the information.

 

[stefanocps]

Most reports are based off queries in a normalized database. Because to be useable information normally comes from multiple tables.

For Example here is the table tblProcessedRoyalties
tblProcessedRoyalties tblProcessedRoyalties

ID	SaleID_FK	OrganizationEffectiveDate	SellerLevel	RoyaltyAgent_ID_FK	RoyaltyAgentLevel	RoyaltyAgentPercent	RecievingAgentActive	RoyaltyNote
61​	15​	4/1/2023​	3​	9​	0​	0.4​	No​
62​	15​	4/1/2023​	3​	1​	1​	0.3​	No​
63​	15​	4/1/2023​	3​	9​	0​	0.2​	No​	Royalty from Inactive Agent: C
64​	15​	4/1/2023​	3​	6​	3​	0.1​	No​
65​	18​	5/1/2023​	3​	9​	0​	0.4​	No​
66​	18​	5/1/2023​	3​	1​	1​	0.3​	No​
67​	18​	5/1/2023​	3​	5​	2​	0.2​	No​
68​	18​	5/1/2023​	3​	7​	3​	0.1​	No​
69​	19​	6/6/2023​	3​	9​	0​	0.4​	No​
70​	19​	6/6/2023​	3​	13​	1​	0.3​	No​
71​	19​	6/6/2023​	3​	4​	2​	0.2​	No​
72​	19​	6/6/2023​	3​	7​	3​	0.1​	No​

Not very useable by itself, because of all the foreign keys. Here is the query linking the other tables and properly sorted.

qryProcessedRoyalties qryProcessedRoyalties

SellingAgentName	SellerLevel	SaleAmount	DateSold	RoyaltyAgentName	RoyaltyAgentLevel	RoyaltyAgentPercent	DateRoyaltiesProcessed	RoyaltyAmount	RoyaltyNote	OrganizationEffectiveDate
F	3​	$10.00​	4/2/2023​	Main Agency	0​	0.4​	6/6/2023​	$4.00​		4/1/2023​
F	3​	$10.00​	4/2/2023​	A	1​	0.3​	6/6/2023​	$3.00​		4/1/2023​
F	3​	$10.00​	4/2/2023​	Main Agency	0​	0.2​	6/6/2023​	$2.00​	Royalty from Inactive Agent: C	4/1/2023​
F	3​	$10.00​	4/2/2023​	F	3​	0.1​	6/6/2023​	$1.00​		4/1/2023​
G	3​	$20.00​	5/2/2023​	Main Agency	0​	0.4​	6/6/2023​	$8.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	A	1​	0.3​	6/6/2023​	$6.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	E	2​	0.2​	6/6/2023​	$4.00​		5/1/2023​
G	3​	$20.00​	5/2/2023​	G	3​	0.1​	6/6/2023​	$2.00​		5/1/2023​
G	3​	$30.00​	6/7/2023​	Main Agency	0​	0.4​	6/6/2023​	$12.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	B	1​	0.3​	6/6/2023​	$9.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	D	2​	0.2​	6/6/2023​	$6.00​		6/6/2023​
G	3​	$30.00​	6/7/2023​	G	3​	0.1​	6/6/2023​	$3.00​		6/6/2023​

A well designed database is like a puzzle which each piece a table. Looking at one piece provides little information. It is not until they are all together and put in the correct order that you can make out all of the information.

look i have added a new report to last sheet of th emask
You need to insert the 2 date dd/mm/yyyy..(i coudn't find how to insert it from a calendar)
it seems working

 

[MajP]

You need to insert the 2 date /dd/mm/yyyy..(i coudn't dinf how to insert it from a calendar)

This seems like an Access bug or just a poor feature. If you use an input mask, then you do not get the pop up calendar even if the input mask forces a date format. Does not make sense. You can leave the format and remove the input mask if you want the calendar. I hate input masks and instead validate the value after input.
I assume this is Italian? It converts the word "Forms" to "Maschere" which is Masks and not "Forme". Does maschere also mean forms?

 

[gemma-the-husky]

@MajP has mentioned several times that I see this as a trivial problem. In terms of the basic organisation as described
there are just 3 levels so a simple 3 level query with each level joined to sales will do the job. But this assumes the world is perfect and does not change.

I pointed out that the OP had not considered what happens when someone leaves or changes roles within the organisation and urged them to clarify all the business rules. On the other forum this was not really answered. Perhaps it has here.

in my experience managing those changes within the application is where the complexity arises - and clear business rules need to be determined.

my experience with commissions is with large organisations with a variety of reward plans fixed for a period and with occasional strategic short term plans to meet an unforeseen threat or opportunity. Not to mention organisational change. Typical annual spend on rewards would be £20-£30m with around 1000 payees, reward typically being around 50% of total earnings.

Over the last 20 odd years I have saved my clients around £250m annually through clarifying the rules and applying them correctly. I should point out this figure includes payments to external agencies as well as employees. External agencies get paid a far higher percentage of the sale.

As far as this thread is concerned with a quick scan through it seems the rules are still unclear and trying to tackle changes and new requirements on the fly as and when the thought occurs is not the way to go. OP should sit down and consider and document all eventualities and requirements, including reporting before starting on the table/relationship design.

I haven’t read the whole if this thread so perhaps the following has been resolved

The original concept of a code ‘ABA’, ‘ABB’ etc is on the face of it a simple solution, but fraught with issues, typo’s being one. But since these are allocated to an individual they cannot be reused, so there is a limit of 26 individuals per level/group. So make it a role identifier rather than an individual. Then you have the issue of when someone starts to occupy that role, or moves on from it. Or for a short period perhaps two people share the role

is there a training period? Does that impact payments?

What happens when someone leaves? Do they continue to be paid for a period of time?

If someone is paid incorrectly - either too much or too little - how do you resolve that? Make a separate payment? Correct the following month? How does that impact on the historical record (e.g.for reporting)

these are just some of the questions I would be asking.belonged to agent

You may have touched on this,but you also get historical agencies. Sales Agent 1 used to deal with a certain customer, but now the customer is allocated to Sales Agent 2. Does the history that was formerly allocated to agent 1 now belong to agent 2. Your table structure might need to change depending on the answer.

Alternatively if agent 1 leaves, and a new agent is appointed do you have to create a new agent record, and reallocate all the agent 1 customers, or can you just rename the employee who is agent 1.

 

[stefanocps]

This seems like an Access bug or just a poor feature. If you use an input mask, then you do not get the pop up calendar even if the input mask forces a date format. Does not make sense. You can leave the format and remove the input mask if you want the calendar. I hate input masks and instead validate the value after input.
I assume this is Italian? It converts the word "Forms" to "Maschere" which is Masks and not "Forme". Does maschere also mean forms?

yes maschere means form
I also hate the input mask..but how to sort? it is not nice to let the uiser write the date..there must be a proper calendar control
Also look i have added another report

Just tell me if it the correct way to go on..i put the filter condition in the query i use for report

p.s. now i am not concentrating on the report format but more on the filtering possibilities
p.s. 2 yes i have sort the date..now i have calendar

 

[MajP]

You may have touched on this,but you also get historical agencies. Sales Agent 1 used to deal with a certain customer, but now the customer is allocated to Sales Agent 2. Does the history that was formerly allocated to agent 1 now belong to agent 2. Your table structure might need to change depending on the answer

This demo does not go down to that level. The starting point is a record comes in assigned to an Agent. There is no customer table relating customers to agents. The starting record includes an Agent, Amount to be divided up, and a date.

The starting point is described here

link record to another record in the same table

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

www.access-programmers.co.uk

However, I do think that level of detail could be added for a different problem and the structure would support it because these royalties are processed an pushed into a table.

If you had a customer table, then you would need an junction table. This would show assignments by date

tblCustomers_Agents
-- CustomerID_FK
-- AgentID_FK
-- DateAssigned

That would then get you to the same starting point.

 

[MajP]

Just tell me if it the correct way to go on..i put the filter condition in the query i use for report

p.s. now i am not concentrating on the report format but more on the filtering possibilities
p.s. 2 yes i have sort the date..now i have calenda

There are many ways to do this. I prefer not to put the control parameter references in the query. I find it limiting and hard to debug. Instead I build the report with no criteria or control references. I then use the Docmd.Openreport and pass in the where condition. This way I can use one report and have a form that can call it many ways. With one report I could filter by
date range
Seller ID
Agent ID
Sale Amount
....

Or any combination that I can pass in.

 

[Gasman]

I am going to make the one hundredth post.
This DB definitely needs to go into Sample Databases when this thread ends.