Table trouble

  • Thread starter Thread starter jefferysf via AccessMonster.com
  • Start date Start date
J

jefferysf via AccessMonster.com

I am designing a database that tracks usage of corporate jets for personal
use. This is considered income to the employees that use the company planes
for personal use. The IRS has come up with a formula that hits the
employee's W-2 as income when they fly personally. The part of the formula I
am having trouble with is the charge per mile flown changes as follows:

0 - 500 miles = $0.2075/mile
501 - 1500 m iles = $0.1582/mile
Over 1500 miles = $0.1521/mile

What would be the best way to capture these diffent mileage ranges and attach
them to the current rates? Also, they are revised every six months.

Thanks in advance,
Jeff
 
Use a rate table with three fields -
MinMiles - MaxMiles - Rate
0 500 $0.2075
501 1500 $0.1582
1501 99999 $0.1521

Use this query ---
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));
 
KARL said:
Use a rate table with three fields -
MinMiles - MaxMiles - Rate
0 500 $0.2075
501 1500 $0.1582
1501 99999 $0.1521

Use this query ---
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));
I am designing a database that tracks usage of corporate jets for personal
use. This is considered income to the employees that use the company planes
Click to expand...
[quoted text clipped - 11 lines]
Thanks in advance,
Jeff
Click to expand...
Click to expand...
Thank you Karl - that makes total sense!
 
Back
Top