Commissions Query

[menkenk]

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.

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]
FROM [Agent Commission Rate] INNER JOIN [Client Policy Information] ON [Agent Commission Rate].AgentID = [Client Policy Information].Agent
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.

[DonRayner]

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

([Quarterly]-22.53)*[Commission Rate] AS [Quarterly Com]

would become

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.

[menkenk]

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.

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)
FROM [Agent Commission Rate] INNER JOIN [Client Policy Information] ON [Agent Commission Rate].AgentID = [Client Policy Information].Agent
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.

[NeoPa]

You put the closing parenthesis in the wrong place. It should be :

..., IIf([Client Policy Information].Plan = 'UL Protector',[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate]) AS MonthlyCom, ...

[DonRayner]

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

IIF(IIF([Client Policy Information].Plan = "UL Protector",[Payment Mode]*[Commission Rate],([Monthly]-7.65)*[Commission Rate] AS MonthlyCom)

and it should be

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"

[menkenk]

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.

[NeoPa]

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.

[menkenk]

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.

[NeoPa]

You're welcome. Glad it works for you :)

[DonRayner]

Glad we could help. Good luck with your project.