Calculate Commission on varialbe rate? 1

[schlorhm]

I am creating a database using Access 2003. I need to calculate a commission on varying percentages. Here is the scenario- When a salesperson sells the first item his commission is 5%, the second 6%, third 7% and fourth or more 8%. I need to count the number of sales for each salesperson then calculate by the appropriate Rate. Below is an example of how I want the data to look. Any suggestions would be helpful.

Table Sales

RecCount Salesman# Rate InvAmt. Com:=[Rate]*[InvAmt]
1 011 .05 $100 $5.00
2 011 .06 $100 $6.00
3 011 .07 $100 $7.00
4 011 .08 $100 $8.00
5 011 .08 $100 $8.00

 

[Golom]

That's the result of the process but what what do you have to begin with?

You will need a way to determine which item was sold First, Second, Third, etc. Usually that is handled with some sort of DateTime field but we don't know if you have that.

You are using the terms "Item" and "Sale" somewhat interchangably. Does a "Sale" invariably involve selling only one item? Conversly, can there be more than one "item" in a sale? If so, is there some way to determine which one should be regarded as the First one?

 

[schlorhm]

Golom thanks for the reply.

The amount is calculated by the Invoice amount (InvAmt.). Yes there is a date field that I can use as well as the Invoice Number. The Item doesn't matter because all items will be included in the InvAmt.

Thanks in Advance!

 

[schlorhm]

Reading over my post after Golom's reply I realized that I should give more background on this problem. I am trying to explain the problem as simply as possible.

I use ODBC to pull the data from a Unix legacy business system. Once I have the tables in Access I am able to query the data to get the salesman’s records (Invoices sold by salesperson) and am able to sum the totals and calculate using a single rate or percentage with no problems.

However, the powers that be want to calculate the total commissions based on the scenario described above. The actual field names are as follows.

Invoice InvDate Slsm EXTPRICE SCode Com.

The Invoice, InvDate, Slsm and EXTPRICE fields are all pulled from multiple tables from the Unix system through ODBC in a query named “qry A Sales”.

I also have a table named Salesperson that contains the fields Slsm and SCode. Slmn being the Salesmans ID and SCode being the percentage to calculate the commission. The commission is then calculated by multiplying the EXTPRICE by the SCode to get the commission.

Like I said this works fine as long as there is only a single percentage to use. I need to figure out how to get that percentage to graduate. Also not all salespeople’s commissions will be calculated using the same percentages.

Hey I didn’t come up with the plan just trying to solve the lunacy!

Thanks Again.

 

[Golom]

Untested ... but I think it's something like

[tt]tblInvoice
Invoice InvDate Slsm EXTPRICE


tblSalesMen
Slsm [# of Sales] SCode
[/tt]

query Q1 - Rank the Invoices by Date

Select Slsm, InvDate, ExtPrice,
       (Select Count(*) From tblInvoice As I2
        Where I2.Slsm = I1.Slsm
          And I2.InvDate <= I1.InvDate) As [SaleNumber]

From tblInvoice As I1

Select Invoice, InvDate, Slsm, ExtPrice, 

       (Select MAX(SCode) From tblSalesMen S
        Where S.Slsm = Q1.Slsm
          And S.[# of Sales] <= Q1.SaleNumber) As SCode,

       (ExtPrice * (Select MAX(SCode) From tblSalesMen S
                    Where S.Slsm = Q1.Slsm
                      And S.[# of Sales] <= Q1.SaleNumber) ) As Commission

From Q1