By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,286 Members | 2,443 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,286 IT Pros & Developers. It's quick & easy.

Commissions Query

P: 10
Hello,

I have a query that I used to calculate the monthly, quarterly, semi-annual, annual commissions by agent. Below is the SQL view of this Query.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Client Policy Information].[Policy #], [Client Policy Information].Plan, [Client Policy Information].Placed, [Client Policy Information].Monthly, ([Monthly]-7.65)*[Commission Rate] AS [Monthly Com], [Client Policy Information].Quarterly, ([Quarterly]-22.53)*[Commission Rate] AS [Quarterly Com], [Client Policy Information].[Semi-Annual], ([Semi-Annual]-44.2)*[Commission Rate] AS [Semi-Annual Com], [Client Policy Information].Annual, ([Annual]-85)*[Commission Rate] AS [Annual Com], [Client Policy Information].CTP, ([CTP]*[Commission Rate]) AS [CTP Com]
  2. FROM [Agent Commission Rate] INNER JOIN [Client Policy Information] ON [Agent Commission Rate].AgentID = [Client Policy Information].Agent
  3. WHERE (((Format([Agent]))=[Enter Agent Name]) AND ((Format([Placed],"mm/yyyy")) Between [Start Date (mm/yyyy):] And [End Date (mm/yyyy):]));
The issue I have is that under the PLAN field, we have a UL Product which has a different commission calculation from the rest of the products ([Payment Mode]*[Commission Rate]). How would I go about making it so that if it sees a client purchased a UL product it will calculate the Monthly, Quarterly, Semi-Annual, and Annual commissions using the [Payment Mode]*[Commission Rate] calculations?

Thank you.
Apr 27 '09 #1
Share this Question
Share on Google+
9 Replies


DonRayner
Expert 100+
P: 489
Take a look at using the IIF() function in your query. The syntax for using the function is IIF(Criteria,Result True,Result False)

Example
Expand|Select|Wrap|Line Numbers
  1. ([Quarterly]-22.53)*[Commission Rate] AS [Quarterly Com]
would become
Expand|Select|Wrap|Line Numbers
  1. IIF([Client Policy Information].Plan = "YourULProduct",[Payment Mode]*[Commission Rate],([Quarterly]-22.53)*[Commission Rate]) AS [Quarterly Com]
Just a suggestion, but you might want to take a look at your naming convention. If your names were done as QuarterlyCom or Quarterly_Com instead of Quarterly Com then you wouldn't require the [] around the name every time it was used.
Apr 27 '09 #2

P: 10
Thanks DonRayner,
I did as you suggested and changed the names to QuarterlyCom, MonthlyCom, etc. to make it easier. I also deleted the [] surrounding them. Im getting a syntax error (missing operator) when I inputted the IIF function you typed down. The SQL view looks like this now.
Expand|Select|Wrap|Line Numbers
  1. SELECT [Client Policy Information].[Policy #], [Client Policy Information].Plan, [Client Policy Information].Agent, [Client Policy Information].Placed, IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate] AS MonthlyCom), IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Quarterly]-22.53)*[Commission Rate]) AS QuarterlyCom), IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Semi-Annual]-44.2)*[Commission Rate] AS Semi-AnnualCom), IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Annual]-85)*[Commission Rate] AS AnnualCom), [Client Policy Information].CTP, ([CTP]*[Commission Rate]) AS CTPCom)
  2. FROM [Agent Commission Rate] INNER JOIN [Client Policy Information] ON [Agent Commission Rate].AgentID = [Client Policy Information].Agent
  3. WHERE (((Format([Agent]))=[Enter Agent Name]) AND ((Format([Placed],"mm/yyyy")) Between [Start Date (mm/yyyy):] And [End Date (mm/yyyy):]));
Another thing I realised is that how would I add a second product that goes on the same UL Product commission calculations? Do I just add another "ProductName" next to the "UL Protector"? I'm very new to Access and have only been using it for 3 months now. Sorry if I'm not familiar with many of the functions.
Apr 27 '09 #3

NeoPa
Expert Mod 15k+
P: 31,494
You put the closing parenthesis in the wrong place. It should be :
Expand|Select|Wrap|Line Numbers
  1. ..., IIf([Client Policy Information].Plan = 'UL Protector',[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate]) AS MonthlyCom, ...
Apr 27 '09 #4

DonRayner
Expert 100+
P: 489
It looks like you have the closing brackets in the wrong spot on your IIF() functions. You should be closing the function before the AS.

You are using
Expand|Select|Wrap|Line Numbers
  1. IIF(IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate] AS MonthlyCom)
and it should be
Expand|Select|Wrap|Line Numbers
  1. IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate]) AS MonthlyCom
I was under the impression that you only had the one alternative plan, that is why I suggested the IIF as the easiest way to achieve what you were looking for. You can use an OR in your IIF() function but that would require you to edit your query each time you need to add a different "UL Protector"
Apr 27 '09 #5

P: 10
Thanks NeoPa & DonRayner,

I got that sorted out, I'm able to save the changes to SQL view. When I run the query, the MonthlyCom, QuarterlyCom, and etc. has #Error where it's supposed to have calculated the commission rate for the UL Protector product. When I open a report based on that Query it says "This expression is typed incorrectly, or it is too complex to be evaluated." Though it calculates the other products' commission rates perfectly fine.

Sorry for the confusion, after I had posted my question I was editing the combo box under Plan and realsed that there were 2 other products which use the same commission calculations as the UL Protector, SUL and UL Plus. I never thought calculating commission would be such a complicated task.
Apr 27 '09 #6

NeoPa
Expert Mod 15k+
P: 31,494
If it gets much more complicated than a simple either/or situation, you may be well advised to determine the results within your query using tables of the data, rather than increasingly complicated IIf() calls.

Logic maintained in tables is generally considered far more easy to maintain than similar logic contained within the code.
Apr 27 '09 #7

P: 10
Thanks for the help NeoSpa and DonRaynor,

I figured out my error with your functions. Instead of [Payment Mode] I filled in the corresponding Field I wanted it to calculate. for example [Monthly]*[Commission Rate] for the MonthlyCom, and it worked. My apologies for overcomplicating it. I really appreciate both of your advice.
Apr 27 '09 #8

NeoPa
Expert Mod 15k+
P: 31,494
You're welcome. Glad it works for you :)
Apr 27 '09 #9

DonRayner
Expert 100+
P: 489
Glad we could help. Good luck with your project.
Apr 27 '09 #10

Post your reply

Sign in to post your reply or Sign up for a free account.