ACC2000: How to Calculate Row-Level Statistics (209839)


The information in this article applies to:

  • Microsoft Access 2000
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.
  1. Start Microsoft Access, and then create a new database.
  2. 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
  3. 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
  4. 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
  5. Create the following query based on the tblTest table that you created above: 
       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.
  6. Run the query. Note that you receive the following results:
       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

REFERENCES

For additional information about database normalization, click the article number below to view the article in the Microsoft Knowledge Base:

209534 ACC2000: Database Normalization Basics


For more information about parameter arrays, in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type understanding parameter arrays in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about the 'For Each...Next Statement', in the Visual Basic Editor, click Microsoft Visual Basic Help on the Help menu, type for each...next statement in the Office Assistant or the Answer Wizard, and then click Search to view the topic.

For more information about statistical formulae, click Microsoft Excel Help on the Help menu, type about statistical analysis tools in the Office Assistant or the Answer Wizard, and then click Search to view the topics returned.
Modification Type:MinorLast Reviewed:7/15/2004
Keywords:kbhowto kbinfo KB209839
©2004 Microsoft Corporation. All rights reserved.