Calculate Median in Access Query

[kjo1080]

Does anyone know how to calculate the median in an Access Query? there is a formula for average but not for median. Does anyone know of a way to do this or of how to write a formula in a module that will work?

Any help is appreciated!

Thanks,

kjo1080

 

[misssami]

That Worked!! Here is the final code:

Public Function MedianOfRst(RstName As String, fldName As String, Optional strWhere As String) As Double
     '** Call function in query by Median: MedianOfRst("Table Name","Field Name","[Group]= " & [Group])
     
     'This function will calculate the median of a recordset. The field must be a number value.
     Dim MedianTemp As Double
     Dim RstOrig As Recordset
    
     '** NEW CODE ADDED BY GIACOMO **
     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)
     '** END OF NEW CODE **


     ' ** OLD CODE REPLACED IN THE BLOCK ABOVE **
     'Set RstOrig = CurrentDb.OpenRecordset(RstName, dbOpenDynaset)
      
     ' ** ORIGINAL CODE **
     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

Again, I really appreciate your help!!

-kjo1080

oh well, like I said air code... it should have been

if strWhere <> vbNullString then 
        strSQL = strSQL & " WHERE " & strWhere 
end if

Giacomo

Hi GIACOMO,
I have also been trying to figure out the process to compute the median for the set of data I have. I am data as below generated through query in Access:
<table border="0" cellpadding="0" cellspacing="0" width="237"><col style="width: 43pt;" width="57"> <col style="width: 50pt;" width="67"> <col style="width: 31pt;" width="41"> <col style="width: 24pt;" width="32"> <col style="width: 30pt;" width="40"> <tbody><tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt; width: 43pt;" height="20" width="57">
Stations</td> <td class="xl63" style="width: 50pt;" width="67">
Year</td> <td class="xl63" style="width: 31pt;" width="41">
A</td> <td class="xl63" style="width: 24pt;" width="32">
B</td> <td class="xl63" style="width: 30pt;" width="40">
C</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">101</td> <td class="xl63">1951</td> <td class="xl64">2.1</td> <td class="xl63">75</td> <td class="xl63">48</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">101</td> <td class="xl63">1952</td> <td class="xl64">0.7</td> <td class="xl63">77</td> <td class="xl63">38</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">102</td> <td class="xl63">1951</td> <td class="xl64">5.1</td> <td class="xl63">80</td> <td class="xl63">48</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">102</td> <td class="xl63">1952</td> <td class="xl64">3.7</td> <td class="xl63">83</td> <td class="xl63">36</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">103</td> <td class="xl63">1951</td> <td class="xl64">3.7</td> <td class="xl63">66</td> <td class="xl63">95</td> </tr> <tr style="height: 15pt;" height="20"> <td class="xl63" style="height: 15pt;" height="20">103</td> <td class="xl63">1952</td> <td class="xl64">3.8</td> <td class="xl63">70</td> <td class="xl63">70
</td> </tr> </tbody></table>
I have to compute median for A, B, C for each stations. I think I have to group them by stations and sort the data with in each stations. I have been following your code but I am not sure whether I can use that code for my purpose. Similarly, this is my first time trying to run code in access interface (but I am familiar in running macros in Excel). So, I am not being able to figure the location/process where/how I need to put your sample code and try to make it work.

Your help is really appreciated!

Thanks!