Microsoft KB Archive/96575

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

Article ID: 96575

Article Last Modified on 1/18/2007

-

APPLIES TO


 * Microsoft Access 1.0 Standard Edition
 * Microsoft Access 1.1 Standard Edition
 * Microsoft Access 2.0 Standard Edition
 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q96575



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



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 &quot;middle&quot; 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 &quot;Building Applications with Microsoft Access&quot; 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 &quot;Introduction to Programming&quot; manual in Microsoft Access version 1.x or the &quot;Building Applications&quot; 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 &quot;[&quot; when the name includes spaces.

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

If tName$ = &quot;&quot; Or fldName$ = &quot;&quot; Then Exit Function

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

If ModalField1 <> ModalField2 Then Mode = &quot;The Result is Modal: &quot; & ModalField1 Else Mode = &quot;The Result is Bimodal: &quot; & ModalField1 & _ &quot; and &quot; & 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(&quot;TableName&quot;, &quot;FieldName&quot;).

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(&quot;[TableName]&quot;, &quot;[FieldName]&quot;) Y = Mode(&quot;[Table Name]&quot;, &quot;[Field Name]&quot;) If X > Y Then Debug.Print &quot;The mode for X is greatest.&quot; End Function

Keywords: kbhowto kbprogramming KB96575

-

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

© Microsoft Corporation. All rights reserved.