Microsoft KB Archive/95918

= ACC: How to Use Code to Derive a Statistical Median =

Article ID: 95918

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q95918



Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
This article shows you how to create a Visual Basic for Applications procedure to open a table, read the data, and find the statistical median. The median is a measure of central tendency, another &quot;middle&quot; of a data set, like the mean or average). The data set consisting of the numbers 1, 2, 3, 6, and 100 has a median of 3, the middle of the set. The data set consisting of the numbers 1, 2, 6, and 10 has a median of 4, the middle of the set [(2 + 6) / 2 = 4].

This article assumes that you are familiar with Visual Basic for Applications and with creating Microsoft Access applications using the programming tools provided with Microsoft Access. For more information about Visual Basic for Applications, please refer to your version of the &quot;Building Applications with Microsoft Access&quot; manual.

NOTE: Visual Basic for Applications is called Access Basic in Microsoft Access versions 1.x and 2.0. For more information about Access Basic, please refer to the &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; manual in Microsoft Access version 2.0.



MORE INFORMATION
To create a procedure that determines the statistical median of a set of numbers:   Create a module and type the following line in the Declarations section if it is not already there: Option Explicit  Type the following procedure:

NOTE: In the following sample code, an underscore (_) at the end of a line is used as a line-continuation character. Remove the underscore from the end of the line when re-creating this code in Access Basic.

In Microsoft Access 2.0, 7.0, and 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(&quot;SELECT [&quot; & fldName$ & _               &quot;] FROM [&quot; & tName$ & &quot;] WHERE [&quot; & fldName$ & &quot;] IS _                NOT NULL ORDER BY [&quot; & fldName$  & &quot;];&quot;) 'NOTE: To include nulls when calculating the median value, omit 'WHERE [&quot; & fldName$ & &quot;] 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 In Microsoft Access 1.x: Function Median (tName$, fldName$) As Single Dim MedianDB As Database Dim ssMedian As Snapshot Dim RCount%, i%, x%, y%, OffSet% Set MedianDB = CurrentDB Set ssMedian = MedianDB.CreateSnapshot(&quot;SELECT [&quot; & fldName$ & _               &quot;] FROM [&quot; & tName$ & &quot;] WHERE [&quot; & fldName$ & &quot;] IS _                NOT NULL ORDER BY [&quot; & fldName$  & &quot;];&quot;) 'NOTE: To include nulls when calculating the median value, omit 'WHERE [&quot; & fldName$ & &quot;] 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

How to Use the Median Function
Create a form and add a text box control where you want to display the median values of a data set. Set the ControlSource property of the text box control to the following: =Median(&quot;&quot;, &quot;&quot;) The value of this control is the median of the data set. Another way to use this function is to call it from within another function that compares the median from different data sets. For example, Function CompareMedians Dim MyDB as Database .     .      .      X = Median(&quot;&quot;, &quot;&quot;) Y = Median(&quot;&quot;, &quot;&quot;) If X > Y Then Debug.Print &quot;The median for X is greatest.&quot; End Function

Additional query words: statistics

Keywords: kbhowto kbprogramming KB95918

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.