By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,316 Members | 2,317 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,316 IT Pros & Developers. It's quick & easy.

**Instead of an IIF statement...

P: 3
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.
Jul 15 '09 #1
Share this Question
Share on Google+
5 Replies


P: 49
Shouldn't this be on the Access forum?
Jul 16 '09 #2

P: 3
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
Jul 16 '09 #3

P: 3
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.
Jul 16 '09 #5

P: 49
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...

Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 16 '09 #6

Post your reply

Sign in to post your reply or Sign up for a free account.