Microsoft KB Archive Search

Notice: This website is an unofficial Microsoft Knowledge Base (hereinafter KB) archive and is intended to provide a reliable access to deleted content from Microsoft KB. All KB articles are owned by Microsoft Corporation. Read full disclaimer for more details.

  1. Home
  2. ACC2000: How to Use Code to Derive the Statistical Mode

ACC2000: How to Use Code to Derive the Statistical Mode

View products that this article applies to.

This article was previously published under Q210569
Moderate: Requires basic macro, coding, and interoperability skills.

This article applies only to a Microsoft Access database (.mdb).

↑ Back to the top

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

↑ Back to the top

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

↑ Back to the top

Keywords: KB210569, kbprogramming, kbhowto

↑ Back to the top

Article Info
Article ID : 210569
Revision : 2
Created on : 6/24/2004
Published on : 6/24/2004
Exists online : False
Views : 350