ACC: How to Use Code to Derive a Statistical Median (95918)

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:

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("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>")

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