ACC2000: How to Calculate Row-Level Statistics

Article translations Article translations
Article ID: 209839 - View products that this article applies to.
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).

Expand all | Collapse all

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.

Properties

Article ID: 209839 - Last Review: July 15, 2004 - Revision: 1.2
APPLIES TO
  • Microsoft Access 2000 Standard Edition
Keywords: 
kbhowto kbinfo KB209839

Give Feedback

 

Contact us for more help

Contact us for more help
Connect with Answer Desk for expert help.
Get more support from smallbusiness.support.microsoft.com