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

MORE INFORMATION

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