help writing a query for multiple relationships within the same table
I have a table, built from a flat file, that contains commission relationships for agents (AGCOMCREV). I have figured out how to get the "contract type" for each agent:
SELECT AGCOMCREV.AgentCode, AGCOMCREV.[1stYrCommAgt1]*
FROM AGCOMCREV
WHERE (((AGCOMCREV.PolicyType)="
That gets me 154 records. I used PolicyType="0" because the commission paid on that policy identifies the contract type (it is always pays the largest commission to the agent - 100 for 100%, or 90 for 90%, etc.). That query only gets me part of the way, though.
The difficult thing for me is this: there may be multiple agents earning commissions (a commission hierarchy) whenever Agt1 makes a sale. There can be up to four additional agents (Agt2, Agt3, Agt4 and Agt5). AgentCode and Agt1 are different columns but contain the same value (that's just the way the data came over). The commission fields iterate for the additional agents (1stYrCommAgt2, 1stYrCommAgt3, etc.).
How do I write the query to show the presence or lack of additional agents and the commissions each agent earns in each hierarchy? (It is always subtractive, by the way: an agent with a 105 contract over an agent with a 90 contract would be making a 15% override commission - but that is just an FYI)
SELECT AGCOMCREV.AgentCode, AGCOMCREV.[1stYrCommAgt1]*
FROM AGCOMCREV
WHERE (((AGCOMCREV.PolicyType)="
That gets me 154 records. I used PolicyType="0" because the commission paid on that policy identifies the contract type (it is always pays the largest commission to the agent - 100 for 100%, or 90 for 90%, etc.). That query only gets me part of the way, though.
The difficult thing for me is this: there may be multiple agents earning commissions (a commission hierarchy) whenever Agt1 makes a sale. There can be up to four additional agents (Agt2, Agt3, Agt4 and Agt5). AgentCode and Agt1 are different columns but contain the same value (that's just the way the data came over). The commission fields iterate for the additional agents (1stYrCommAgt2, 1stYrCommAgt3, etc.).
How do I write the query to show the presence or lack of additional agents and the commissions each agent earns in each hierarchy? (It is always subtractive, by the way: an agent with a 105 contract over an agent with a 90 contract would be making a 15% override commission - but that is just an FYI)
als315
Can you upload sample DB with dummy (or real, if possible) data and expected result?
I second the request from als315 - either please upload a sample, or give detailed explanations about all the fields and their relationships. We have no idea about this business, so can only guess about things like subtractive commissions, 105 contract, policytype, etc. If you want to show additional agents, then what is the field that connects one agent to another?
ASKER
Per your request. Please ignore my comments about how the commissions are subtractive. That is immaterial to your understanding of the agent commission relationships. The agent columns are as mentioned above in my initial question. Thank you!
And remember, this data is coming straight from a flat file, so there is no "database design" or schema to reference. I first converted the data from text to Excel and then uploaded the Excel file to access.
Database22-for-EE.accdb
And remember, this data is coming straight from a flat file, so there is no "database design" or schema to reference. I first converted the data from text to Excel and then uploaded the Excel file to access.
Database22-for-EE.accdb
Could you convert it into .mdb? I don't have Access 2007 (though maybe other experts do).
ASKER
which mdb version? it differentiates between Access 2000 and Access 2002-2003
Better 2002-3.
@Vadim: converted version for you is inserted
@kbdaemon:
Can you show expected result also?
Database22-for-EE.zip
@kbdaemon:
Can you show expected result also?
Database22-for-EE.zip
@als315 - thanks
@kbdaemon: - could you give an example of what you want to see, using 1-2 rows in your table? what's still unclear is: what are the values in column Agt1 and such - are they the agents that you want the report for, i.e. you want to know the commission earned by A24Q, by A24R and so forth? Then, where are the commissions earned - are they the values from 1stYearCommAgt1 and so on? (we can guess, but it's better not to guess but to hear it from you exactly).
@kbdaemon: - could you give an example of what you want to see, using 1-2 rows in your table? what's still unclear is: what are the values in column Agt1 and such - are they the agents that you want the report for, i.e. you want to know the commission earned by A24Q, by A24R and so forth? Then, where are the commissions earned - are they the values from 1stYearCommAgt1 and so on? (we can guess, but it's better not to guess but to hear it from you exactly).
ASKER
Good questions. The AgentCode column contains the same value as column Agt1. I can determine the contract type by looking at the first year commission earned by each agent in a policy type "0" transaction, so I would like to see something like:
Agt1 1stYearCommAgt1 Agt2 1stYearCommAgt2 ... etc.
A73P 0.5500 A71M 0.3000 A70Y 0.1000 A70X 0.100
note that if A71M is the primary agent (Agt1) the commissions change:
A71M 0.8500 A70Y 0.1000 A70X 0.1000
and then if you look at the record for A70Y as primary agent:
A70Y 0.9500 A70X 0.1000
and finally with A70X as the primary agent:
A70X 1.0500
Agt1 1stYearCommAgt1 Agt2 1stYearCommAgt2 ... etc.
A73P 0.5500 A71M 0.3000 A70Y 0.1000 A70X 0.100
note that if A71M is the primary agent (Agt1) the commissions change:
A71M 0.8500 A70Y 0.1000 A70X 0.1000
and then if you look at the record for A70Y as primary agent:
A70Y 0.9500 A70X 0.1000
and finally with A70X as the primary agent:
A70X 1.0500
ASKER
Remember, I am basing my query on policy type "0" (zero), but there could be more than one situation based upon the contract date ("ContractEffectiveDate").
I would like to return a result that states the date, the agents tied to that particular hierarchy and the commissions they each earn from the sale of policy type "0".
I would like to return a result that states the date, the agents tied to that particular hierarchy and the commissions they each earn from the sale of policy type "0".
ASKER
@als315 I forgot to say thanks as well for converting the accdb to mdb. Thank you!
And notice the answer is right there in my example above. The contract types for everyone in the hierarchy (assuming their contract dates match) are:
A70X 1.0500
A70Y 0.9500
A71M 0.8500
A73P 0.5500
I would then be able to say that agents A70X, A70Y, A71M and A73P are in a commission hierarchy where their top-level commission for policy type 0 is 105%, 95%, 85% and 55%, respectively and that this contract was in force from "x" date to "x" date. The date data I could fill this in using newer contract effective dates or termination dates from the other table called agents which has all of their personal and contracting info (not disclosed in this example).
And notice the answer is right there in my example above. The contract types for everyone in the hierarchy (assuming their contract dates match) are:
A70X 1.0500
A70Y 0.9500
A71M 0.8500
A73P 0.5500
I would then be able to say that agents A70X, A70Y, A71M and A73P are in a commission hierarchy where their top-level commission for policy type 0 is 105%, 95%, 85% and 55%, respectively and that this contract was in force from "x" date to "x" date. The date data I could fill this in using newer contract effective dates or termination dates from the other table called agents which has all of their personal and contracting info (not disclosed in this example).
I can't find any logic on your records, sorry. May be we can move step by step. For example, let take Agent A73P from your first example. If, as you described, we take A73 and Policy type 0, we will have 4 very similar record. Why you've decided to take 1stYearComm = 0.55, when there is also value 0.65?
ASKER
the dates are different - I just chose one and looked at the relationships as an example. An agent could sign contracts that are BOTH "active" in the sense that he/she might work in one city under one hierarchy and then in another location for somebody else. The contract date is the key (ContractEffectiveDate).
You have two records for each date:
We can select minimal date (is it correct?), but how we can select one of two remaining records?
We can select minimal date (is it correct?), but how we can select one of two remaining records?
ASKER
I understand your point now. In my original select statement I used the age condition to single out one part of the overall contract. The commissions vary by age, so commissions are paid according to the PolicyAgeLimit field. The limit to age 70 represents the highest-paying commission range in the entire contract for EVERY agent that sells policy type 0.
If you add "AND PolicyAgeLimit=0" to your query it should reduce to just one record for A73P for that effective date.
If you add "AND PolicyAgeLimit=0" to your query it should reduce to just one record for A73P for that effective date.
PolicyAgeLimit values for A73P are: 70 and 85. If I set it to 0 I don't get anything. We could select minimum or maximum (70 - minimum). I see 3 possible values for this field (70 - many, 85 - many, 95 - only one). Can I select minimum?
What mean Age values? Days (from?)?
What mean Age values? Days (from?)?
ASKER
I am using your file "A73P.png" in my discussion.
We are dealing with life insurance policies, and as such the commission earned by the Agent is based upon two items: 1) Policy Type; and 2) Age of person purchasing the Policy. The commissions were set in these records by using max age, so A73P earned 55% commission each time he sold policy type 0 to a person 70-years old or less beginning July 1, 2004. On November 1, 2004 A73P received a new contract that paid him 65% commission for that same type of transaction.
We are dealing with life insurance policies, and as such the commission earned by the Agent is based upon two items: 1) Policy Type; and 2) Age of person purchasing the Policy. The commissions were set in these records by using max age, so A73P earned 55% commission each time he sold policy type 0 to a person 70-years old or less beginning July 1, 2004. On November 1, 2004 A73P received a new contract that paid him 65% commission for that same type of transaction.
Ok, very good. Can we take ID field as unique Contract (is it sale in your question?) number?
ASKER
Sorry for the delay. ID is unique - it is an Access-created primary key. The combination of AgentCode, PolicyType=0, PolicyAgeLimit=70 and ContractEffectiveDate should also be unique.
OK, we can now move forward. What you like to get? These percents:
A70X 1.0500
A70Y 0.9500
A71M 0.8500
A73P 0.5500
for each contract?
A70X 1.0500
A70Y 0.9500
A71M 0.8500
A73P 0.5500
for each contract?
ASKER
Yes! I would like that info by contract date.
Test this DB (Qry - resulting query for all contracts, QryContractID36050 - comissions for contract ID 36050)
Database22-for-EE.zip
Database22-for-EE.zip
ASKER
I "sort of" understand your result and am wondering how I would get the contract date to appear in the datasheet. Adding " AGCOMCREV.ContractEffectiv
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks to als315 for helping me understand how to attack my problem and for the queries getting me where I needed to be.