Changing commission rate at a future date

[ChuckW]

Hi,

I have created a query from two tables that has the following fields: Name,
StartDate and Type. The Type field has four possible values: New2008,
New2007, Existing2006 and Pre2006. Through my query I am able to assign the
value in the type field based on the StartDate. The New2008 values will
start showing up for new clients who start date is on or after March 1st of
2008. The New2007 clients are those people whose start date is in the 3/1/07
to 2/28/08 range. Right now, the sales rep for these New2007 clients receive
a 14 percent commission rate. However, starting on 3/1/08, the sales rep for
these New2007 client will have a commission rate of 10 percent and the
New2008 clients will have a commission rate of 14 percent. Also on 3/1/08,
the Existing2006 will have a 10 percent commission rate which is the same as
it is now, however, the Pre2006 clients whill have a reduced commission rate
from 10 percent to 8 percent. I want to write a query for the future which
will analyze the current date, compare it to the start date and automatically
change the commission rates on 3/1/08. So without having to change my
percentages manually, it will start giving these New2007 clients a 10 percent
rate rather than the current 14 percent rate.

Can someone help?

thanks,

 

[Jeff Boyce]

Chuck

A couple observations...

It sounds like your [Type] can be calculated from knowing your [StartDate].
If so, [Type] is redundant and unnecessary, since you can do the calculation
in a query (remember, Access is NOT a spreadsheet!).

If your fieldname is actually "Name", be aware that Access treats this term
as a reserved word -- what you think you mean by it and what Access thinks
it means are two (possibly) different matters. Change the fieldname to
something more descriptive (and not a reserved word).

It sounds like your commission rate is based on date ranges. I would
probably create a table that holds [BeginDate], [EndDate], and
[CommissionRate] to keep track of these, then use the [StartDate] from your
other table to "look up" the appropriate commission.

"I have created a query from two tables ..." -- but you list three fields.
It all starts with the data. What's your (underlying) table structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

 

[ChuckW]

Jeff,

Thanks for your help. My Name field is actually CustomerName. I created a
table called StartDateER that has three fields: MinDate, MaxDate and Type.
Here are the values in this table.

MinDate MaxDate Type
1/1/2000 2/28/2006 Existing Pre 2006
3/1/2006 2/28/2007 Existing 2006
3/1/2007 2/28/2008 New 2007
3/1/2008 2/28/2009 New 2008

I have a query that contains date parameters based on a form. The query is
called ElisaSales3 and has StartDate, CustomerName and Total. There is a
form called frmCommissions that have two text boxes for a start and end
dates. Someone in accounting plugs in the start and end dates which is
bi-monthly. The ElisaSales2 query calculates the sales transactions for this
particular sales rep for this time period. The ElisaSales3 then summarizes
this.

I created a query calle ElisaSales4 with the following SQL:

SELECT ElisaSales3.FullName, ElisaSales3.StartDate, StartDatesER.Type
FROM ElisaSales3, StartDatesER
WHERE (((ElisaSales3.StartDate) Between [MinDate] And [MaxDate]));

What is does is to look at the StartDate field and assign it a type (ie
New2007). The problem is that the commission rate will change for all
customers in this category starting 3/1/08. I am attempting to write a query
that will look at the current date and determine the commision rate.

Any thoughts?

Thanks,

--
Chuck W

Chuck

A couple observations...

It sounds like your [Type] can be calculated from knowing your [StartDate].
If so, [Type] is redundant and unnecessary, since you can do the calculation
in a query (remember, Access is NOT a spreadsheet!).

If your fieldname is actually "Name", be aware that Access treats this term
as a reserved word -- what you think you mean by it and what Access thinks
it means are two (possibly) different matters. Change the fieldname to
something more descriptive (and not a reserved word).

It sounds like your commission rate is based on date ranges. I would
probably create a table that holds [BeginDate], [EndDate], and
[CommissionRate] to keep track of these, then use the [StartDate] from your
other table to "look up" the appropriate commission.

"I have created a query from two tables ..." -- but you list three fields.
It all starts with the data. What's your (underlying) table structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a query from two tables that has the following fields:
Name,
StartDate and Type. The Type field has four possible values: New2008,
New2007, Existing2006 and Pre2006. Through my query I am able to assign
the
value in the type field based on the StartDate. The New2008 values will
start showing up for new clients who start date is on or after March 1st
of
2008. The New2007 clients are those people whose start date is in the
3/1/07
to 2/28/08 range. Right now, the sales rep for these New2007 clients
receive
a 14 percent commission rate. However, starting on 3/1/08, the sales rep
for
these New2007 client will have a commission rate of 10 percent and the
New2008 clients will have a commission rate of 14 percent. Also on
3/1/08,
the Existing2006 will have a 10 percent commission rate which is the same
as
it is now, however, the Pre2006 clients whill have a reduced commission
rate
from 10 percent to 8 percent. I want to write a query for the future
which
will analyze the current date, compare it to the start date and
automatically
change the commission rates on 3/1/08. So without having to change my
percentages manually, it will start giving these New2007 clients a 10
percent
rate rather than the current 14 percent rate.

Can someone help?

thanks,

 

[Jeff Boyce]

Where will the new commission rate be stored? How will you reconstruct the
"old" commission rate?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your help. My Name field is actually CustomerName. I created
a
table called StartDateER that has three fields: MinDate, MaxDate and
Type.
Here are the values in this table.

MinDate MaxDate Type
1/1/2000 2/28/2006 Existing Pre 2006
3/1/2006 2/28/2007 Existing 2006
3/1/2007 2/28/2008 New 2007
3/1/2008 2/28/2009 New 2008

I have a query that contains date parameters based on a form. The query
is
called ElisaSales3 and has StartDate, CustomerName and Total. There is a
form called frmCommissions that have two text boxes for a start and end
dates. Someone in accounting plugs in the start and end dates which is
bi-monthly. The ElisaSales2 query calculates the sales transactions for
this
particular sales rep for this time period. The ElisaSales3 then
summarizes
this.

I created a query calle ElisaSales4 with the following SQL:

SELECT ElisaSales3.FullName, ElisaSales3.StartDate, StartDatesER.Type
FROM ElisaSales3, StartDatesER
WHERE (((ElisaSales3.StartDate) Between [MinDate] And [MaxDate]));

What is does is to look at the StartDate field and assign it a type (ie
New2007). The problem is that the commission rate will change for all
customers in this category starting 3/1/08. I am attempting to write a
query
that will look at the current date and determine the commision rate.

Any thoughts?

Thanks,

--
Chuck W

Chuck

A couple observations...

It sounds like your [Type] can be calculated from knowing your
[StartDate].
If so, [Type] is redundant and unnecessary, since you can do the
calculation
in a query (remember, Access is NOT a spreadsheet!).

If your fieldname is actually "Name", be aware that Access treats this
term
as a reserved word -- what you think you mean by it and what Access
thinks
it means are two (possibly) different matters. Change the fieldname to
something more descriptive (and not a reserved word).

It sounds like your commission rate is based on date ranges. I would
probably create a table that holds [BeginDate], [EndDate], and
[CommissionRate] to keep track of these, then use the [StartDate] from
your
other table to "look up" the appropriate commission.

"I have created a query from two tables ..." -- but you list three
fields.
It all starts with the data. What's your (underlying) table structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a query from two tables that has the following fields:
Name,
StartDate and Type. The Type field has four possible values: New2008,
New2007, Existing2006 and Pre2006. Through my query I am able to
assign
the
value in the type field based on the StartDate. The New2008 values
will
start showing up for new clients who start date is on or after March
1st
of
2008. The New2007 clients are those people whose start date is in the
3/1/07
to 2/28/08 range. Right now, the sales rep for these New2007 clients
receive
a 14 percent commission rate. However, starting on 3/1/08, the sales
rep
for
these New2007 client will have a commission rate of 10 percent and the
New2008 clients will have a commission rate of 14 percent. Also on
3/1/08,
the Existing2006 will have a 10 percent commission rate which is the
same
as
it is now, however, the Pre2006 clients whill have a reduced commission
rate
from 10 percent to 8 percent. I want to write a query for the future
which
will analyze the current date, compare it to the start date and
automatically
change the commission rates on 3/1/08. So without having to change my
percentages manually, it will start giving these New2007 clients a 10
percent
rate rather than the current 14 percent rate.

Can someone help?

thanks,

 

[ChuckW]

Jeff,

I won't need to reconstruct the old commission rate. It is run on the 15th
and 30th or 31st of every month. We never go back and rerun it. I could
have stored the commission rates in my StartDateER table but since they
change, I can't do this. Perhaps this query is to complex and I should just
put my commission rates in this StartDateER table and then make a note to
change them when March 08 rolls around.

Chuck
--
Chuck W

Where will the new commission rate be stored? How will you reconstruct the
"old" commission rate?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your help. My Name field is actually CustomerName. I created
a
table called StartDateER that has three fields: MinDate, MaxDate and
Type.
Here are the values in this table.

MinDate MaxDate Type
1/1/2000 2/28/2006 Existing Pre 2006
3/1/2006 2/28/2007 Existing 2006
3/1/2007 2/28/2008 New 2007
3/1/2008 2/28/2009 New 2008

I have a query that contains date parameters based on a form. The query
is
called ElisaSales3 and has StartDate, CustomerName and Total. There is a
form called frmCommissions that have two text boxes for a start and end
dates. Someone in accounting plugs in the start and end dates which is
bi-monthly. The ElisaSales2 query calculates the sales transactions for
this
particular sales rep for this time period. The ElisaSales3 then
summarizes
this.

I created a query calle ElisaSales4 with the following SQL:

SELECT ElisaSales3.FullName, ElisaSales3.StartDate, StartDatesER.Type
FROM ElisaSales3, StartDatesER
WHERE (((ElisaSales3.StartDate) Between [MinDate] And [MaxDate]));

What is does is to look at the StartDate field and assign it a type (ie
New2007). The problem is that the commission rate will change for all
customers in this category starting 3/1/08. I am attempting to write a
query
that will look at the current date and determine the commision rate.

Any thoughts?

Thanks,

--
Chuck W

Chuck

A couple observations...

It sounds like your [Type] can be calculated from knowing your
[StartDate].
If so, [Type] is redundant and unnecessary, since you can do the
calculation
in a query (remember, Access is NOT a spreadsheet!).

If your fieldname is actually "Name", be aware that Access treats this
term
as a reserved word -- what you think you mean by it and what Access
thinks
it means are two (possibly) different matters. Change the fieldname to
something more descriptive (and not a reserved word).

It sounds like your commission rate is based on date ranges. I would
probably create a table that holds [BeginDate], [EndDate], and
[CommissionRate] to keep track of these, then use the [StartDate] from
your
other table to "look up" the appropriate commission.

"I have created a query from two tables ..." -- but you list three
fields.
It all starts with the data. What's your (underlying) table structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a query from two tables that has the following fields:
Name,
StartDate and Type. The Type field has four possible values: New2008,
New2007, Existing2006 and Pre2006. Through my query I am able to
assign
the
value in the type field based on the StartDate. The New2008 values
will
start showing up for new clients who start date is on or after March
1st
of
2008. The New2007 clients are those people whose start date is in the
3/1/07
to 2/28/08 range. Right now, the sales rep for these New2007 clients
receive
a 14 percent commission rate. However, starting on 3/1/08, the sales
rep
for
these New2007 client will have a commission rate of 10 percent and the
New2008 clients will have a commission rate of 14 percent. Also on
3/1/08,
the Existing2006 will have a 10 percent commission rate which is the
same
as
it is now, however, the Pre2006 clients whill have a reduced commission
rate
from 10 percent to 8 percent. I want to write a query for the future
which
will analyze the current date, compare it to the start date and
automatically
change the commission rates on 3/1/08. So without having to change my
percentages manually, it will start giving these New2007 clients a 10
percent
rate rather than the current 14 percent rate.

Can someone help?

thanks,

 

[Jeff Boyce]

Since you have no need for historical data, I'm with you, keep it simple!

Another idea might be to use a parameterized query and (be forced to) enter
the commission rate each time the query is run.

Good luck!

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

I won't need to reconstruct the old commission rate. It is run on the
15th
and 30th or 31st of every month. We never go back and rerun it. I could
have stored the commission rates in my StartDateER table but since they
change, I can't do this. Perhaps this query is to complex and I should
just
put my commission rates in this StartDateER table and then make a note to
change them when March 08 rolls around.

Chuck
--
Chuck W

Where will the new commission rate be stored? How will you reconstruct
the
"old" commission rate?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Jeff,

Thanks for your help. My Name field is actually CustomerName. I
created
a
table called StartDateER that has three fields: MinDate, MaxDate and
Type.
Here are the values in this table.

MinDate MaxDate Type
1/1/2000 2/28/2006 Existing Pre 2006
3/1/2006 2/28/2007 Existing 2006
3/1/2007 2/28/2008 New 2007
3/1/2008 2/28/2009 New 2008

I have a query that contains date parameters based on a form. The
query
is
called ElisaSales3 and has StartDate, CustomerName and Total. There is
a
form called frmCommissions that have two text boxes for a start and end
dates. Someone in accounting plugs in the start and end dates which is
bi-monthly. The ElisaSales2 query calculates the sales transactions
for
this
particular sales rep for this time period. The ElisaSales3 then
summarizes
this.

I created a query calle ElisaSales4 with the following SQL:

SELECT ElisaSales3.FullName, ElisaSales3.StartDate, StartDatesER.Type
FROM ElisaSales3, StartDatesER
WHERE (((ElisaSales3.StartDate) Between [MinDate] And [MaxDate]));

What is does is to look at the StartDate field and assign it a type
(ie
New2007). The problem is that the commission rate will change for all
customers in this category starting 3/1/08. I am attempting to write a
query
that will look at the current date and determine the commision rate.

Any thoughts?

Thanks,

--
Chuck W


:

Chuck

A couple observations...

It sounds like your [Type] can be calculated from knowing your
[StartDate].
If so, [Type] is redundant and unnecessary, since you can do the
calculation
in a query (remember, Access is NOT a spreadsheet!).

If your fieldname is actually "Name", be aware that Access treats this
term
as a reserved word -- what you think you mean by it and what Access
thinks
it means are two (possibly) different matters. Change the fieldname
to
something more descriptive (and not a reserved word).

It sounds like your commission rate is based on date ranges. I would
probably create a table that holds [BeginDate], [EndDate], and
[CommissionRate] to keep track of these, then use the [StartDate] from
your
other table to "look up" the appropriate commission.

"I have created a query from two tables ..." -- but you list three
fields.
It all starts with the data. What's your (underlying) table
structure?

Regards

Jeff Boyce
Microsoft Office/Access MVP

Hi,

I have created a query from two tables that has the following
fields:
Name,
StartDate and Type. The Type field has four possible values:
New2008,
New2007, Existing2006 and Pre2006. Through my query I am able to
assign
the
value in the type field based on the StartDate. The New2008 values
will
start showing up for new clients who start date is on or after March
1st
of
2008. The New2007 clients are those people whose start date is in
the
3/1/07
to 2/28/08 range. Right now, the sales rep for these New2007
clients
receive
a 14 percent commission rate. However, starting on 3/1/08, the
sales
rep
for
these New2007 client will have a commission rate of 10 percent and
the
New2008 clients will have a commission rate of 14 percent. Also on
3/1/08,
the Existing2006 will have a 10 percent commission rate which is the
same
as
it is now, however, the Pre2006 clients whill have a reduced
commission
rate
from 10 percent to 8 percent. I want to write a query for the
future
which
will analyze the current date, compare it to the start date and
automatically
change the commission rates on 3/1/08. So without having to change
my
percentages manually, it will start giving these New2007 clients a
10
percent
rate rather than the current 14 percent rate.

Can someone help?

thanks,