You are currently offline, waiting for your internet to reconnect

ACC2000: How to Calculate Row-Level Statistics

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 toperform statistical analysis across records, it does not have a built-infunction to perform statistical analysis across multiple columns within asingle row. This article shows you how to create several sampleuser-defined functions that you can use to get row-level statistics inAccess 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 ExplicitFunction 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 IfEnd FunctionFunction 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 = lngCountEnd FunctionFunction 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 IfEnd FunctionFunction 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 IfEnd FunctionFunction 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 IfEnd 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.
inf
Properties

Article ID: 209839 - Last Review: 07/15/2004 16:22:00 - Revision: 1.2

  • Microsoft Access 2000 Standard Edition
  • kbhowto kbinfo KB209839
Feedback