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