With a table holding the data:
Drf ChdEq Low High RiskMax Rate
1 1 0 200 200 70
0 1 20 200 200 70
1 0 0 200 200 100
0 1 0 20 200 100
0 0 20 200 200 100
0 0 20 200 2 160
Then,
---------------------------------------
SELECT ..., Nz(Rates.Rate, 130) AS LDL_Goal
FROM myTable LEFT JOIN Rates
ON myTable.DiabetesRF = Rates.Drf
AND myTable.[CHD or Equ] = Rates.ChdEq
AND [CHD Risk] >= Rates.Low
AND [CHD Risk] < Rates.High
AND TotalRisk < Rates.RiskMax
---------------------------------------
Note that I assumed that the minimum and maximum values for [CHD Risk] are
0 and 200. I also implied your >20 really meant >=20 while <20 is exactly a
strict inequality.
If the query is not faster, at least, now, all the data is in a table and
can be easily modified (by accessing the table rather than by accessing the
SQL code).
Hoping it may help,
Vanderghast, Access MVP
"Access Joe" <(E-Mail Removed)> wrote in message
news:82E88361-C834-4CEC-8BC5-(E-Mail Removed)...
> Does anyone know of a better way to write this? It's a formula in a query
> which DOES give me the right results, but seems like I'm taking a very
> long
> way.
>
> LDL_Goal: IIf([DiabetesRF]=1 And [CHD or Equ]=1,70,IIf([DiabetesRF]=0 And
> [CHD or Equ]=1 And [CHD Risk]>20,70,IIf([DiabetesRF]=1 And [CHD or
> Equ]=0,100,IIf([DiabetesRF]=0 And [CHD or Equ]=1 And [CHD
> Risk]<20,100,IIf([DiabetesRF]=0 And [CHD or Equ]=0 And [CHD
> Risk]>20,100,IIf([DiabetesRF]=0 And [CHD or Equ]=0 And [CHD Risk]<20 And
> [TotalRisk]<2,160,130))))))
>
> Any help would be appreciated. Thank you.
>
> Joe
|
Similar Threads
