Thread: classify
View Single Post
  #5  
Old December 8th, 2009, 10:17 PM posted to microsoft.public.access.queries
vanderghast
external usenet poster
 
Posts: 593
Default classify

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





"KARL DEWEY" wrote in message
...
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?



.