link record to another record in the same table (1 Viewer)

[stefanocps]

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.

Ok butnthe way you need writing code skill, right?may be not the best for me
I can also set the where condition at report level so i don t need to create a query copy for each report, is it?

 

[MajP]

Ok butnthe way you need writing code skill, right?may be not the best for me

That is true. In the short term you may be OK with macros and parameter queries, but you will definitely benefit from some simple vba.

I can also set the where condition at report level so i don t need to create a query copy for each report, is it?

If you look at my code attached to the buttons it will show you how to pass a where condition to a report. Understanding this would be a good way to learn some code writing. Unfortunately this app requires a lot of code on my part.

 

[stefanocps]

That is true. In the short term you may be OK with macros and parameter queries, but you will definitely benefit from some simple vba.

If you look at my code attached to the buttons it will show you how to pass a where condition to a report. Understanding this would be a good way to learn some code writing. Unfortunately this app requires a lot of code on my part.

your button stil have code though

i see that if use th eoption of the report "builtin macro" on the click property of the report i can also specify there some where condition..it is anothe possible option?

 

[MajP]

i see that if use th eoption of the report "builtin macro" on the click property of the report i can also specify there some where condition..it is anothe possible option?

Yes there are options using macros. Although they may be easier at first for new developers they are limited in what you can do. Very few people on this forum use macros and instead use VBA. But you can try first using macros and see if you can do what you want. I have very little experience using macros so may not be able to help.

your button stil have code though

I was suggesting that if you wanted to try code, there is an example.

 

[stefanocps]

yes right..in some cases reading your code i can write mine..i don't know vba but i know the logic of coding..so it might help

 

[MajP]

know the logic of coding

If you already know how to code in other code languages you likely will be better off learning to use vba if you plan to do more work in Access.

 

[stefanocps]

after few days i am working again at it, changing at first all the test to italian

I want to clear the datas..but i see i cannot remove agents..and may be the archived organizitation
How do i do that? i have set as inactive thge agents..but still cannot delete

Also if i change the status INACTIVE to INATTIVO..there will b e some problem?

one more thing:
when i do a change to the orgnization, i exit the mask and it says the organizaton will be saved, but when i open back in the date of change i cklcik and nothing is displayed on the next box

 

[stefanocps]

just to be synchronized i attach the db at this point

 

[gemma-the-husky]

Hello i am building a scheme for a multlevel marketing and i need to relate agents o other agents
View attachment 108196
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

I've not read the entire thread, but all you need is a table showing the teams, with a team leader, and a second table showing the team members.

If a member can only report to one other team member, then all you need is a ReportsTo field.

So F and G report to C. C and E report to A. A's team comprises those agents for whom ReportsTo is A. A full tree then includes those Agents who report to the members of A's immediate team, until you include all the agents who do not manage any subteams. Conceptually it's straightforward, but displaying the enter tree is quite difficult.

Sub teams would be traced by recursion, and effectively it works like a genealogy tree. A child can only have one father and one mother, and that's all you need to manage a complete family tree. (Strictly, although it's changed if you need to track marriages and so on)

For example, in a family tree, cousins are persons whose parents are siblings, where siblings are persons whose father and mother are the same. Etc.

 

[stefanocps]

I've not read the entire thread, but all you need is a table showing the teams, with a team leader, and a second table showing the team members.

If a member can only report to one other team member, then all you need is a ReportsTo field.

So F and G report to C. C and E report to A. A's team comprises those agents for whom ReportsTo is A. A full tree then includes those Agents who report to the members of A's immediate team, until you include all the agents who do not manage any subteams. Conceptually it's straightforward, but displaying the enter tree is quite difficult.

Sub teams would be traced by recursion, and effectively it works like a genealogy tree. A child can only have one father and one mother, and that's all you need to manage a complete family tree. (Strictly, although it's changed if you need to track marriages and so on)

For example, in a family tree, cousins are persons whose parents are siblings, where siblings are persons whose father and mother are the same. Etc.

thanks! MajP has done a big and fantastic work that is working for the pourpouse!

 

[MajP]

want to clear the datas..but i see i cannot remove agents..and may be the archived organizitation
How do i do that? i have set as inactive thge agents..but still cannot delete

I purposely made the Agents form to not allow deletions. Once you have real working data you never want to delete an Agent. When they leave you set the inactive date and move them under Inactive. In the short term while you are working on populating with real data you may want to go directly to the tables and delete data from both archive tables and the agents table

If you want to start putting real data in the model and delete the old you can just run all of the delete queries. Red Xs

This will clear out all of the old data except for the royalty percentages.

You can change Inactive to INNATIVO.
If you change Main Agency to Italian you must change one line of code

rsProcessed!RoyaltyAgent_ID_FK = DLookup("agentPK", "tblAgents", "AgentName = 'Main Agency'")

Change Main Agency to new name.

when i do a change to the orgnization, i exit the mask and it says the organizaton will be saved, but when i open back in the date of change i cklcik and nothing is displayed on the next box

The words "[Event Procedure]" were cleared from the listbox AfterUpdate event. May have happened when you were editing. Should work now.

 

[stefanocps]

i have just tried to manually delete all the table content but the one with commission
after that there is something that don't work properly anymore
so i really need to check what to delete
Of course i understand the fact that i cannot delete once "in production" but i am doing test an dbefore start i want to have all clear (commission can stay of course, i can change it easily
so i don't understand..shall i manually delete data from table or run the queries?

edit..i tried running the queries but i still see the data there

 

[stefanocps]

i have run the queries on your latest update version capital2..agent are cleared but not all the other tables are deleted, i see there is criteria "date() may be to be removed in the queries that don't delete. Also i don't see the organization being updated when changing yet.

 

[stefanocps]

yes i removed the date() criteria and the table is deleted..i don't know if i made something that should not be done anyway

anyway a coule of table don't have the queires to delete their data, the tablkes sales and subordinates

 

[MajP]

yes i removed the date() criteria and the table is deleted..i don't know if i made something that should not be done anyway

Do not do that. That will break something. You will never be able to archive.
Use this version. There is a new form called "FrmClearData". Run that button to clear out old data and start new. Do not modify any queries or you will break something. They are all used somewhere.

 

[stefanocps]

great seems fine now


After a SALES in the report page i don't find the sale..i have to close and reopen the db..so it will need a requery to be update
Also ..i create a structure, and then i cnage it..i see in the structure the date of th eorganization but if i click on it nothing appear
and if i do more chnage to organization in one day i ihave only one day shown in th eorganization list(which remain empty anyway)

 

[stefanocps]

i am having problem with the inactive user...once i set the inactive date for user..commission are still calculated for him and not anymore to main agemcy (Capital1 now)
Is th e INATTIVO user needed for this?

 

[MajP]

Appears to work for me.
1. Create a new Organization. That organization will get today's date.
2. Set the Inactive date prior to today's date. Example 1 Jun
3. Create a sale

If the Inactive date is less than the processed date it should work. So the inactive need to be less than today.

 

[stefanocps]

look here, pierluigino is inactive from 12/6 but if i make a sales..any sales after 12 from his affilate Genoveffo he still get commission

Also i have notice that even if Pierluigino is inacive i can choose him for a sale..while is better to do not have this chance

and see i cannot see the STRUCTURE detail ..if i click on 12/6 is empty

 

[stefanocps]

or look thisd that is even simplier with less agent
i have tried with the previuous version and it work..so i don't know what happen with some modification that has been done