Median Function

[krishkrishnan]

Iam a relatively a new user of Microsoft Access. My problem is that the Median function is not available for use in queries (while I can use Avg, Min, Max). How do I get the Median of a field content ?

Would be thankful for your help.

 

[R. Hicks]

This function will work in Access 97:

Function Median(tName$, fldName$) As Single
Dim MedianDB As Database
Dim ssMedian As Recordset
Dim RCount%, i%, x%, y%, OffSet%

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

HTH
RDH

[This message has been edited by R. Hicks (edited 10-06-2001).]