Calculate Sales Advisor Commission based on tier chart

[philipscodes]

Greetings Experts,



I have a Access DB where I store sales data from our CRM server.
I base this DB for all my Excel calculations/reports via MS Query.

Now I have the business requirement to calculate the monthly commission for each sales advisor.
It is based on a chart as per business rules and I have promptly placed these in an Access table as well.

Now I have the sales table, commission chart table and some other useful lookup tables.

Once I have a query that provides the correct commission calculation along with the entire sales field table, I can transfer this over to Excel for a 'subtotal' and my work is done.

How do I calculate the commission based on this chart in Access Query?

I have attached all the relevant tables and lookup tables for this exercise.
Kindly advise how to go about this to obtain the desired results.

Warm Regards,
Philip

[ranman256]

you would make a Sum query,Q1, to get the agent totals for the period
then make Q2, that uses Q1 and the commission chart (not joined together) but rather use criteria,

Q1.AgentTotal => tCommision.LowLimit and Q1.AgentTotal <= tCommision.HighLimit

this will return the CompAmt for that Agent

[philipscodes]

Thanks Ranman.

I have implemented your advise and have attached the result.

We base the commission on units sold(we sell cars) not the invoice amount - so I have used a Count query instead.
The commission also differs based on the location where the unit is sold, as is evident from my first attachment (commission chart).

So, the result is not quiet what I expected.
Plus, I would want all the fields in the SalesTable as well for the final result.

Would you be kind to offer a tailored alternative please.

Note: I have also attached the Count query alone as well, which give the correct result.

Warm Regards,
Philip

[ranman256]

your compensation table must now be a query...since EACH agent has their own comp scale.
If they all used 1 comp scale then my example works.

Using my example , the comp query willl be joined to the Agents sale results (via ID or name), but the criteria will be the same.
join qsComps and qsAgentSales on AgentID
keep the BETWEEN low and high criteria.

[philipscodes]

Hi Ranman,

Yes your solution worked but with a join as shown in the attachment.

Thank you so much.
Have up-voted and marked the thread as solved.

Warm Regards,
Philip