How to write expression in query to calculate commission with 2 conditions.

[moi]

Dear all,

Please help me to write an expression for a commision..

Scenario are..

Agent code=1 (8%)
Agent code=2 (10)%)
TCP = 550,000.00

I need to test if the agent code is "1" (550,000.00*. 8%) or "2" (550,000.00*10%)..

Please i need help very badly.

Thank you.

 

[tvanstiphout]

What is TCP in your case?
What are the tables and fields involved? Show us a screenshot of your Relationships diagram (with the relevant tables nicely laid out).

 

[plog]

You've only covered 2 cases, what about the rest of infinity? What would you like the result to be when Agent code is neither 1 nor 2?

 

[moi]

What is TCP in your case?
What are the tables and fields involved? Show us a screenshot of your Relationships diagram (with the relevant tables nicely laid out).

TCP is total contract price.. Table involve is tblBuyer and tblAgent.. Link field is agtID (FKey to tblBuyer).

 

[moi]

You've only covered 2 cases, what about the rest of infinity? What would you like the result to be when Agent code is neither 1 nor 2?

Agent code will only be 1 or 2.. per contract. (1=freelance agent, 2=brokerhouse agent)..

 

[plog]

Commission: [TCP] * iif(AgentCode = 1, .08, .1)

 

[moi]

Commission: [TCP] * iif(AgentCode = 1, .08, .1)

Many thanks sir.. Solved.

 

[CJ_London]

Presume the commission rates will never change?

 

[moi]

Presume the commission rates will never change?

Yes.. it is always 8% & 10%..

 

[MajP]

If it is ever going to change or you are ever going to have additional codes then build a helper table and simply join on that by agent code
tblCommisions
--AgentCode
--Commission

AgentCode	Commission
1	.08
2	.1
3	.15

 

[Pat Hartman]

I would think about this in a more generic way. In all of the commission applications I have built there was the concept of a Commission Agreement which defined the rules. Whenever possible, the discrete values are stored in a table rather than hard coded. Your commission agreements sound very simple. Somehow, mine never were. Sometimes the agreements just had different rates or different breakpoints 8% for first 5, 10% for remaining sales. Or 10% for a new customer and 8% for a renewal.

Right now, you have two plans. Plan A and Plan B. Plan A has a rate of 8% and Plan B has a rate of 10%. You would assign one of the plans to each Agent.

Here's one example for a Gas/Electricity reseller.

 

[moi]

I would think about this in a more generic way. In all of the commission applications I have built there was the concept of a Commission Agreement which defined the rules. Whenever possible, the discrete values are stored in a table rather than hard coded. Your commission agreements sound very simple. Somehow, mine never were. Sometimes the agreements just had different rates or different breakpoints 8% for first 5, 10% for remaining sales. Or 10% for a new customer and 8% for a renewal.

Right now, you have two plans. Plan A and Plan B. Plan A has a rate of 8% and Plan B has a rate of 10%. You would assign one of the plans to each Agent.

Here's one example for a Gas/Electricity reseller.
View attachment 114506

Hi pat,
Yes my commission is simple and straight forward.. 8% for freelance agents, 10% for broker house agents.

 

[Pat Hartman]

I would still use the concept of a Commission agreement and create an Agent Agreement and a House Agreement and store the values in a table. That gives you the ultimate flexibility with your currently simple rules.

 

[moi]

I would still use the concept of a Commission agreement and create an Agent Agreement and a House Agreement and store the values in a table. That gives you the ultimate flexibility with your currently simple rules.

Thanks pat, i will try that.

 

[Pat Hartman]

You're welcome. If they ever add a third tier, you will be very happy to have an easy way to handle it.

 

[moi]

You're welcome. If they ever add a third tier, you will be very happy to have an easy way to handle it.

Indeed, i created a table as u suggested and it also solved the other post i posted regarding the totals.

Thank you pat..

 

[Pat Hartman]

You're welcome