CustID SaleAmt
----------------
1 15
1 6
1 92
2 42
3 6
3 17
3 84
3 26
SELECT CustID, Max(SaleAmt) AS MaxSale
FROM SomeTable
GROUP BY CustID
returns:
CustID SaleAmt
------------------------------------
1 92
2 42
3 84
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
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
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
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
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
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];
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;
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
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];
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.
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
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
SELECT ID, Nz(Col1,0)+Nz(Col2,0)+Nz(Col3,0)+Nz(Col4,0) AS Total
FROM SomeTable
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
SELECT ID, RowStats("Sum", Col1, Col2, Col3, Col4) AS Total
FROM SomeTable
SELECT ID, RowStatsFieldList("Sum", "SomeTable", "ID", ID,
"Col1,Col2,Col3,Col4") AS Total
FROM SomeTable
Have a question about something in this article? You can receive help directly from the article author. Sign up for a free trial to get started.
Comments (2)
Commented:
Voted yes above.
Commented:
Outstanding work, Patrick -- and thank you.
ep