Thread: classify
View Single Post
  #4  
Old December 8th, 2009, 07:58 PM posted to microsoft.public.access.queries
KARL DEWEY
external usenet poster
 
Posts: 10,767
Default classify

Create a table that defines the range, and you can then create a query to
look up that table and return the range number.
It takes a bit of effort to get this kind of query working.
Translation table --
Low_Avg High_Avg Classification
0 3.99 1
4.0 4.75 2
4.76 8.99 3

I find it easy, something like this --
SELECT [YourFields], [Classification]
FROM YourTable, Translation
WHERE Average Between Low_Avg AND High_Avg;

--
Build a little, test a little.


"Allen Browne" wrote:

Your ranges don't look like a simple, linear scale, so it can't be done with
a simple expression. Create a table that defines the range, and you can then
create a query to look up that table and return the range number.

It takes a bit of effort to get this kind of query working. Tom Ellision
explains how in this article:
Lookup in a range of values in a query
at:
http://allenbrowne.com/ser-58.html

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.


"Miriam" wrote in message
...
After calculating an average, I need to classify each member as 1, 2, 3,
...
10. 1 = an average of 3.99 or lower, 2 = an average of 4.0 - 4.75, etc.

How can I set a field to a particular classification, or "handicap", based
on given ranges? Does this require coding or VBA, or can it be done
through a query?



.