A Microsoft Office (Excel, Word) forum. OfficeFrustration

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.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

classify



 
 
Thread Tools Display Modes
  #1  
Old December 7th, 2009, 01:17 AM posted to microsoft.public.access.queries
Miriam
external usenet poster
 
Posts: 11
Default 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!


Ads
  #2  
Old December 7th, 2009, 03:43 AM posted to microsoft.public.access.queries
Allen Browne
external usenet poster
 
Posts: 11,706
Default 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  
Old December 7th, 2009, 06:16 PM posted to microsoft.public.access.queries
Dale Fye
external usenet poster
 
Posts: 2,651
Default 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  
Old December 8th, 2009, 06: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?



.

  #5  
Old December 8th, 2009, 09: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?



.


 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 03:36 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2018, Jelsoft Enterprises Ltd.
Copyright 2004-2018 OfficeFrustration.
The comments are property of their posters.