Solved

help writing a query for multiple relationships within the same table

Posted on 2012-04-02
26
284 Views
Last Modified: 2012-04-09
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]*100 AS Expr1, AGCOMCREV.ContractEffectiveDate
FROM AGCOMCREV
WHERE (((AGCOMCREV.PolicyType)="0") AND ((AGCOMCREV.PolicyAgeLimit)=70));

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)
0
Comment
Question by:kbdaemon
  • 12
  • 9
  • 4
26 Comments
 
LVL 39

Expert Comment

by:als315
ID: 37799545
Can you upload sample DB with dummy (or real, if possible) data and expected result?
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37800572
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?
0
 

Author Comment

by:kbdaemon
ID: 37801489
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
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37801619
Could you convert it into .mdb? I don't have Access 2007 (though maybe other experts do).
0
 

Author Comment

by:kbdaemon
ID: 37801711
which mdb version?  it differentiates between Access 2000 and Access 2002-2003
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37801850
Better 2002-3.
0
 
LVL 39

Expert Comment

by:als315
ID: 37802516
@Vadim: converted version for you is inserted
@kbdaemon:
Can you show expected result also?
Database22-for-EE.zip
0
 
LVL 40

Expert Comment

by:Vadim Rapp
ID: 37802609
@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).
0
 

Author Comment

by:kbdaemon
ID: 37802742
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
0
 

Author Comment

by:kbdaemon
ID: 37802767
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").  If the contract dates match for the agents in the above scenario, then they are indeed tied together.  So, the above example could be invalidated if we do not account for matching contract effective dates.

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

Author Comment

by:kbdaemon
ID: 37802787
@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).
0
 
LVL 39

Expert Comment

by:als315
ID: 37807346
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?
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:kbdaemon
ID: 37807513
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).
0
 
LVL 39

Expert Comment

by:als315
ID: 37809498
You have two records for each date:
A73PWe can select minimal date (is it correct?), but how we can select one of two remaining records?
0
 

Author Comment

by:kbdaemon
ID: 37810813
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.
0
 
LVL 39

Expert Comment

by:als315
ID: 37811778
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?)?
0
 

Author Comment

by:kbdaemon
ID: 37811936
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.
0
 
LVL 39

Expert Comment

by:als315
ID: 37812650
Ok, very good. Can we take ID field as unique Contract (is it sale in your question?) number?
0
 

Author Comment

by:kbdaemon
ID: 37813234
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.
0
 
LVL 39

Expert Comment

by:als315
ID: 37815339
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?
0
 

Author Comment

by:kbdaemon
ID: 37816433
Yes!  I would like that info by contract date.
0
 
LVL 39

Expert Comment

by:als315
ID: 37818353
Test this DB (Qry - resulting query for all contracts, QryContractID36050 - comissions for contract ID 36050)
Database22-for-EE.zip
0
 

Author Comment

by:kbdaemon
ID: 37823366
I "sort of" understand your result and am wondering how I would get the contract date to appear in the datasheet.  Adding " AGCOMCREV.ContractEffectiveDate AS ContractDate" to the SELECT statements works for L1-L4 but not for the final union query.
0
 
LVL 39

Accepted Solution

by:
als315 earned 500 total points
ID: 37823512
It is shown in QryContractID36050 - remove criteria and you will have all contracts.
0
 

Author Closing Comment

by:kbdaemon
ID: 37825232
Thanks to als315 for helping me understand how to attack my problem and for the queries getting me where I needed to be.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now