[AccessD] Median Number

Hi Reuben

> Anyone have a good way to find the median value in a table of unkown number
> of records?

You could look up our own list at 2001-03-06:

<quote>

Sub GetTableMedian ()
  Dim dblMedian As Double
  dblMedian = GetTableMedian ("", "tblData", "FieldName")
  Debug.Print (dblMedian)
End Sub

Function GetTableMedian(strDatabase As String, strTable As String, strField
As String) As Double
  'strDatabase - name of the database to look in or "" for the current
database
  'strTable - name of the table to analyze
  'strField - name of the field to analyze
  'Returns: median

  Dim dbsTemp As Database
  Dim rstTemp As Recordset
  Dim strSQL As String
  Dim lngElements As Long
  Dim lngIndex As Long
  Dim fInterpolate As Boolean
  Dim dblMedian As Double

  If strDatabase = "" Then
    Set dbsTemp = CurrentDb()
  Else
    Set dbsTemp = DBEngine.Workspaces(0).OpenDatabase(strDatabase)
  End If

  strSQL = "SELECT DISTINCTROW [" & strField & "] FROM [" & strTable & "] "
  strSQL = strSQL & "WHERE ([" & strField & "] Is Not Null) "
  strSQL = strSQL & "ORDER BY [" & strField & "];"

  Set rstTemp = dbsTemp.OpenRecordset(strSQL)
  If Not rstTemp.EOF Then
    rstTemp.MoveLast
    lngElements = rstTemp.RecordCount
    If lngElements = 1 Then
      dblMedian = rstTemp(strField)
    Else
      lngIndex = Int((lngElements + 1) / 2)
      fInterpolate = (lngElements Mod 2 = 0)
      rstTemp.MoveFirst
      rstTemp.Move (lngIndex - 1)
      dblMedian = rstTemp(strField)
      If fInterpolate Then
        rstTemp.MoveNext
        dblMedian = (dblMedian + rstTemp(strField)) / 2
      End If
    End If
  Else
    dblMedian = 0
  End If

  rstTemp.Close
  dbsTemp.Close

  GetTableMedian = dblMedian

End Function

William Hindman

</quote>

/gustav