Access VBA: Calculating Median on data using GROUP BY on two columns

Question

I am trying to find a way to calculate the median of a dataset in access, that is grouped by two columns, typeA, typeB.

This is a sample of the table:

ID (autonumber)	typeA (large number)	typeB (large number)	total (large number)
1	1	1	15
2	2	1	15
3	1	1	45
4	2	1	44
5	1	2	19
6	1	2	4
7	1	2	34
8	2	2	19
9	2	2	18

Using Access 2016

Currently I am using the following code snippet:

Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, GroupFieldName2, GroupFieldValue2, MedianFieldName)
DoCmd.SetWarnings False

Dim rs As DAO.Recordset

Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)

If IsDate(GroupFieldValue) Then
    GroupFieldValue = "#" & GroupFieldValue & "#"
ElseIf Not IsNumeric(GroupFieldValue) Then
    GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
End If

If IsDate(GroupFieldValue2) Then
    GroupFieldValue2 = "#" & GroupFieldValue2 & "#"
ElseIf Not IsNumeric(GroupFieldValue) Then
    GroupFieldValue2 = "'" & Replace(GroupFieldValue2, "'", "''") & "'"
End If

rs1.Filter = GroupFieldName & "=" & GroupFieldValue
rs1.Sort = MedianFieldName

Set rs = rs1.OpenRecordset()
rs.Move (rs.RecordCount / 2)

If rs.RecordCount Mod 2 = 0 Then
    varMedian1 = rs.Fields(MedianFieldName)
    rs.MoveNext
    fMedian = varMedian1 + rs.Fields(MedianFieldName) / 2
Else
    fMedian = rs.Fields(MedianFieldName)
End If

End Function

As it stands, this works great for grouping by one column, but I cannot figure out how to allow it to group by on both typeA and typeB. I have by editing the rs1.filter line but to no avail.

Any help with the code, or a better approach would be appreciated.

Thank you!

NOTE: solved using parfaits solution below. added line medianVBA = fmedian before the end of the function.

Answer 1

Consider an extension of @Fionnuala's great answer to calculate median in MS Access by accommodating an open-ended number of grouping variables.

VBA (save below in a standard module of Access project)

Code builds a dynamic SQL string for DAO recordset call for later median calculation. Special handling required for groupings with 0-2 records and null values for groupings.

Public Function MedianVBA(ParamArray Arr() As Variant) As Double
On Error GoTo ErrHandle
    Dim N As Long
    Dim tblName As String, numCol As String, grpVals As String
    Dim strSQL As String
    Dim db As DAO.Database, rs As DAO.Recordset
    Dim varMedian As Double, fMedian As Double
    
    'BUILD DYNAMIC SQL
    tblName = Arr(0)
    numCol = Arr(1)
    grpVals = " WHERE " & numCol & " IS NOT NULL "
        
    For N = 2 To UBound(Arr) Step 2
        If Arr(N + 1) = "" Or IsNull(Arr(N + 1)) Then
            grpVals = grpVals & " AND " & Arr(N) & " IS NULL"
        ElseIf IsDate(Arr(N + 1)) Then
            grpVals = grpVals & " AND " & Arr(N) & " = #" & Arr(N + 1) & "#"
        Else
            grpVals = grpVals & " AND CStr(" & Arr(N) & ") = '" & Arr(N + 1) & "'"
        End If
    Next N

    strSQL = "SELECT " & numCol _
              & " FROM " & tblName _
              & grpVals _
              & " ORDER BY " & numCol
  
    'CALCULATE MEDIAN
    Set db = CurrentDb
    Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    If rs.RecordCount = 0 Then
        MedianAcc = fMedian
        GoTo ExitHandle
    ElseIf rs.RecordCount = 1 Then
        MedianAcc = rs.Fields(numCol)
        GoTo ExitHandle
    End If
    
    rs.Move (rs.RecordCount / 2)
    rs.MovePrevious

    If rs.RecordCount Mod 2 = 0 Then
        varMedian = rs.Fields(numCol)
        If rs.RecordCount = 2 Then
            rs.MoveLast
        Else
            rs.MoveNext
        End If
        fMedian = (varMedian + rs.Fields(numCol)) / 2
    Else
        fMedian = rs.Fields(numCol)
    End If

    rs.Close
    MedianAcc = fMedian
  
ExitHandle:
    Set rs = Nothing: Set db = Nothing
    Exit Function
    
ErrHandle:
    MsgBox Err.Number & ": " & Err.Description, vbCritical, "RUNTIME ERROR"
    Resume ExitHandle
End Function

Do note, above VBA function uses a ParamArray where first argument expects the source table and second column expects the numeric column and the remaining is open-ended for group column name and value pairs. Signature of call is as follows:

=MedianAcc("table_name", 
           "numeric_col", 
           "group1_column", "group1_value",
           "group2_column", "group2_value", 
           ...)

SQL (stored query that calls above VBA function)

Below runs a one-group and two-group median calculation.

SELECT t.typeA, t.typeB
       , MedianVBA('[myTable]', '[total]', '[typeA]', t.typeA) AS MedianGrp1, 
       , MedianVBA('[myTable]', '[total]', '[typeA]', t.typeA, '[typeB]', t.typeB) AS MedianGrp2
FROM myTable t
GROUP BY t.typeA, t.typeB