Re: classify
- From: "vanderghast" <vanderghast@com>
- Date: Tue, 8 Dec 2009 16:17:49 -0500
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" <KARLDEWEY@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message news:5E720B4B-99EB-4961-9CD8-43FB2D938134@xxxxxxxxxxxxxxxx
look up that table and return the range number.Create a table that defines the range, and you can then create a query to
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" <Miriam@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:B34B564E-6955-41EB-8863-EB38810773DE@xxxxxxxxxxxxxxxx
> 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?
.
.
- References:
- classify
- From: Miriam
- Re: classify
- From: Allen Browne
- Re: classify
- From: KARL DEWEY
- classify
- Prev by Date: combining fields
- Next by Date: RE: IIf statement in query criteria
- Previous by thread: Re: classify
- Next by thread: RE: classify
- Index(es):
Relevant Pages
|
