Calculating YTD Commissions

[kishanm]

I have a table with Salesman' data, incld their assigned commission rate and effective data for that rate. These commission rates change randomly as the salesman move from job to job. when they do change the commission rate changes along with the effective date. I want to find out by how to get there prorated ytd commission rate if I give a random date. The calculation has to take into account based on the date I give how long has this salesman been within each commison rate (days) based on the given date period.

I.e. Salesman "A"
Jan comm rate = 2000 Effective date = 01/01/08
Feb Comm Rate = 2000, effective Date = 01/01/08
Mar Comm Rate = 2000, effective Date = 01/01/08
April Comm Rate = 2500, effective Date = 04/16/08
May Comm Rate = 2500, effective Date = 04/16/08
June Comm Rate = 2500, effective Date = 04/16/08
July Comm Rate = 3500, effective Date = 07/26/08
Aug Comm Rate = 3500, effective Date = 07/26/08

If I give a date of 06/30/08 the program sould calcuate the commission rate in the following manner;
01/01/08-04/16/08 = 2000+ 2000+2000+((200 0/30)* 15)+((2500/15)*15)+2500+25 00= 13250

For the life of me I cannot seem to figure out how to do this in a query using "iif" "then" or with my limited skills of VBA, please help. Thanks.

[puppydogbuddy]

In my opinion, the best way to do what you want is to write one or more UDF's (user defined functions) to do the computation, then reference the function in the criteria row of your query....like this:

Select * From yourTable Where Commission = fYTDCommission( )

Check this link out and see if it helps with how to do the computations..
Access Tutorial_Sales_ Statistics

http://www.profsr.com/access/accless13.htm

[NeoPa]

Perhaps if you explained the problem in definitive terms rather than solely providing an example it might make more sense.

What are the fields in the table?
What is the actual logic required (with reference to those fields)?

I couldn't make sense of the data as there was no explanation as to why you might have a record with both March & 1/1/08 associated with it.