ACC2000: How to Use Code to Derive the Statistical Mode (210569)

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

MORE INFORMATION

NOTE: The sample code in this article uses Microsoft Data Access Objects. For this code to run properly, you must reference the Microsoft DAO 3.6 Object Library. To do so, click References on the Tools menu in the Visual Basic Editor, and make sure that the Microsoft DAO 3.6 Object Library check box is selected.

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.

Function Mode(tName As String, fldName As String)
' 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 DAO.Database
   Dim ssMode As DAO.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 ModalResult1 <> ModalResult2 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("<TableName>", "<FieldName>")
   If X > Y Then Debug.Print "The mode for X is greatest."
End Function