[AccessD] Median Number

This can also be done (faster I expect) by a SQL statement without coding
and ADO or DAO as the case may be, though the SQL is quite complex. If you
can get hold of it, there's an excellent article about this in the May 2003
issue of 'Smart Access' magazine. You can get a trial subscription at
http://www.pinpub.com/html/main.isx?sub=29

John R. Porter
I.T. Services
University of Strathclyde
Faculty of Education
76 Southbrae Drive
Glasgow
G13 1PP
e-mail: j.r.porter at strath.ac.uk
Tel. 0141 950 3289


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Gustav Brock
Sent: 07 November 2003 09:30
To: Access Developers discussion and problem solving
Subject: Re: [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

_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com