Anyone know of an easier way to write this formula?

[Guest]

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

 

[Michel Walsh]

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