Results 1 to 15 of 15

Thread: Median Function

  1. #1
    Join Date
    Jan 2013
    Posts
    53

    Unanswered: Median Function

    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

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    your function already has the capability to handle this
    just add the part number (or a variable holding he part number) to the list of parameters passed as part of the function call
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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?
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  4. #4
    Join Date
    Jan 2013
    Posts
    53
    Quote Originally Posted by weejas View Post
    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

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  6. #6
    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
    00012ADU00__32.00__3.20___18.00___32.00__3.20___18.00
    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

  7. #7
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  8. #8
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  9. #9
    Join Date
    Jan 2013
    Posts
    53
    Quote Originally Posted by weejas View Post
    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...

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by weejas View Post
    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
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  12. #12
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    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.
    10% of magic is knowing something that no-one else does. The rest is misdirection.

  13. #13
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    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
    I'd rather be riding on the Tiger 800 or the Norton

  14. #14
    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. #15
    Join Date
    Sep 2006
    Location
    Surrey, UK
    Posts
    995
    Provided Answers: 2
    You're welcome - always happy to help!
    10% of magic is knowing something that no-one else does. The rest is misdirection.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •