**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.

[daniel2335]

Shouldn't this be on the Access forum?

[davec8723]

It may be, I apologize I am new to this forum. Could you point me in the right direction as in where to find it? Thanks

[daniel2335]

http://bytes.com/topic/access/answers/

[davec8723]

I looked through the Access forum, but I cannot find the appropriate thread. There is over 500 though, so I may have missed it. Could anyone just give me an idea of how I would go about trying to accomplish this? I would really appreciate it.

[daniel2335]

In mysql I would left join the grades onto the scorecard table using a where statement to join the appropriate grade onto each of the scord card rows. Then also add to the where statement to select the relevant scorecard row.

So if your scorecard table had playerID and score fields and your grades had as you said. To select the grade of the player with ID "12" I would do...

SELECT gradeName FROM scorecard LEFT JOIN grades WHERE (scorecard.score <= grades.LowerLimit) AND (scorecard.score >= grades.UpperLimit) AND scorecard.playerID = 12

But thats my mysql solution, for the access one you will have to post on the access forum i gave you above.