ACC: How to Use Code to Derive the Statistical Mode

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

SUMMARY

Moderate: Requires basic macro, coding, and interoperability skills.

This article describes 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, the data set consisting of the numbers

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

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

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 module 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.

Create the following Mode() function in the module:

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

   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

Keywords          : kbprg PgmHowTo PgmOthr
Version           : 1.0 1.1 2.0 7.0 97
Platform          : WINDOWS
Hardware          : x86
Issue type        : kbhowto