That Worked!! Here is the final code:
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
Code:
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!