Updating Records based on today's date

[Guest]

I have created a commission database and the commission percentage needs to
change based on one year from the policy effective date. Is there any way of
having access do this for me automatically without performing a query? Any
help would be greatly appreciated. Thanks.

 

[Guest]

You will need to use a query or code but there is no need to rebuild the
query each time if that is what you were thinking. Just base the query
criteria on the calendar that is built in. You did not state any particulars
about your database such as field names or percent change (fixed or
variable). If it is variable then what is the formula for the varriation -
Starting a 5.1% and decrease by 0.2% per year for first five years and then
by 0.1 per year.

 

[Guest]

Thanks for the reply.

I guess I should give a little more information. In my database I have a
CLIENT TABLE and a client is assigned to an agent. I have the agents in
their own table and tie the two tables together with the AgentID field. Also
I capture the agent's commission percentage for a particular client on the
CLIENT TABLE since it is a unique field to the client.

So lets say in November 2005 Client A signs up with Agent X and the
commission percentage that Agent X recieves for the first 12 months is 40% (I
enter this on the client table). On November 2006, the agent commission
percentage then goes to 20% and will stay there for the life of the client.

I am currently updating the commission percentage, on the client table, by
using a query that I use over and over, but it would be even better if the
percentage would update on its own after one year.

Is that possible?

 

[Guest]

Ok, then why not in the Client table put InitialPCT, InitInterval, and
FollowOnPCT.

Then I assume you use a summation query to calculat the monthly commision
for the agent. In the query then calculate it if withing the initial period
at the initial rate or if later at the follow on percent.

 

[Guest]

Karl,

Thank you I never even thought about that. I will do that.

Thanks again