Microsoft KB Archive/209839

= ACC2000: How to Calculate Row-Level Statistics =

Article ID: 209839

Article Last Modified on 7/15/2004

-

APPLIES TO


 * Microsoft Access 2000 Standard Edition

-



This article was previously published under Q209839



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

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



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 Access 2000.



MORE INFORMATION
NOTE: The functions in this article take advantage of the ParamArray declaration within Microsoft Access 2000. The ParamArray declaration enables the user-defined functions to accept a variable number of arguments.  Start Microsoft Access, and then create a new database.  Create the following new table, and then save it as 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 then type the following sample data:

NOTE: You must enter a blank where specified, not a zero. Otherwise, the results do not match those shown.   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 then 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">  Field: tblTest.* Table: tblTest Field: FldCount: Val(RCount([Test1],[Test2],[Test3],[Test4])) Format: Fixed Field: FldSum: Val(RSum([Test1],[Test2],[Test3],[Test4])) Format: Fixed Field: FldAvg: Val(RAvg([Test1],[Test2],[Test3],[Test4])) Format: Fixed Field: FldStDev: Val(RStDev([Test1],[Test2],[Test3],[Test4])) Format: Fixed Field: FldStDevP: Val(RStDevP([Test1],[Test2],[Test3],[Test4])) Format: Fixed Save the query as qryTest. </li>  Run the query. Note that you receive the following results: <pre class="fixed_text">  ID Test1 Test2 Test3 Test4 FldCount FldSum FldAvg FldStDev FldStDevP 1   80    84                 2        164  82.00     2.83      2.00   2   100    75    25     0     4        200  50.00    45.64     39.53   3    88    89    90           3        267  89.00     1.00      0.82   4    50          75   100     3        225  75.00    25.00     20.41                    </li></ol>

<div class="references_section">