Calculating Median in Access Query Across Groups

[EBox]

Hello:

I have spent numerous hours evaluating VBA code I found on the internet to calculate Median in a query. The only problem is that I would like to get median values for each date presented in the query result, and the VBA code I am using (the only one that worked) calculates the same median for each date in the result, which is actually the median for ALL values in the query.

I am calling this function through a query with the following: MedianF("mytable","myfield"). I would like to get the median for the query results for each date shown in the result, which is captured in a field named [DOS].

I would greatly appreciate it if one of the VBA experts can help identify how to achieve this, using the code below.

Many thanks in advance!

Function MedianF(ptable As String, pfield As String) As Single
'*******************************************
'Re:        [URL unfurl="true"]http://www.utteraccess.com/forums/postlist.php?Cat=&Board=85[/URL]
'Purpose:   Return median value from a recordset
'Coded by:  raskew
'Inputs:    ? medianF("Orders", "Freight") <enter.
'Output:    41.36 (may vary according to hom much
'           you've fiddled with this table).
'*******************************************

Dim rs       As Recordset
Dim strSQL   As String
Dim n        As Integer
Dim sglHold  As Single

    strSQL = "SELECT " & pfield & " from " & ptable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
    Set rs = CurrentDb.OpenRecordset(strSQL)
    rs.MoveLast
    n = rs.RecordCount
    rs.Move -Int(n / 2)
    
    If n Mod 2 = 1 Then 'odd number of elements
       MedianF = rs(pfield)
    Else                'even number of elements
       sglHold = rs(pfield)
       rs.MoveNext
       sglHold = sglHold + rs(pfield)
       MedianF = sglHold / 2
    End If
    rs.Close
End Function

Function Medianx(ParamArray varNums() As Variant) As Variant
'*******************************************
'Purpose:   Return the median from a parameter
'           array of numbers
'Coded by:  raskew
'Inputs:    (1) ? medianx(1,11,8,3,6,13)
'           (2) ? medianx(1,11,8,3,6)
'Output:    (1) 7
'           (2) 6
'*******************************************

Dim i    As Integer
Dim j    As Integer
Dim n    As Integer
Dim temp As Integer

    n = UBound(varNums)
    If (n < 0) Then
       Exit Function
    Else
       'use bubble sort to sequence the elements
       '(good for small number of elements but
       'slow for larger sorts)
       For i = 0 To UBound(varNums)
          For j = 0 To UBound(varNums)
             If varNums(i) < varNums(j) Then
                temp = varNums(i)
                varNums(i) = varNums(j)
                varNums(j) = temp
             End If
          Next j
       Next i
    End If
    'If there's an odd number of elements, median = center element
    'e.g. if elements = 1,3,6,8,11 then median = 6
    'With an even number elements, median = average of 2 center elements
    'e.g. if elements = 1,3,6,8,11,13 then median = (6+8)/2 = 7
    Medianx = IIf(n Mod 2 = 0, varNums(n / 2), (varNums(n \ 2) + varNums(n \ 2 + 1)) / 2)
    
    'To display results, uncomment the following 3 lines
    'For i = 0 To UBound(varNums)
    '   Debug.Print varNums(i)
    'Next i

End Function

 

[PHV]

Replace this:
Function MedianF(ptable As String, pfield As String) As Single
with this:
Function MedianF(ptable As String, pfield As String, strWhere As String) As Single

and this:
strSQL = "SELECT " & pfield & " from " & ptable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
with this:
strSQL = "SELECT " & pfield & " from " & ptable & " WHERE " & pfield & ">0 AND " & strWhere & " Order by " & pfield & ";"

Tnen in the query:
MedianF("mytable","myfield","[DOS]=#" & Format([DOS],"yyyy-mm-dd") & "#")

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[EBox]

Thanks for the guidance - I put in your changes and now get a Run-time error '3021': No current record.

I should mention that the [DOS] field I have is in mm/dd/yy format, and I even tried changing your query recommendation above to match that, but received the same error.

This [DOS] field actually comes from a query (not a table), calculated from a date/time field to match a format of mm/dd/yy. But I'm not sure why this isn't working.

Would greatly appreciate your help!!!!

Thank you!