ACC: How to Use Code to Derive a Statistical Median

• Microsoft Access versions 1.0, 1.1, 2.0, 7.0, 97

SUMMARY

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

This article describes a Visual Basic for Applications procedure that you can use 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:

         Option Explicit
   

2. 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:
   
       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>")

   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