[AccessD] Median Number

Thanks to everyone responding to my median question.  My mind was asleep
yesterday - I couldn't get any search to find anything.

Reuben Cummings
GFC, LLC
phone: 812.523.1017
email: reuben at gfconsultants.com

> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com]On Behalf Of John R. Porter
> Sent: Friday, November 07, 2003 5:20 AM
> To: 'Access Developers discussion and problem solving'
> Subject: RE: [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
>
> _______________________________________________
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
>