1. Registered User
Join Date
Jan 2013
Posts
53

Hello All, kindly please take a look at the following code for calculating the Median. Code for Min and Max work just fine but need some assistance here.

Am basically trying to pull the Median from a structure such as this:

Part Number Length Width Height
00010236 4.00 3.50 1.50
00010237 5.00 4.50 2.50
00010238 6.00 5.50 3.50

My expression is - Med: MedianOfRst("Master_HMA","Length")

When I run this I get the Median based on every single record listed for Length. How do I structure the code and expression so I can pull the median from Length, Width and Height for each Part Number?

Code:
```Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double

Dim MedianTemp As Double
Dim RstOrig As Recordset

Dim strSQL As String

strSQL = "SELECT [" & fldName & "] FROM [" & RstName & "]"

If strWhere <> vbNullString Then
strSQL = strSQL & " WHERE " & strWhere
End If

Set RstOrig = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)

RstOrig.Sort = fldName
Dim RstSorted As Recordset
Set RstSorted = RstOrig.OpenRecordset()
If RstSorted.RecordCount Mod 2 = 0 Then
RstSorted.AbsolutePosition = (RstSorted.RecordCount / 2) - 1
MedianTemp = RstSorted.Fields(fldName).Value
RstSorted.MoveNext
MedianTemp = MedianTemp + RstSorted.Fields(fldName).Value
MedianTemp = MedianTemp / 2
Else
RstSorted.AbsolutePosition = (RstSorted.RecordCount - 1) / 2
MedianTemp = RstSorted.Fields(fldName).Value
End If
MedianOfRst = MedianTemp
End Function```
Last edited by VBAnewbie; 05-17-13 at 21:18. Reason: Updated code and provided additional example

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
just add the part number (or a variable holding he part number) to the list of parameters passed as part of the function call

3. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
There's already an argument for this - pass the part number as the third argument:
Med: MedianOfRst("Master_HMA","Length", "[Part Number] = '00010238'").

However, I'm not sure that I fully understand your requirement. Could you please illustrate what you want to see in the result set, based on the samples above?

4. Registered User
Join Date
Jan 2013
Posts
53
Originally Posted by weejas
There's already an argument for this - pass the part number as the third argument:
Med: MedianOfRst("Master_HMA","Length", "[Part Number] = '00010238'").

However, I'm not sure that I fully understand your requirement. Could you please illustrate what you want to see in the result set, based on the samples above?
weejas, here is my desired result for Med based on the data above:

Part Number Length Width Height Med
00010236 4.00 3.50 1.50 3.50
00010237 5.00 4.50 2.50 4.50
00010238 6.00 5.50 3.50 5.50

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
how?
the median is the middle number in a sorted list of numbers
from the data you have provided there is no way of gettign the media vlaue
as
1) there is only value per part
2) you don't indicate which of the 3 numeric values you want as a median value

it seems like you want the width of a part. what is it to be?
the median of length or width or height or all of a part
the width of a part

i think it would help if you provided more data examples and a workthrough as to how you want the result derived

right now what you are saying (ie you want the median value) or do you want the middle value of 3 measurements?

if its the middle value of length,width, height then you could do it with a compound IIF statement

6. Registered User
Join Date
Jan 2013
Posts
53
healdem, here is a sample of the data my QRY is generating and the desired result for the median. I have another code which generates the Max and Min, now if I could just get the Med to work everything would be golden

Part Number - Length - Width - Height - [Max] - [Min] - - [Med]
00010236 ____4.00___0.71___3.54____4.00___0.71___3.54
00011614 ____7.00___8.00___1.50____8.00___1.50___7.00
00012459 ____5.00___4.50___0.80____5.00___0.80___4.50
00021763_____6.00___5.20___2.00____6.00___2.00___5.20

I don't necessarily want to assign the Median as the middle value rather the code should determine which is the Median based on a series of 3 numbers (Length/Width/Height) for each record (Part Number). Hope this makes sense

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
No it doesn't make sense
the median is the middle value of a sorted list of numbers.

it sounds like you want the middle value of Length, Height & width, so your current function will not do that

so it sounds like you want a compound IIF

8. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
What you want and what your code is returning are two different things.

The median value of a given list is the value mid-way between the lowest and highest. It's not necessarily a value that occurs in the list. For example, the median value of the list {2}{4}{6}{8} is 5.

the code should determine which is the Median based on a series of 3 numbers (Length/Width/Height) for each record (Part Number)
This is not the median. Your function currently looks for a single named column from a named table, possibly restricted to a subset by a WHERE clause, and returns the middle value (offset by one place towards the lowest value in recordsets with odd numbers of records). From what you've said, you want to pass your function a part number, and have it look at three independent values and return the one that isn't highest or lowest. This is not the median.

It's easy to achieve. Create a recordset holding the three values from the source table. Pass each one into an element of an array. Sort the array, and then return the second element. But it's not the median.

9. Registered User
Join Date
Jan 2013
Posts
53
Originally Posted by weejas
It's easy to achieve. Create a recordset holding the three values from the source table. Pass each one into an element of an array. Sort the array, and then return the second element. But it's not the median.
My apologies but am not sure how I would even start this...

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
Originally Posted by weejas
The median value of a given list is the value mid-way between the lowest and highest. It's not necessarily a value that occurs in the list. For example, the median value of the list {2}{4}{6}{8} is 5.
no thats the mean/average, or at least thats my understanding
Mean, Median, Mode, and Range
the median is the middle value of a sorted list of numbers

however thats not what (I think) you want. I think you want the middle value between Length, Width and Height, a different proposition

you should be able to get want you want with a compound IIF

the boolean logic is
if height >= width
...and if width >= length, then use width otherwise use length
else
if width >= height
...and if height >= length then use heigth, otherwise use height
else
if height>= length
...and if length >= width then use width, otherwise use length

11. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
You already have a function that generates an SQL statement from passed parameters and uses that string to open a recordset. From that beginning, amend the parameters SQL to pick up the values that you want. Declaring an array is easy enough:
Code:
`Dim adblDimension(1 To 3) As Double`
The values from the recordset each get assigned to an element of the array.

Sadly, VBA doesn't have any native array sort methods or functions, but if you search for "Sorting arrays in VBA", you should get enough hits to start you off.

Have a play and see what you come up with.

12. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105
Sorry - healdem is quite right. It's been a long time since I learned that, and haven't had much use for any of them other than mean since then.

Join Date
Nov 2004
Location
out on a limb
Posts
13,692
I still think you are better off using a compound IIF, but in the meantime you could use a function

Code:
```public Function GetMiddleValue(Value1 as double, Value2 as double, Value3 as Double) as double
'this function finds the middle value in a set of three numbers
if value1 >= Value2 then
if Value2 >= Value3 then
GetMiddleValue = Value2
else
GetMiddleValue = Value3
endif
elseif value2 >= Value3 then
if Value3>= Value1 then
GetMiddleValue = Value3
else
GetMiddleValue = Value1
endif
elseif value3 >= Value1 then
if Value2>= Value1 then
GetMiddleValue = Value2
else
GetMiddleValue = Value1
endif
endif
end function```
the above is air code, it hasn't been tested it may not compile, the logic may be wrong, but it should give you an outline

ideally plonk the function in a code module.

then call the function as part of your SQL query. eg:-
Code:
`select PartNo, Length, Height, Width, GetMiddleValue(Length, Height, Width) as MiddleValue from MyTable`
you will need to make certain you reaplce the column and table names to be the same as yours

14. Registered User
Join Date
Jan 2013
Posts
53
healdem, the Public Function you provided worked like a charm! Thanks so much! Will definitely explore the compound IIf solution as well.

weejas, a great thanks to you as well. Will look into the developing script based on your recommendation and post if I run into any snags

15. Grumpy old man (training)
Join Date
Sep 2006
Location
Surrey, UK
Posts
1,105