If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
classify
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! |
#2
|
|||
|
|||
classify
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? |
#3
|
|||
|
|||
classify
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 ---- HTH Dale |
#4
|
|||
|
|||
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? . |
#5
|
|||
|
|||
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? . |
Thread Tools | |
Display Modes | |
|
|