classify

[Miriam]

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?

thanks!

 

[Allen Browne]

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

 

[Dale Fye]

I prefer Allen's method (relatively easy to implement and extremely easy to
change if you need to make changes to the category ranges). This technique
is also ideal if you are working in a multi-user environment, because changes
to the table in the backend will apply to everyone immediately, without
having to field a new front-end application.

However, there are several other options:

1. Use the Switch function. The Switch function accepts an array of
parameter pairs where the odd numbered parameters are expressions that will
evaluate to True or False. If the expression evaluates to True, then the
even numbered parameter which follows it is what gets returned. If the
expression evaluates to False, then the function goes to the next odd
numbered parameter, and evaluates it.

You can test this in the immediate window by typing

intAge = 5
?switch(intAge < 4, 1, intAge <= 4.75, 2, intAge < 6.3, 3, True, "invalid")

I generally add a parameter pair at the end (as above) which allows me to
identify if none of the conditions specified were met. If you don't do this,
the function will return NULL.

The down side of using this method, is that you have to find each and every
instance where you have used this method if you need to make a simple change
to the values in the expressions that define your categories. If your
application is being used by others, then you have to distribute a new front
end.

2. Another method would be to create a user defined function which you
would pass the age and which would return an age category. This one also has
the drawback that you must distribute a new front-end if you make changes.

Public Function fnAgeCat(dblAge as double) as Integer

if dblAge < 3.99 then
fnAgeCat = 1
elseif dblAge <= 4.75 then
fnAgeCat = 2
elseif dblAge < 6.3 then
fnAgeCat = 3
else
fnAgeCat = 0
endif

End Sub

 

[KARL DEWEY]

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;

 

[vanderghast]

There are obviously two problems.

The first one is about 3.9999 falling nowhere, unless High_Avg = Low_Avg of
some other record, AND that you relinquish BETWEEN operator and use

WHERE Average >= Low_Avg AND Average < High_Avg;


note the strict <, not <= that BETWEEN would imply.

The second problem is more disputable but it is one of maintenance and of
strict independence of the data from one record to the other. As example,
what happens if, starting with:


Low_Avg High_Avg
0 4
4 4.75
4.75 9
9 32000


the first record is changed to

0 4.5


without changing the second record?


Sure, there are cases where such overlap is WHAT YOU MAY WANT, example:
Low High Category
0 2 baby
2 18 child
18 75 adult
75 200 elder
2 12 young
12 18 teenager


in cases where you may want stats from 2 to 18 as a group, but also, with
the two sub-group young / teenagers. Which you get, here, with just one
query (using an inner join)


But I doubt that the OP case is such a case.


Sure, this problem of potential of overlapping intervals can be deal with by
other means, and while, I personally consider that the order of complexity
that the fully normalized case bring does NOT worth its benefit, in general,
myself I use the not fully normalized Low / High fields, it is nice to
know the potential problems linked to that kind of design.




Vanderghast, Access MVP

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.

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

Reply to group, rather than allenbrowne at mvps dot org.





.