<

Computing row-wise aggregations in Access

Published on
26,058 Points
9,458 Views
21 Endorsements
Last Modified:
Awarded
Editor's Choice
Community Pick
By Patrick Matthews (matthewspatrick)

1. Introduction


A very common database task is to compute aggregate statistics (such as Count, Sum, Min, Max, etc.) for some column of data.  For example, the user may need to determine the largest value associated with a particular customer as in the data below:

CustID   SaleAmt
----------------
1        15
1        6
1        92
2        42
3        6
3        17
3        84
3        26

Open in new window


In this case, the SQL is very simple:

SELECT CustID, Max(SaleAmt) AS MaxSale
FROM SomeTable
GROUP BY CustID
 
returns:
CustID   SaleAmt
------------------------------------
1        92
2        42
3        84

Open in new window


This is an example of a column-wise aggregation.

From time to time, Access users may find it necessary to compute row-wise aggregate statistics.  This can usually be avoided by building a normalized database schema.  However, at times, the user may be constrained to a denormalized design: the data may be coming from a data warehouse; there may be some performance reason for denormalizing the data; or the Access user may be stuck with another developer's design, and that design cannot be changed.

For example, suppose the data set is similar to the following:

CustID  H01  H02  H03  ...  H24
-------------------------------
1       22   44   66   ...  81
2       7    38   55   ...  99
3       49   12   38   ...  107
4       100  83   9    ...  1

Open in new window


If you have to determine an aggregate statistic based on the values in H01, H02, etc for each row, there are some techniques available using native Access functionality, but pursuing them can result in difficult and unreadable SQL, if it can be done at all.

Whatever the reason, this article offers two user defined functions, RowStats and RowStatsFieldList, that will enable Access users to compute the usual aggregate statistics on a row-wise basis.  The article includes the source code for this functions, as well as a sample Access database.

2. Row-Wise Aggregates Using Native Functions


Like other database platforms, Microsoft Access offers several aggregate functions, which compute statistics such as Count, Sum, Min, Max, etc. for all values within a column.  These aggregations may be applied to all values in a column, either for an entire table, or according to the distinct groupings produced by a GROUP BY clause.

However, Access does not offer any native functions that perform row-wise aggregations.  Part of this stems from good database practices: if a database developer has need of a row-wise aggregate function, this may indicate a denormalized data model.  If this is the case, developers should first consider whether or not the data model can (or should) be normalized.

In cases where a true row-wise aggregate is needed, if the number of items being considered is relatively small, then one can use the Switch() function, or nested IIf() expressions.  As an example, consider this case, based on an EE question (Access Highest Date Query):

In the following table, select the latest date in each row:

tblDates
-------------------------------------------
ID (AutoNumber, PK)
Date1 (Date)
Date2 (Date)
Date3 (Date)
Date4 (Date)

Here is one approach, using the Switch() function:

SELECT ID, Date1, Date2, Date3, Date4, 
    Switch(Date1 >= Date2 And Date1 >= Date3 And Date1 >= Date4, Date1, 
        Date2 >= Date1 And Date2 >= Date3 And Date2 >= Date4, Date2, 
        Date3 >= Date1 And Date3 >= Date2 And Date3 >= Date4, Date3, 
        True, Date4) AS MaxDate
FROM tblDates
ORDER BY ID

Open in new window


You could also use IIf():

SELECT ID, Date1, Date2, Date3, Date4,
    IIf(Date1 >= Date2 And Date1 >= Date3 And Date1 >= Date4, Date1,
        IIf(Date2 >= Date1 And Date2 >= Date3 And Date2 >= Date4, Date2,
        IIf(Date3 >= Date1 And Date3 >= Date2 And Date3 >= Date4, Date3,
            Date4))) AS MaxDate
FROM tblDates
ORDER BY ID

Open in new window


A third approach uses the UNION operator to transform the row-wise data into a true column, and thus allow you to use the regular aggregate functions:

SELECT z.ID, Max(z.TheDate) AS MaxDate
FROM
    (SELECT ID, Date1 AS TheDate FROM tblDates UNION ALL
    SELECT ID, Date2 AS TheDate FROM tblDates UNION ALL
    SELECT ID, Date3 AS TheDate FROM tblDates UNION ALL
    SELECT ID, Date4 AS TheDate FROM tblDates) AS z
GROUP BY z.ID
ORDER BY z.ID

Open in new window


All of these are manageable, albeit sometimes difficult to read and understand.  However, if you must consider more than a handful of items, such queries will quickly grow in complexity to point where Access cannot evaluate the expression, nor can a human operator understand it.

Further, some aggregate calculation, such as standard deviation and variance, may be simply impossible using solely native functions.  (For example, you could calculate standard deviation using the UNION approach, but not by using a straight SELECT combined with native functions.)

3. RowStats() user defined function


As an alternative, I wrote the user defined function RowStats() to compute row-wise aggregate analogues for the column-wise aggregates built into Access:
Count
Sum
Avg
Min
Max
StDev (sample standard deviation)
StDevP (population standard deviation)
Var (sample variance)
VarP (population variance)

Here is the source code for RowStats:

Function RowStats(Stat As String, ParamArray Vars())
    
    ' Function by Patrick Matthews
    
    ' This code may be used and distributed freely, so long as you attribute authorship, and indicate
    ' what URL you found the code at
    
    ' This Access UDF calculates various stats for the values passed into the ParamArray.  It
    ' was originally designed for passing several values from a particular row set for
    ' evaluation; since the values come from the same row, the usual aggregate functions would
    ' not be appropriate.
    
    ' The Stat argument (NOT case sensitive) determines what statistic is calculated:
    ' "count":          count of non-null values
    ' "min", "max":     minimum or maximum
    ' "sum", "avg":     sum or average, excluding strings
    ' "var", "stdev":   sample variance or standard deviation (excluding strings)
    ' "varp", "stdevp": population variance or standard deviation (excluding strings)
    
    ' You may pass any value in the ParamArray.  Strings, nulls, and dates are ignored for the
    ' sum, avg, stdev, stdevp, var, and varp calculations.  (Thus, you could use this function to
    ' add several columns together without wrapping each column with Nz() to handle nulls...)
    
    ' If run from VBA, this function appears to accept any number of values in the Vars argument;
    ' I successfully tested passing several hundred values.  If run from the Access query editor,
    ' the limit appears to be about 28 values.  If you need to process more than 28 values, you
    ' should use the related function RowStatsFieldList.  (You can overcome this limit by embedding
    ' RowStats expressions, but this will produce potentially erroneous results for the Avg,
    ' StDev[P], or Var[P] stats
    
    ' Please note that you can pass arrays as elements of the Vars ParamArray; indeed, the
    ' RowStatsFieldList function does that
    
    ' As with the regular aggregate functions, if all of the parameters are null, then the
    ' return value is null, except on count, where the return would be zero
    
    Dim Numerator As Double
    Dim Denominator As Double
    Dim Counter As Long, Counter2 As Long
    Dim Result As Variant
    Dim Mean As Double
    
    ' Force to upper case to make sure string comparisons are always performed as expected;
    ' Access uses Option Compare Database by default, but other VBA/VB6 uses binary default
    
    Stat = UCase(Stat)
    
    Select Case Stat
    
        ' In each Case below, loop through the elements of the Vars ParamArray.  If the element
        ' is itself an array, then loop through its elements
        
        Case "COUNT"
        
            ' Increment the result for each non-null value in the array
            
            Result = CLng(0)
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If Not IsNull(Vars(Counter)) Then Result = Result + 1
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If Not IsNull(Vars(Counter)(Counter2)) Then Result = Result + 1
                    Next
                End If
            Next
            
        Case "MIN"
        
            ' Initialize the result to Null, then check all non-Null values in turn to see
            ' if it is less.
            
            Result = Null
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                        Result = Vars(Counter)
                    ElseIf Vars(Counter) < Result Then
                        Result = Vars(Counter)
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
                            Result = Vars(Counter)(Counter2)
                        ElseIf Vars(Counter)(Counter2) < Result Then
                            Result = Vars(Counter)(Counter2)
                        End If
                    Next
                End If
            Next
                      
        Case "MAX"
            
            ' Initialize the result to Null, then check all non-Null values in turn to see
            ' if it is greater.
            
            Result = Null
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNull(Result) And Not IsNull(Vars(Counter)) Then
                        Result = Vars(Counter)
                    ElseIf Vars(Counter) > Result Then
                        Result = Vars(Counter)
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNull(Result) And Not IsNull(Vars(Counter)(Counter2)) Then
                            Result = Vars(Counter)(Counter2)
                        ElseIf Vars(Counter)(Counter2) > Result Then
                            Result = Vars(Counter)(Counter2)
                        End If
                    Next
                End If
            Next
            
        Case "AVG", "SUM"
        
            ' Check each value in turn.  If it is numeric, then increment numerator and denominator.
            ' Divide numerator by denominator to get an average, or by 1 to get the sum.  Any Date
            ' values are coerced into Double
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNumeric(Vars(Counter)) Then
                        Numerator = Numerator + Vars(Counter)
                        Denominator = Denominator + 1
                    ElseIf IsDate(Vars(Counter)) Then
                        Numerator = Numerator + CDbl(Vars(Counter))
                        Denominator = Denominator + 1
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNumeric(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + Vars(Counter)(Counter2)
                            Denominator = Denominator + 1
                        ElseIf IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
                            Denominator = Denominator + 1
                        End If
                    Next
                End If
            Next
            
            If Denominator > 0 Then
                Result = Numerator / IIf(Stat = "AVG", Denominator, 1)
            Else
                Result = Null
            End If
            
        Case "STDEV", "STDEVP", "VAR", "VARP"
        
            ' Take one pass through the set to determine the average, and then determine the
            ' sum of squared deviances from the mean.  Divide by number of elements in the
            ' array for population or (elements - 1) for sample.  If standard deviation,
            ' take square root.  Any Date values are coerced into Double
            
            ' This pass generates the numerator and denominator needed for the average
            
            For Counter = LBound(Vars) To UBound(Vars)
                If Not IsArray(Vars(Counter)) Then
                    If IsNumeric(Vars(Counter)) Then
                        Numerator = Numerator + Vars(Counter)
                        Denominator = Denominator + 1
                    ElseIf IsDate(Vars(Counter)) Then
                        Numerator = Numerator + CDbl(Vars(Counter))
                        Denominator = Denominator + 1
                    End If
                Else
                    For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                        If IsNumeric(Vars(Counter)(Counter2)) Or IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + Vars(Counter)(Counter2)
                            Denominator = Denominator + 1
                        ElseIf IsDate(Vars(Counter)(Counter2)) Then
                            Numerator = Numerator + CDbl(Vars(Counter)(Counter2))
                            Denominator = Denominator + 1
                        End If
                    Next
                End If
            Next
            
            ' Make sure there are enough numeric elements to avoid a division by zero error.  If not,
            ' return Null
            
            If (Stat Like "*P" And Denominator > 0) Or (Not Stat Like "*P" And Denominator > 1) Then
                
                Mean = Numerator / Denominator
                
                ' This pass sums the squares of the differences between each data point and the mean
                
                For Counter = LBound(Vars) To UBound(Vars)
                    If Not IsArray(Vars(0)) Then
                        If IsNumeric(Vars(Counter)) Then
                            Result = Result + (Vars(Counter) - Mean) ^ 2
                        ElseIf IsDate(Vars(Counter)) Then
                            Result = Result + (CDbl(Vars(Counter)) - Mean) ^ 2
                        End If
                    Else
                        For Counter2 = LBound(Vars(Counter)) To UBound(Vars(Counter))
                            If IsNumeric(Vars(Counter)(Counter2)) Then
                                Result = Result + (Vars(Counter)(Counter2) - Mean) ^ 2
                            ElseIf IsDate(Vars(Counter)(Counter2)) Then
                                Result = Result + (CDbl(Vars(Counter)(Counter2)) - Mean) ^ 2
                            End If
                        Next
                    End If
                Next
                
                ' Divide by N for population, and N-1 for sample
                
                If Stat Like "*P" Then
                    Result = Result / Denominator
                Else
                    Result = Result / (Denominator - 1)
                End If
            
                ' Take square root if standard deviation
                
                If Stat Like "S*" Then Result = Result ^ 0.5
            
            Else
                Result = Null
            End If
            
        Case Else
            
            ' If Stat is none of the above, then return Null -- invalid Stat
            
            Result = Null
    End Select
    
    ' Set return value
    
    If Not IsNull(Result) Then RowStats = Result Else RowStats = Null
    
End Function

Open in new window


RowStats takes two arguments:
Stat is a string indicating which statistic will be computed.  Allowed values are Count, Sum, Avg, Min, Max, StDev, StDevP, Var, and VarP.  Not case-sensitive
Vars is an array of one or more elements to be considered in computing the desired statistic

Please note the following:
For Count, RowStats returns the count of non-Null items in Vars.  If all the values are Null, then RowStats will return zero
For all Stat values except Count, Null values are ignored, and if all values are Null, RowStats returns Null
For Sum, Avg, StDev, StDevP, Var, and VarP, strings are also treated as if they were Null
For StDev and Var, there must be at least two non-Null values, or RowStats will return Null to avoid a division by zero error

As an example of using RowStats in a query, below is the SQL statement for the query "qryAllStats" used in the attached sample file:

SELECT [Date], 
    RowStats("Count",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Count,
    RowStats("Sum",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Sum,
    RowStats("Avg",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Avg,
    RowStats("Min",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Min,
    RowStats("Max",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Max,
    RowStats("StDev",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _StDev,
    RowStats("Var",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _Var,
    RowStats("StDevP",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _StDevP,
    RowStats("VarP",[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]) AS _VarP
FROM tblData
ORDER BY [Date];

Open in new window


In testing, I have been able to pass several hundred elements in the Vars array when calling this function from VBA.  In Access's query editor, the greatest number of elements appears to be about 28 (after which point Access indicates that the expression is too complex to evaluate), although this can be circumvented by embedding RowStats expressions in the Vars array.  For example, this expression manages to bring in 80 elements:

SELECT RowStats("sum",1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0,
    RowStats("sum",1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0),
    RowStats("sum",1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0),
    RowStats("sum",1,2,3,4,5,6,7,8,9,0,1,2,3,4,5,6,7,8,9,0)) as foo;

Open in new window


This work-around is valid for the Count, Sum, Min, and Max aggregations.   It is not valid for the Avg, StDev, StDevP, Var, and VarP aggregations!

I wrote the RowStatsFieldList function, described in the next section, specifically to address this Access limitation.  If you must pass more than 28 elements for a row-wise aggregate from an Access query, I recommend you use the RowStatsFieldList function.

4. RowStatsFieldList user defined function


The RowStatsFieldList function computes the same statistics as the RowStats function.  However, since it takes the list of columns to be evaluated as a comma-delimited string, it avoids the limit of 28 elements when run from the Access query editor.

Here is the source code for RowStatsFieldList:

Function RowStatsFieldList(Stat As String, TblName As String, Conditions As String, FieldList As String, _
    Optional ResetParms As Boolean = False)
    
    ' Function by Patrick Matthews
    
    ' Requires reference to DAO library
    
    ' This code may be used and distributed freely, so long as you attribute authorship, and indicate
    ' what URL you found the code at
    
    ' This Access UDF calculates various stats for the values passed into the FieldList.  This
    ' function relies on the RowStats function to work.
    
    ' When run in the Access query editor, the RowStats function appears to have a limit of about
    ' 28 items, after which Jet deems the expression too complex to be evaluated.  This function
    ' was specifically designed to overcome that limit
    
    ' The Stat argument (NOT case sensitive) determines what statistic is calculated:
    ' "count":          count of non-null values
    ' "min", "max":     minimum or maximum
    ' "sum", "avg":     sum or average, excluding dates or strings
    ' "var", "stdev":   sample variance or standard deviation (excluding strings)
    ' "varp", "stdevp": population variance or standard deviation (excluding strings)
    
    ' The TblName argument specifies the table or query from which we draw the raw data.  If the
    ' table name contains anything other than letters, digits, or underscores, if the table name
    ' does not start with a letter, or if the table name is a reserved word, then you must
    ' enclose the table name in square brackets.  Thus "TableName", "[TableName]", and "[Table Name]"
    ' are all valid, while "Table Name" is not
    
    ' The Conditions argument specifies the conditions that uniquely identify a row from the
    ' source table or query.  You may use more than one column to specify your criteria.  Enclose
    ' all column names in square brackets.  Use # as a date qualifier, and ' as a text qualifier.
    ' Examples:
    ' "[NumericID] = " & NumericID
    ' "[TextJobCode] = '" & TextJobCode & "'"
    ' "[StartDate] = #" & StartDate & "#"
    ' "[TextJobCode] = '" & TextJobCode & "' And [StartDate] = #" & StartDate & "#"
        
    ' The FieldList argument specifies the columns to be evaluated for the statistics, in a
    ' comma-delimited list.  Strings, nulls, and dates are ignored for the sum, avg, stdev, stdevp,
    ' var, and varp calculations.  (Thus, you could use this function to add several columns
    ' together without wrapping each column with Nz() to handle nulls...)  If a column name
    ' contains anything other than letters, digits, or underscores, if it does not start with a
    ' letter, or if it is a reserved word, then you must enclose the name in square brackets.  Thus
    ' "Col1,Col2,Col3", "[Col1],[Col2],[Col3]", and "[Col 1],[Col 2],[Col3]" are all valid, while
    ' "Col 1,Col 2,Col 3" is not
    
    ' The ResetParms argument indicates whether or not the function should automatically rebuild
    ' its value recordset and array on every function call.  True indicates that they will always
    ' be rebuilt, and False indicates that the function should reuse the recordset and array if
    ' possible.  The function also keeps a timestamp of each call in the Runtime variable; if the
    ' seconds difference between that last Runtime and the current system datetime is greater than
    ' the MaxDelay constant, the function will always rebuild the recordset and array, as a defense
    ' against the underlying data in the source table/query having changed since the last function
    ' call
    
    ' As with the regular aggregate functions, if all of the parameters are null, then the
    ' return value is null, except on count, where the return would be zero
    
    ' Use of static variables for the recordset and array should boost performance when used
    ' on large data sets
    
    Static rs As DAO.Recordset
    Static arr()
    Static SQL As String
    Static Runtime As Date
    
    Dim Counter As Long
    Dim TestSQL As String
    Dim Reuse As Boolean
    
    Const MaxDelay As Long = 10     ' see note for ResetParms argument above
    
    ' Build a SQL statement to return the desired record from the source table.  We are relying
    ' on the inputs to supply bracketing, text qualifiers, and/or date qualifiers where needed
    
    TestSQL = "SELECT " & FieldList & _
        " FROM " & TblName & _
        " WHERE " & Conditions
        
    ' If enough time has passed since the last function call, automatically destroy the recordset
    
    If ResetParms Or DateDiff("s", Runtime, Now) > MaxDelay Then Set rs = Nothing
    Runtime = Now
    
    ' Check to see if recordset has changed
    
    If TestSQL <> SQL Or rs Is Nothing Then
        Reuse = False
        SQL = TestSQL
    Else
        Reuse = True
    End If
    
    If Not Reuse Then Set rs = CurrentDb.OpenRecordset(SQL)

    ' If recordset returns anything, loop through the first (should be only!) record, and populate
    ' an array with the column values
    
    If Not rs.EOF Then
        
        ' Rebuild the array if the recordset has changed
        
        If Not Reuse Then
            
            ' Redimension the array to fit the number of columns
            
            ReDim arr(0 To rs.Fields.Count - 1)
    
            ' Loop through columns and assign values in array
            
            For Counter = LBound(arr) To UBound(arr)
                arr(Counter) = rs.Fields(Counter)
            Next
        End If
        
        ' Make function call to RowStats to do actual calculation
        
        RowStatsFieldList = RowStats(Stat, arr)
    Else
        
        ' If recordset is empty, force function to return Null unless the Stat is Count
        
        If UCase(Stat) <> "COUNT" Then RowStatsFieldList = Null Else RowStatsFieldList = 0
    End If
    
End Function

Open in new window


The RowStatsFieldList function takes the following arguments:
Stat is a string indicating which statistic will be computed.  Allowed values are Count, Sum, Avg, Min, Max, StDev, StDevP, Var, and VarP.  Not case-sensitive
TblName specifies the table or query from which we draw the raw data
Conditions specifies a set of criteria that uniquely identifies a row from the source table or query.  The argument text is basically the contents of a SQL WHERE clause, omitting the word WHERE.  The Conditions argument may specify criteria for more than one column, in which case use And to combine the criteria expressions.  Column names should be wrapped in square brackets.  Use # as a Date qualifier, and ' as a text qualifier.
FieldList is a comma-delimited string listing the columns to be evaluated for the row-wise statistics
ResetParms is an optional argument indicating whether or not RowStatsFieldList should attempt to reuse the data from the last function call.  If False or omitted, the function will attempt to reuse the data unless the FieldList and/or Conditions have changed; if True, then the function will not try to reuse the data

Please note the following:
The RowStatsFieldList function depends on the RowStats function to perform the actual row-wise calculations, so if you plan to use RowStatsFieldList in your project, you must also include the RowStats function
The RowStatsFieldList function requires a reference to the Microsoft Data Access Objects (DAO) library
For Count, RowStatsFieldList returns the count of non-Null items in Vars.  If all the values are Null, then RowStatsFieldList will return zero
For all Stat values except Count, Null values are ignored, and if all values are Null, RowStatsFieldList returns Null
For Sum, Avg, StDev, StDevP, Var, and VarP, strings are also treated as if they were Null
For StDev and Var, there must be at least two non-Null values, or RowStats will return Null to avoid a division by zero error
For the TblName and FieldList arguments, you must enclose the database object names in square brackets if they contain anything other than letters, digits, or underscores; begin with anything other than a letter; or are Access reserved words.  Thus, for example, for the TblName argument, "TableName", "[TableName]", and "[Table Name]" are all valid, while "Table Name" is not.  For the FieldList argument, "Col1,Col2,Col3", "[Col1],[Col2],[Col3]", and "[Col 1],[Col 2],[Col3]" are all valid, while "Col 1,Col 2,Col 3" is not
If you are familiar with Access's domain aggregate functions (DSum, DCount, etc.), the Conditions argument for RowStatsFieldList behaves the same way as in the Criteria argument for the domain aggregates
Allowing RowStatsFieldList to reuse data from the last function call (ResetParms = False or omitted) may yield faster performance.  Regardless of the value for ResetParms, if the number of seconds since the last function call exceeds the MaxDelay constant (10 seconds in the code posted here, but you can change that if desired), then RowStatsFieldList will always discard the data from the last function call.  This is done to guard against potential changes in the source data since the last function call

As an example of using RowStatsFieldList in a query, below is the SQL statement for the query "qryAllStats_FieldList" used in the attached sample file:

SELECT [Date],
    RowStatsFieldList("Count","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Count,
    RowStatsFieldList("Sum","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Sum,
    RowStatsFieldList("Avg","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Avg,
    RowStatsFieldList("Min","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Min,
    RowStatsFieldList("Max","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Max,
    RowStatsFieldList("StDev","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _StDev,
    RowStatsFieldList("Var","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _Var,
    RowStatsFieldList("StDevP","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _StDevP,
    RowStatsFieldList("VarP","tblData","[Date] = #" & [Date] & "#","[H01],[H02],[H03],[H04],[H05],[H06],[H07],[H08],[H09],[H10],[H11],[H12],[H13],[H14],[H15],[H16],[H17],[H18],[H19],[H20],[H21],[H22],[H23],[H24]") AS _VarP
FROM tblData
ORDER BY [Date];

Open in new window


Please note also that, if desired, you may store the values for the Conditions and/or FieldList arguments in another table.  This may be especially useful for Conditions and/or FieldList combinations that you use frequently.  Thus, for example, you might have a table such as the following in your application:

tblFieldLists
----------------------------------------------------------
ID (AutoNumber, PK)
FieldListName (Text)
FieldListValue (Text; Memo if you need >256 characters)
 
ID  FieldListName  FieldListValue
1   Months         [Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
2   Days           [Sun],[Mon],[Tue],[Wed],[Thu],[Fri],[Sat]
3   Big Dates      [OrderDate],[ShipDate],[DelivDate],[InvoiceDate],[PaidDate]
etc.

Open in new window


You could then either join to that table, or use DLookup, to retrieve the FieldLists:

SELECT e.EmpLName, 
    RowStatsFieldList("Max", "tblMonthlySales", 
        "[ID]=" & e.ID, f.FieldListValue) AS MaxMonthlySales
FROM tblEmployees e, tblFieldLists l
WHERE l.FieldListName = "Months"
ORDER BY RowStatsFieldList("Max", "tblMonthlySales", 
    "[ID]=" & e.ID, f.FieldListValue) DESC

Open in new window


SELECT EmpLName, 
    RowStatsFieldList("Max", "tblMonthlySales", "[ID]=" & ID, 
        DLookup("FieldListValue", "tblFieldLists", "FieldListName = 'Months'") AS MaxMonthlySales
FROM tblEmployees
ORDER BY RowStatsFieldList("Max", "tblMonthlySales", "[ID]=" & ID, 
    DLookup("FieldListValue", "tblFieldLists", "FieldListName = 'Months'") DESC

Open in new window


While this table-based approach for storing FieldList values is not always necessary, in some cases you may have to: Access imposes overall limits on the number of characters in a SQL string (1024), and if your query expression is pushing this limit when you pass in the FieldList as a string, then you may have to use this table-based approach to get around the limit.

5. Implementing RowStats and/or RowStatsFieldList in Your Access Application


To implement the RowStats and RowStatsFieldList functions in your Access application, please do the following:
Go to the Visual Basic Editor
Add a new "regular" module to your project (not a class module!)
Paste the code for RowStats and, if desired, RowStatsFieldList into that module.  If you plan to use RowStatsFieldList, you must have both functions present
If you add RowStatsFieldList to your project, make sure that your project includes a reference to Microsoft Data Access Objects (DAO).  You can check this by selecting Tools|References from the VB Editor's main menu
Start using RowStats/RowStatsFieldList in your queries, forms, or elsewhere in your VBA project

6. Using RowStats and/or RowStatsFieldList as an alternative to Nz(Col1,0)+...+Nz(ColN,0)


RowStats also has an alternate use that some Access users may find handy: summing multiple columns in which some or all of the columns may be Null.

Consider a case in which you must sum Col1 through Col4, but some or all of the columns may be Null, and you want Null values ignored.  In such a case, you cannot simply add the columns together: if any of the values are Null, the result will be Null as well.

A typical approach to handling the Nulls would be to use the Nz function to substitute a zero value for any Nulls:

SELECT ID, Nz(Col1,0)+Nz(Col2,0)+Nz(Col3,0)+Nz(Col4,0) AS Total
FROM SomeTable

Open in new window


That is a well-established approach, but it does have one drawback: if all of the values are Null, the expression will return a zero.  If that is the desired result, that is perfect.  However, if you want to get a Null result if all of the inputs are Null, then the expression becomes very convoluted:

SELECT ID, IIf(
    (IIf(Col1 Is Null,0,1)+IIf(Col2 Is Null,0,1)+IIf(Col3 Is Null,0,1)+
        IIf(Col4 Is Null,0,1))>0,
    Nz(Col1,0)+Nz(Col2,0)+Nz(Col3,0)+Nz(Col4,0),
    Null) AS Total
FROM SomeTable

Open in new window


On the other hand, you can get this behavior from RowStats or RowStatsFieldList with a much cleaner expression:

SELECT ID, RowStats("Sum", Col1, Col2, Col3, Col4) AS Total
FROM SomeTable

Open in new window


SELECT ID, RowStatsFieldList("Sum", "SomeTable", "ID", ID, 
    "Col1,Col2,Col3,Col4") AS Total
FROM SomeTable

Open in new window


That will yield a sum ignoring Nulls, but still return a Null if all of the input columns are Null.

7. Sample File


The attached file, RowAggregates.mbd, includes a sample set that you may use to practice with the RowStats and RowStatsFieldList functions.  It has a single table with the following design:

tblDates
-------------------------------------------------------
Date (Date, PK)
H01 (Long)
H02 (Long)
H03 (Long)
...
H24 (Long)

The conceit is a table of observations taken once each hour over a period of 1,001 days.

RowAggregates.mdb


8. Acknowledgements


I would like to thank EE Member and Page Editor aikimark for his assistance with this article, and most especially for suggesting that I include an alternative approach that eventually became the RowStatsFieldList function.  The code and any mistakes are mine, but the inspiration and several very helpful pointers came from him.


=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
If you liked this article and want to see more from this author,  please click here.

If you found this article helpful, please click the Yes button near the:

      Was this article helpful?

label that is just below and to the right of this text.   Thanks!
=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
21
Comment
3 Comments
 
LVL 61

Expert Comment

by:Kevin Cross
Nice work, Patrick.
Voted yes above.
0
 
LVL 15

Expert Comment

by:Eric AKA Netminder
Where was this article about six years ago when I was writing innumerable update queries just to get a report to show four salespeople's commissions?

Outstanding work, Patrick -- and thank you.

ep
0
 
 

Administrative Comment

by:tigermatt
Editor's Choice Awarded. Thanks, Patrick!

tigermatt
Page Editor
0

Featured Post

Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

Join & Write a Comment

In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month