I am using the following vba code to calculate the median of a table
in MS Access:
Public Function MedianOfRst(RstName As String, fldName As String) As
Double
'This function will calculate the median of a recordset. The
field must be a number value.
Dim MedianTemp As Double
Dim RstOrig As Recordset
Set RstOrig = CurrentDb.OpenRecordset(RstName, 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
While it works great for calculating the median of an entire row in a
table, I now have the need to calculate the median of several subsets
of data in a table. Say we have a table:
Shift ProcessingTime
Day 2
Night 1
Day 3
Day 5
Night 6
Night 9
I need to be able to return the median processing time of orders
processed during day and during the night. I know I could run 2
queries, one to split the table into day and night orders, then use
the median function, but my real project requires the data to be split
up into 8 categories so this would be rather time consuming and
involve a lot of sub-queries. Any ideas on how to modify the above
query to only calculate the median if another cell in the row meets
certain criteria?
Thanks,
Andrew V. Romero