Microsoft KB Archive/210569

= ACC2000: How to Use Code to Derive the Statistical Mode =

Article ID: 210569

Article Last Modified on 6/24/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



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



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

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("", "") Y = Mode("", "") If X > Y Then Debug.Print "The mode for X is greatest." End Function

Keywords: kbhowto kbprogramming KB210569

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.