ACC: How to Use Code to Derive a Statistical Median

ID: Q95918


The information in this article applies to:
  • Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

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 "middle" 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 "Building Applications with Microsoft Access" 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 "Introduction to Programming" manual in Microsoft Access version 1.x or the "Building Applications" manual in Microsoft Access version 2.0.


MORE INFORMATION

To create a procedure that determines the statistical median of a set of numbers:

  1. Create a module and type the following line in the Declarations section if it is not already there:


  2. 
    Option Explicit 
  3. 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("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 
    In Microsoft Access 1.x:


  4. 
        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("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 

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("<TableName>", "<FieldName>") 
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("<TableName>", "<FieldName>")
      Y = Median("<TableName>", "<FieldName>")
      If X > Y Then Debug.Print "The median for X is greatest."
   End Function 

Additional query words: statistics

Keywords : kbprg kbdta AccCon KbVBA
Version : WINDOWS:1.0,1.1,2.0,7.0,97
Platform : WINDOWS
Issue type : kbhowto


Last Reviewed: August 2, 1999
© 2000 Microsoft Corporation. All rights reserved. Terms of Use.