Assigning a number to a date range

[ChuckW]

Hi,

I have a field called StartDate in a Customer table. I want to create a
table called Commissions that has two fields: DateValue and CommissionRate.
There would be three records. 1,2 and 3 for the DateValue field and 8%, 10%
and 14% for the CommissionRate. I then want to write a query that will
analyze a date range and assign a value based on the StartDate field in the
Customer table. so 3/1/06 to 2/28/07 = 1, 3/1/07 to 2/28/08 = 2 and 3/1/08
to 2/28/09 = 3. I then want to join the values generated from this query to
the DateValue field in the Commissions table. So if someone has a StartDate
of 12/1/06, they have a value of 1 which means they are assigned a commission
rate of 8 %. If someone has a start date of 12/1/07, they have a value of 2
and a commision rate of 10% and if someone has a startdate of 12/1/08, they
have a datevalue of 3 and a commison rate of 14 %. Does anyone have any
ideas of how to do this?

Thanks,

 

[KARL DEWEY]

Substitute date for mileage and percent for Rate in the table and query.
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

 

[ChuckW]

Karl,

Thanks for your help. Just to confirm, is the table you describe below
called RateTable? It looks like you have a separate table called Travel. I
am not familiar with your sql statement [ratetable-mileage].rate. What does
this statement do?

Thanks,

--
Chuck W

Substitute date for mileage and percent for Rate in the table and query.
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little

Hi,

I have a field called StartDate in a Customer table. I want to create a
table called Commissions that has two fields: DateValue and CommissionRate.
There would be three records. 1,2 and 3 for the DateValue field and 8%, 10%
and 14% for the CommissionRate. I then want to write a query that will
analyze a date range and assign a value based on the StartDate field in the
Customer table. so 3/1/06 to 2/28/07 = 1, 3/1/07 to 2/28/08 = 2 and 3/1/08
to 2/28/09 = 3. I then want to join the values generated from this query to
the DateValue field in the Commissions table. So if someone has a StartDate
of 12/1/06, they have a value of 1 which means they are assigned a commission
rate of 8 %. If someone has a start date of 12/1/07, they have a value of 2
and a commision rate of 10% and if someone has a startdate of 12/1/08, they
have a datevalue of 3 and a commison rate of 14 %. Does anyone have any
ideas of how to do this?

Thanks,

 

[KARL DEWEY]

It looks like you have a separate table called Travel.
Substitute your Customer table for Travel.
[RateTable-Mileage] is the table I posted that has the high and low with
corresponding number - your dates & number.

I am not familiar with your sql statement [ratetable-mileage].rate. What does this statement do?

[RateTable-Mileage] is the table I posted.

I just did not take the time to build everything for you. You should be
able to substitute your table and fields.

--
KARL DEWEY
Build a little - Test a little

Karl,

Thanks for your help. Just to confirm, is the table you describe below
called RateTable? It looks like you have a separate table called Travel. I
am not familiar with your sql statement [ratetable-mileage].rate. What does
this statement do?

Thanks,

--
Chuck W

Substitute date for mileage and percent for Rate in the table and query.
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521

SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));

--
KARL DEWEY
Build a little - Test a little

Hi,

I have a field called StartDate in a Customer table. I want to create a
table called Commissions that has two fields: DateValue and CommissionRate.
There would be three records. 1,2 and 3 for the DateValue field and 8%, 10%
and 14% for the CommissionRate. I then want to write a query that will
analyze a date range and assign a value based on the StartDate field in the
Customer table. so 3/1/06 to 2/28/07 = 1, 3/1/07 to 2/28/08 = 2 and 3/1/08
to 2/28/09 = 3. I then want to join the values generated from this query to
the DateValue field in the Commissions table. So if someone has a StartDate
of 12/1/06, they have a value of 1 which means they are assigned a commission
rate of 8 %. If someone has a start date of 12/1/07, they have a value of 2
and a commision rate of 10% and if someone has a startdate of 12/1/08, they
have a datevalue of 3 and a commison rate of 14 %. Does anyone have any
ideas of how to do this?

Thanks,