High/Mid/Low Query

[Hartkoorn]

It must be me but:

I have a table with the column Grade and a column Grade_Group
In the column Grade I can give a value from 0 - 10 (pull-down).
When I grade between 0 and 3 I want the column Grade_Group to be updated to Low (easy, even I can do that with an update query).
But if the grading is between 4 and 7 the Grade_Group needs to be Mid and if the grading is between 8 and 10 High.
In this case I am able to create reports on High/Mid/Low criteria.
If I put this all in (one) query the query changes all data in the Grade_Group to Low, or I get a duplicate error because there is just one update field.
Someone, I searched the forums but nothing came up.
Thanks in advance!

 

[raskew]

Hi -

Grade_Group is in fact a calculated field and shouldn't be stored*. You can (in this particular scenario), with a query, determine the grade_group using the Choose() function, e.g.
Choose(Grade\4 + 1, "Low", "Medium","High")

0 - 3 \4 (note the \ operator) returns 0, + 1 returns 1
4 - 7\4 returns 1, + 1 returns 2
8 - 10\4 returns 2, + 1 returns 3

Lookup the Choose() function and the \ operator in the Help File for further info.

HTH = Bob

Added:
* In retrospect, your particular scenario lends itself to the Choose() function. However, given a different set of circumstances where the parameters were different and irregular (e.g. 0-5 = "Low", 6-9 = "Medium", 10 - 14 = "High"), you'd probably want to use the Switch() function, e.g.:

x = [Grade]
? Switch(x<= 5, "Low", x<= 9, "Medium", True, "High")

Add the Switch() function to your Help File lookup list.

Bob

 

[Hartkoorn]

Wow,
Thanks, it works perfectly!