Microsoft KB Archive/189584

= ACC: How to Calculate Row-Level Statistics (95/97) =

Article ID: 189584

Article Last Modified on 1/23/2007

-

APPLIES TO


 * Microsoft Access 95 Standard Edition
 * Microsoft Access 97 Standard Edition

-



This article was previously published under Q189584



Advanced: Requires expert coding, interoperability, and multiuser skills.



SUMMARY
Although Microsoft Access has several built-in functions that enable you to perform statistical analysis across records, it does not have a built-in function to perform statistical analysis across multiple columns within a single row. This article shows you how to create several sample user-defined functions that you can use to get row-level statistics in Microsoft Access version 95 and 97.

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.



MORE INFORMATION
NOTE: The functions in this article take advantage of the ParamArray declaration within Microsoft Access version 95 and 97. The ParamArray declaration enables the user-defined functions to accept a variable number of arguments.  Start Microsoft Access, and create a new database.  Create the following new table, and then save it as tblTest:       Table: tblTest ---      Field Name: ID          Data Type: AutoNumber Indexed: Yes (No Duplicates) Field Name: Test1 Data Type: Number Field Size: Double Field Name: Test2 Data Type: Number Field Size: Double Field Name: Test3 Data Type: Number Field Size: Double Field Name: Test4 Data Type: Number Field Size: Double   View the tblTest table in Datasheet view and enter the following sample data:        ID   Test1   Test2   Test3   Test4 1     80      84        2     100      75      25       0        3      88      89      90        4      50              75     100                      Open a new module and type the following functions: '*************************************************************     'Declarations section of the module. '*************************************************************

Option Explicit

Function RSum(ParamArray FieldValues) As Variant '--        ' Function RSum adds all the arguments passed to it. ' If all arguments do not contain any data, RSum will return a        ' null value. '--        Dim dblTotal As Double, blnValid As Boolean Dim varArg As Variant For Each varArg In FieldValues If IsNumeric(varArg) Then blnValid = True dblTotal = dblTotal + varArg End If        Next If blnValid Then ' One of the arguments was a number. RSum = dblTotal Else ' Noo valid points to add. RSum = Null End If     End Function

Function RCount(ParamArray FieldValues) As Variant '-        ' Function RCount will accept a variable number of arguments, ' and returns a count of arguments containing numbers. '-        Dim lngCount As Long Dim varArg As Variant For Each varArg In FieldValues If IsNumeric(varArg) Then lngCount = lngCount + 1 End If        Next RCount = lngCount End Function

Function RAvg(ParamArray FieldValues) As Variant '        ' Function RAvg will average all the numeric arguments passed to         ' the function. If none of the arguments are numeric, it will ' return a null value. '-        Dim dblTotal As Double Dim lngCount As Long Dim varArg As Variant For Each varArg In FieldValues If IsNumeric(varArg) Then dblTotal = dblTotal + varArg lngCount = lngCount + 1 End If        Next If lngCount > 0 Then RAvg = dblTotal / lngCount Else RAvg = Null End If     End Function

Function RStDev(ParamArray FieldValues) As Variant '-        ' Function RStDev calculates the Standard Deviation of         ' sample data passed as arguments. NOTE: The standard deviation ' of sample data is only valid if more than one argument is        ' numeric. If only one of the arguments passed to the function ' contains a numeric value, the function will correctly return ' a null value. '-        Dim dblSum As Double, dblSumOfSq As Double Dim n As Long Dim varArg As Variant For Each varArg In FieldValues If IsNumeric(varArg) Then dblSum = dblSum + varArg dblSumOfSq = dblSumOfSq + varArg * varArg n = n + 1 End If        Next If n > 1 Then ' Variance/StDev applies if more than a single point RStDev = Sqr((n * dblSumOfSq - dblSum * dblSum) _              / (n * (n - 1))) Else RStDev = Null End If     End Function

Function RStDevP(ParamArray FieldValues) As Variant '---        ' Function RStDevP returns the Standard Deviation of the ' Population for all the arguments passed to it. The standard ' deviation of the population is only valid for one or more ' numeric values. If none of the arguments passed to        ' the function contains a numeric value, the function will return ' a null. '---        Dim dblSum As Double, dblSumOfSq As Double Dim n As Long Dim varArg As Variant For Each varArg In FieldValues If IsNumeric(varArg) Then dblSum = dblSum + varArg dblSumOfSq = dblSumOfSq + varArg * varArg n = n + 1 End If        Next If n > 0 Then 'only applies if points available RStDevP = Sqr((n * dblSumOfSq - dblSum * dblSum) / n / n)        Else RStDevP = Null End If

End Function   Create the following query based on the tblTest table that you created above: <pre class="fixed_text">     Query: qryTest -     Type: Select Query Tables: tblTest

Field: tblTest.* Table: tblTest Field: FldCount: RCount([Test1],[Test2],[Test3],[Test4]) Field: FldSum: RSum([Test1],[Test2],[Test3],[Test4]) Field: FldAvg: RAvg([Test1],[Test2],[Test3],[Test4]) Field: FldStDev: RStDev([Test1],[Test2],[Test3],[Test4]) Field: FldStDevP: RStDevP([Test1],[Test2],[Test3],[Test4]) </li>  Run the query. Note that you receive the following results: <pre class="fixed_text">  ID   Test1   Test2   Test3   Test4 FldCount FldSum FldStDev FldStDevP 1     80      84                        2    164    2.828     2    2     100      75      25       0        4    200   45.644    39.528    3      88      89      90                3    267        1     0.816    4      50              75     100        3    225       25    20.412 </li></ol>

<div class="references_section">