Commission rates based on rolling years

[ChuckW]

Hi,

I need to create a query that will calculate commissions for a sales rep.
There are transaction tables where I can calculate sales and also the start
date of a client which is a key field for determining the pay rate. We have
a sales rep that started on 1/1/07 and inherited accounts. All inherited
accounts (those with start dates before 1/1/07) are called Existing Clients
and are paid at 8 %. She has been paid a commission rate of 14 % for all new
clients that she has signed up for this year (those with start dates between
1/1/07 and 12/31/07). I have been able to calculate this in a query.
However, starting the first of the year, there will be two categories of new
clients. New clients for 2007 will soon be paid at a commission rate of 10 %
while new clients for 2008 will be paid a rate of 14 percent. Starting
1/1/09, new clients for 2009 will be paid at a rate of 14 percent, new
clients for 2008 will be paid at a rate of 10 percent and new clients for
2007 will be paid at 8 percent. I can hard code the dates but I would like
to set this up so that when the new years (2008 and 2009) come around, it
will automatically calculate this. Can someone help?

Thanks,