Expert: Geoff - 2/9/2004

Question
Hi Geoff,
I have used the codes for calculating Median from Microsoft and it works fine. But I can use only for one list. In my report I have groupings (3layers). I want the median value to come out for a particular group and not for the complete field list. This is the function I used. I can supply tName with the table name and fldName with the field that I want median for and I will get the median. What should I do if I want median for the group. In the example below,

Type    Amount
1         10
3         15
1          7
1          8
3         20
3         25

This function will get median for the whole list. How can I get median for Type 1 and median for Type 3 in the same report dynamically by using grouping/sorting.

Function Median(tName As String, fldName As String) 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()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
           "] FROM [" & tName & "] WHERE [" & fldName & _
           "] IS NOT NULL ORDER BY [" & fldName & "];")
'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

Thanks.
Mark Kaphle


Answer
Hello Mark

Modify the function as follows:
1. Pass the Type as a parameter
2. Modify the SQL to use the Type

See below:

Function Median(tName As String, fldName As String, nType 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()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
        "] FROM [" & tName & "] WHERE [" & fldName & _
        "] IS NOT NULL AND [Type] = " & nType & " ORDER BY [" & fldName & "];")
'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

Please check that the field name in the table is indeed [Type], and also beware that this is a reserved word so always use [] square brackets around it...

I hope this helps

Kind regards

Geoff