**Instead of an IIF Statement...

[Davec8723]

I am building a database in Access 2003 with a included scorecard. In the scorecard, i am atttempting to offer a grade on the bottom, based on a relevant percentage. I will refer to this percentage as X. I have built another Table, which has three columns, grade name, lower limit, and upper limit. When X falls between lower limit and upper limit, I would like the query to generate the relevant grade name on that respective record. Is this possible? Heres an example.

Grade Name Lower Limit Upper Limit
Level 0 0% 5%
Level 1 5.01% 10%
Level 2 10.01% 15%

Therefore, if X = 7%, I would like the query to generate Level 1.

I know this can be done with an if statement, however I would like the users to be able to modify this table above as they see fit, making modification simple. Any help would be appreciate.

 

[PWise]

Select field1,field2,X gradename
from table
inner join Grade
on x between lowerlimit and Upperlimit

 

[Davec8723]

Pwise, Thank You for your reply, could you be a little more specific on how to go about this. I am not an expert in MS Access, but really need to build this query to finish a project I am working on. I do not know SQL very well, and have been using the expression builder. If you could provide me with an example of what I would put into that I would appreciate it very much, Thanks!

 

[JS1521253]

Davec8723,
I'm not a great SQL person either, but I think you can do it this way:

SELECT Score.Field1, Score.Percentage, GradeNm.Grade

FROM Score
  INNER JOIN GradeNm
    ON Score.Percentage > GradeNm.GradeMin
    AND Score.Percentage < Grade.GradeMax

Just switch your view to SQL and add the fields you want from your Score table (I used Field1 as an example). I think this is pretty close to the code you'll need to get what you need. I have a similar table/query set-up in my own database.