ACC: How to Use Code to Derive the Statistical Mode (96575)

SUMMARY

This article shows you how to create a Visual Basic for Applications function that creates a snapshot that determines the frequency of occurrences for all numbers in a table.

The most frequently occurring number in a data set is called the mode, which is a measure of central tendency (a "middle" measure of a data set). For example, a data set consisting of the numbers

{1,1,2,2,2,3,6,100}

has a mode of 2 because 2 occurs three times in the set. And a data set consisting of the numbers

{1,1,1,2,2,2,3,6,10}

is bimodal, and has modes of 1 and 2 (both numbers occur three times in the set).

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 Visual Basic for Applications function that determines the mode, open a new or a previously created module and enter the following code.

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

   Function Mode (tName$, fldName$)
   ' The function will initialize:
   ' - A variable for the database object.
   ' - A variable for the snapshot.
   ' - Sets the database object to the opened database.
   ' - Creates a snapshot based on the database object.
   ' - This function requires table and field name parameters where the
   '     arguments are passed using "[" when the name includes spaces.

      Dim ModeDB As Database
      Dim ssMode As Recordset
      Dim ModalField1, ModalField2, ModalResult1, ModalResult2

      If tName$ = "" Or fldName$ = "" Then Exit Function

      Set ModeDB = CurrentDB()
      Set ssMode = ModeDB.OpenRecordSet("SELECT DISTINCTROW Count _
                      (" & fldName$ & ") AS Mode, " & fldName$ & " _
                      FROM " & tName$ & " GROUP BY " & fldName$ & " _
                      ORDER BY Count(" & fldName$ & ") _
                      DESC;",DB_OPEN_SNAPSHOT)
      ModalField1 = ssMode(fldName$)
      ModalResult1 = ssMode!Mode
      ssMode.MoveNext
      ModalField2 = ssMode(fldName$)
      ModalResult2 = ssMode!Mode

         If ModalField1 <> ModalField2 Then
            Mode = "The Result is Modal: " & ModalField1
         Else
            Mode = "The Result is Bimodal: " & ModalField1 & _
                     " and " & ModalField2
         End If
      ssMode.Close
      ModeDB.Close
   End Function

How to Use the Mode() function

Create a form with text box controls that will reflect all measures of central tendency of a data set. In the ControlSource property for the text box control, enter:

=Mode("TableName", "FieldName").

The value of this control will be the statistical mode of the data set. Another way to use this function is to call it from within another function that compares mode from different data sets. For example:

   Function CompareModes()
      Dim MyDB as Database
      .
      .
      .
      X = Mode("[TableName]", "[FieldName]")
      Y = Mode("[Table Name]", "[Field Name]")
      If X > Y Then Debug.Print "The mode for X is greatest."
   End Function