Karl,
Thanks for your response. To better understand your answer, I built a small
test database with two tables as follows:
--tblPatients--
PatientID Name ClaimAmt
01 Joe 100
02 Mary 1700
03 Tom 900
04 Steve 1100
Then in a second table, I have the rates:
--tblRates--
Min Max Rate
0 500 0.02
501 1000 0.04
1001 1500 0.06
1501 2000 0.08
I built a form (frmPatient) based on the Patients table. In that form, I
placed a text box (txtRate) that I want to display the correct rate based on
which record is showing. I'm assuming that I have to build a statement
similar to yours in the Control Source property for that text field that
would pull the correct rate from tblRates. Here's what I tried:
= SELECT tblRates.Rate FROM tblRates WHERE (((tblPatients.ClaimAmt) Between
[Min] and [Max]));
I get a syntax error when I enter this. Am I anywhere near the correct
answer? Thanks again for your help.
--
Steve C
KARL DEWEY said:
Use a 'translation' table like this example ---
-- [RateTable-Mileage] --
MinMiles MaxMiles Rate
0 500 0.2075
501 1500 0.1582
1501 999999 0.1521
-- Travel --
Name Mileage
joe 650
dd 100
ll 10000
p 10
SELECT Travel.Name, Travel.Mileage, [RateTable-Mileage].Rate
FROM Travel, [RateTable-Mileage]
WHERE (((Travel.Mileage) Between [MinMiles] And [MaxMiles]));
Query results --
Name Mileage Rate
joe 650 0.1582
dd 100 0.2075
ll 10000 0.1521
p 10 0.2075
--
KARL DEWEY
Build a little - Test a little
:
I have some medical claims data, and depending on the level of the charges, I
need to apply a different percentage to estimate what actually will be paid.
So for example, if the charged claim amount is less than $5000, I want to
apply a factor of 0.25; if it between $5000 and $10000, I want to apply a
factor of 0.30; and so on up to say 20 different levels. Is there a way I
can do this in Access, without writing a bunch of nested "IIF's"?
