I have a data field containing multiple values ranging from -1 to 50. I need to count the number of records with values in the following ranges: "-1, >0and<0.5, >0.5and<3.0, >3.0and<5.0, >5.0". a Typical end result should indicate as follows: (-1: 50 records),(>0and<0.5: 20 records) etc. I would like to do this with a query and can not seem to crack this one. Can anybody please help. I am running Access 2007
A small auxiliary table Ranges can help here: three fields, LOW, HIGH and
RANGENAME. A "non equi join" query will give you the ranges:
SELECT yourtable.<whatever>, Ranges.Rangename
FROM yourtable
INNER JOIN Ranges ON yourtable.numberfield >= Ranges.Low AND
yourtable.numberfield < Ranges.High;
Adjust the >= and < appropriately so that values exactly on the boundary line
end up in the right range.
If a second table is unacceptable, you can use a much less efficient Switch()
function: in a vacant Field cell type
Rangename: Switch([numberfield] = -1, "-1", [numberfield] < 0.5, "0 to 0.5",
[numberfield] < 3.0, "0.5 to 3", [numberfield] < 5.0, "3 to 5", True, "Over
5")
adjusted as appropriate. The Switch() function takes arguments in pairs and
evaluates them left to right; when it encounters a pair in which the first
argument is TRUE it returns the second member of that pair and quits.
--
John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also
http://www.utteraccess.com