I have a query that is grouped by State, Description and Month. I have a function in VB that is designed to give me median by month only. See function below. I call it in the query. I need to adjust this to group by three columns in the query, not one. I do not understand what the function is actually doing so I cannot modify it myself.
Function.....................................
Option Compare Database
Option Explicit
Function Median(tName As String, fldName As String, Optional qField As String, Optional qValue As Long) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
If Len(qField) > 0 Then
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & qField & _
"] =" & qValue & " ORDER BY [" & fldName & "];")
Else
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
End If
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x = RCount Mod 2
If x <> 0 Then
OffSet = ((RCount + 1) / 2) - 2
For i% = 0 To OffSet
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(fldName)
ssMedian.MovePrevious
y = ssMedian(fldName)
Median = (x + y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function
SQL...........................................
SELECT Data.State, Data.Description, Data.Month, Median("Data","DaysOpen","Month",[Month]) AS MedianValue
FROM Data
GROUP BY Data.State, Data.Description, Data.Month
HAVING (((Data.Description)="Bodily Injury"));