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

• Microsoft Access versions 7.0, 97

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.

1. Start Microsoft Access, and create a new database.



2. 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 

3. 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 

4. 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 

5. Create the following query based on the tblTest table that you created above:




      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]) 

6. Run the query. Note that you receive the following results:




   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  

REFERENCES

For more information about database normalization, please see the following article in the Microsoft Knowledge Base:

Q100139 ACC: Database Normalization Basics

Additional query words: inf

Keywords          : kbdta AccCon PgmHowto QryHowto KbVBA 
Version           : WINDOWS:7.0,97
Platform          : WINDOWS 
Issue type        : kbhowto 

Last Reviewed: July 30, 1999