How to find the range of a set of numbers in Access 2003? 1

[SeadnaS]

Does anybody know a way to find the range of a set of numbers in access 2003? I have a form with 8 text boxes where you input each number and I want to output the range of these numbers to another text box.

Any help much appreciated!

 

[MajP]

SeadnaS,
Although I can give you a function that you employ just like the StandardDev function, this kind of reiterates my point. If you normalize your data using a union query then calculating the max, min, std dev, avg are all simple Sql statements and can be done in your query.

 

[SeadnaS]

Thanks for your reply.

I don't have any data yet to normalize! I need all 8 numbers and the range in a table and text boxes on my form or I won't have what i need. I'm sure its quite simple but I am inexperienced and can't find any other help online.

 

[MajP]

Sorry for the confusion. I am speaking about database noramlization, which is a design theory for proper database design. This has absolutely nothing to do with statistical normalization which has to do with fitting values to a normal distribution with a given mean and std. So yes your data structure can most definately be normalized through a union (normalization) query.

 

[SeadnaS]

Thanks again for your reply.

OK. Well I don't actually know how to do that. What I need is a way to calculate the range (highest number minus the lowest number) of a set of 8 numbers entered in 8 text boxes in a form. I really don't know how to do that. I need a table that contains the 8 numbers entered in 8 fields and i need the range in another (9th) field. Can't find info on this anywhere online.

 

[MajP]

Public Function getMax(ParamArray varVals() As Variant) As Variant
Dim varVal As Variant
'get first non null value
For Each varVal In varVals
If IsNumeric(varVal) Then
getMax = varVal
Exit For
End If
Next varVal
'loop all
For Each varVal In varVals
If IsNumeric(varVal) And varVal > getMax Then
getMax = varVal
End If
Next varVal
End Function
Public Function getMin(ParamArray varVals() As Variant) As Variant
Dim varVal As Variant
'get first non null value
For Each varVal In varVals
If IsNumeric(varVal) Then
getMin = varVal
Exit For
End If
Next varVal
'loop all
For Each varVal In varVals
If IsNumeric(varVal) And varVal < getMin Then
getMin = varVal
End If
Next varVal
End Function

 

[SeadnaS]

Thanks again for your help!

 

[MajP]

Assume table one

ID x1  x2     x3     x4   x5
1  12  13     15.5   6	  8.5
2  7   12.8   10.2   9	  1

Normalize it

SELECT 
  ID, 
  x1 AS ItemValue
FROM 
  tblOne
UNION
SELECT ALL
  ID, 
  x2 AS ItemValue
FROM 
  tblOne
UNION
SELECT ALL
  ID, 
  x3 AS ItemValue
FROM 
  tblOne
UNION
SELECT ALL
  ID, 
  x4 AS ItemValue
FROM 
  tblOne
UNION SELECT ALL
  ID, 
  x5 AS ItemValue
FROM 
  tblOne;

output

ID  ItemValue
1   6
1   8.5
1   12
1   13
1   15.5
2   1
2   7
2   9
2   10.2
2   12.8

now it is real simple to get your stats

SELECT 
 qryNormalData.ID, 
 Sum(qryNormalData.ItemValue) AS ItemSum, 
 Avg(qryNormalData.ItemValue) AS ItemAvg, 
 Min(qryNormalData.ItemValue) AS ItemMin, 
 Max(qryNormalData.ItemValue) AS ItemMax, 
 StDev(qryNormalData.ItemValue) AS ItemStd
FROM 
 qryNormalData
GROUP BY 
 qryNormalData.ID;

output

ID  ItemSum ItemAvg  ItemMin  ItemMax   ItemStd
1   55      11       6        15.5      3.76
2   40      8        1        12.8      4.44

That is the better solution. Quicker and more efficient.

 

[SeadnaS]

Awesome. Its great to see a few different options. Thanks for your guidance.

 

[SeadnaS]

I get a syntax error with that union query. Heres my version:

SELECT
ID,
x1 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x2 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x3 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x4 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x5 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x6 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x7 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x8 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x9 AS ItemValue
FROM tblCHILD
UNION
SELECT
ID,
x10 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x12 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x13 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x14 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x15 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x16 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x17 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x18 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x19 AS ItemValue
FROM tblCHILD
UNIONSELECT
ID,
x20 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x21 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x22 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x23 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x24 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x25 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x26 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x27 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x28 AS ItemValue
FROM tblCHILD
UNION
SELECT
ID,
x29 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x30 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x31 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x32 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x33 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x34 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x35 AS ItemValue
FROM tblCHILD
UNION
SELECT ALL
ID,
x36 AS ItemValue
FROM tblCHILD;

 

[SeadnaS]

Found my mistakes got it working. Only thing is some of the values are not numbers.

 

[SeadnaS]

Some fields non numerical values are causing problems is there a way that I can omit them or get the query to ignore them?