How to address many-to-many relationship with different levels of relationships

[LynneSochinski]

Hi, I'm self taught and am trying to create a database in Access 2003 for our small Real Estate firm that allows us to track our Agents and their transactions. Additionally, we've set up a system where if an Agent refers another Agent to our firm they receive a portion of the fees we receive from them. They then also receive a smaller portion of the fees paid by agents referred by the agent they referred up to 4 levels. With differing percentages at each level.

I've created an agent table and a sponsor table both of which contain much of the same basic information and an append query to transfer that information. My question is as the Agents can have up to 4 types of relationships with many sponsors at any one time, I need to track the different level of sponsors in order to have Access calculate the fees we owe them. Do I have to make several different sponsor tables and then do many-to-many links or is there some other way to do this?

For example- Agent A, sponsors Agent B and gets 20% of B's fees. Agent B refers Agent C (Agent A gets 15% and Agent B get 20%). Agent C refers Agent D (Agent A gets 10%, Agent B gets 15% and Agent C gets 20%). Agent D refers Agent E (Agent A gets 5%, Agent B gets 10%, Agent C gets 15% and Agent D gets 20%).

Any help would be greatly appreciated as I'm tearing my hair out at this point!

Thanks,

Lynne

[ADezii]

Is the assumption correct that an Agent can only be referred by another, single (1), Agent?

[Lynne Sochinski]

Yes. An agent could only be related to one sponsor (other agent) at one level (20%, 15%, 10% or 5%).

Thanks,
Lynne

[Lynne Sochinski]

Hi again. I wanted to clarify a little more. An agent could only be related to a specific other agent (sponsor) at a specific level (20%, 15%, 10% or 5%) but could be related to many different sponsors at those levels.

Thanks again.

[ADezii]

Hello Lynne. Download the Attachment, and let me know what you thing. The Logic is based on the premise that 1 Agent can refer many other Agents, but can only be referred by a single Agent. A single Table consisting of Sponsorship/Referral Chains for each Agent, plus an Algorithm based on the above premise is all that I used. Not really sure if I am on the right track with this one, but let me know in either event.

[Lynne Sochinski]

Thanks so much for your help. It gives me a direction that I think might work into the rest of the database. Will let you know.