How do i set a value based on a data range

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I want to perform a query on a database table with a 1:1 relationship. Given
the returned value of the query I want to create a new calculated field that
assings a letter rating (A-D) based on data ranges. eg

score <= 05 then [newfield] = "A"
05 < score <= 10 then [newfield] = "B"
15 < score <= 20 then [newfield] = "C"
25 < score <= 30 then [newfield] = "D"

Given this rating I want to then display it in a field in a form?

Can anyone help with how to one or both of these?

Thanks
 
Create another table for you Grades. This table should contain a
lowerBound, UpperBound, and a Grade fields. I've added a couple of rows
because I wasn't too sure what you wanted to do with the range bands you
left out.

LowerBound UpperBound Grade
-1 5 A
5 10 B
10 15 B-
15 20 C
20 25 C-
25 30 D

Now create a query that uses your other query or table and this new GRADES
table, it would look something like:

Select T.StudentID, T.Score, G.Grade
FROM YourFirstTable T, GRADES G
WHERE T.Score > G.LowerBound AND T.Score <= G.UpperBound

HTH
Dale
 
Back
Top