2 criteria lookup setup

[djdeuph]

Hello,

I have a query where I am calculating a new field from the SUM of some other fields. This result coupled with a value from another table would be used to return a value (should this be in a table?). Here's an example:

qryRiskRating
Select (tblRisk.Category1 + tblRisk.Category2 + tblRisk.Category3 + tblRisk.Category4)/4 AS RatingCalc, IIf ([RatingCalc]>=2,"High",IIf([RatingCalc]<=1.5,"Low","Medium")) AS RiskRating

So I want to take the RiskRating and another field to return a value. I had tried to do a ridiculous Nested If statement but Access decided to say it was too complex. I know there should be a way to do a lookup table of some sort but am at a loss on how to do it.

Thanks in advance for your help!

Derek

 

[rac2]

I was able to do something similar.
An expression based on two columns

X: [tblaccounts]![Collection_Status]*[tblaccounts]![Amount_Paid]

Use the alias X in a second expression

Y: IIf([X]=0,"No",IIf([X]<=5,"Maybe","yes"))

Use that alias with another column in a third expression.

Z: IIf([Y]="No","Stop",IIf([Y]="Maybe","Stop","Go"))

So maybe it is not the complexity. Are the datatypes appropriate where you use "RiskRating and another field to return a value.

 

[dhookom]

Dereck,
In my opinion, you have two major but common flaws with your application. The first is multiple category fields. I would normalize your table structure so you don't have to calculate across fields. You should calculate across records. You can create a union query that normalizes your current table structure. Then create a simple totals query that averages your single normalized field.

Your second flaw is trying to create business rules calculations in a complex expression. >2 High, <=1.5 Low, and Medium should be stored in lookup/range tables. Storing these rules in an expression in a query is not the best solution. A better solution is to create a small user-defined function that accepts the rating calc and returns a rating title. The best solution involve creating a table of ranges with titles:

[tt][blue]
tblRatingRanges
====================
LowRate HighRate RateTitle
0 1.5 Low
1.5 2.0 Medium
2.0 9999 High
[/blue][/tt]
You can use this query with the average of your ratings to retrieve the RateTitle.

If you are will to accept another change, I would never use a derived field/column (RatingCalc) in another column calculation in a single query.

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]