Median for all data, qryUDF_Median_All
SELECT DMedian("Value", "Sample") AS Median;
Returns:
Median
104
Median by Code, qryUDF_Median_ByCode
SELECT Sample.Code, DMedian("Value","Sample","[Code] = '" & Code & "'") AS Median
FROM Sample
GROUP BY Sample.Code;
Returns:
Code Median
A 100
B 110.5
C 101.5
Median by Section, qryUDF_Median_BySection
SELECT Sample.Section, DMedian("Value","Sample","[Section] = '" & Section & "'") AS Median
FROM Sample
GROUP BY Sample.Section;
Returns:
Section Median
Section 1 105.5
Section 2 103
Median by Section and Code, qryUDF_Median_BySectionAndCode
SELECT Sample.Section, Sample.Code, DMedian("Value","Sample","[Section] = '" &
[Section] & "' And [Code] = '" & [Code] & "'") AS Median
FROM Sample
GROUP BY Sample.Section, Sample.Code;
Returns:
Section Code Median
Section 1 A 100
Section 1 B 112
Section 1 C 100
Section 2 A 100.5
Section 2 B 107
Section 2 C 103
Mode for all data, SQL, qrySQL_Mode_All
SELECT TOP 1 Sample.Value
FROM Sample
GROUP BY Sample.Value
ORDER BY Count(Sample.Value) DESC;
Returns:
Value
80
99
Mode by Code, SQL, qrySQL_Mode_ByCode
SELECT t1.Code, t1.Value
FROM Sample AS t1
WHERE t1.Value In
(SELECT TOP 1 t2.Value
FROM Sample t2
WHERE t2.Code = t1.Code
GROUP BY t2.Value
ORDER BY Count(t2.Value) Desc)
GROUP BY t1.Code, t1.Value;
Returns:
Code Value
A 99
A 105
B 115
C 71
Mode by Section, SQL, qrySQL_Mode_BySection
SELECT t1.Section, t1.Value
FROM Sample AS t1
WHERE t1.Value In
(SELECT TOP 1 t2.Value
FROM Sample t2
WHERE t2.Section = t1.Section
GROUP BY t2.Value
ORDER BY Count(t2.Value) Desc)
GROUP BY t1.Section, t1.Value;
Returns:
Section Value
Section 1 80
Section 1 97
Section 1 99
Section 1 108
Section 2 104
Section 2 131
Section 2 140
Mode by Section and Code, SQL, qrySQL_Mode_BySectionAndCode
SELECT t1.Section, t1.Code, t1.Value
FROM Sample AS t1
WHERE t1.Value In
(SELECT TOP 1 t2.Value
FROM Sample t2
WHERE t2.Section = t1.Section And t2.Code = t1.Code
GROUP BY t2.Value
ORDER BY Count(t2.Value) Desc)
GROUP BY t1.Section, t1.Code, t1.Value;
Returns:
Section Code Value
Section 1 A 99
Section 1 B 95
Section 1 B 114
Section 1 B 115
Section 1 B 116
Section 1 C 81
Section 1 C 108
Section 2 A 105
Section 2 B 123
Section 2 B 131
Section 2 B 140
Section 2 C 104
Mode for all data, UDF, qryUDF_Mode_All
SELECT DMode("Value","Sample") AS ModeAll, DMode("Value","Sample","",-1) AS ModeFirst,
DMode("Value","Sample","",1) AS ModeLast;
Returns:
ModeAll ModeFirst ModeLast
80, 99 80 99
Mode by Code, UDF, qryUDF_Mode_ByCode
SELECT Sample.Code, DMode("Value","Sample","[Code] = '" & [Code] & "'") AS ModeAll,
DMode("Value","Sample","[Code] = '" & Code & "'",-1) AS ModeFirst,
DMode("Value","Sample","[Code] = '" & Code & "'",1) AS ModeLast
FROM Sample
GROUP BY Sample.Code;
Returns:
Code ModeAll ModeFirst ModeLast
A 105, 99 99 105
B 115 115 115
C 71 71 71
Mode by Section, UDF, qryUDF_Mode_BySection
SELECT Sample.Section, DMode("Value","Sample","[Section] = '" & [Section] & "'") AS ModeAll,
DMode("Value","Sample","[Section] = '" & [Section] & "'",-1) AS ModeFirst,
DMode("Value","Sample","[Section] = '" & [Section] & "'",1) AS ModeLast
FROM Sample
GROUP BY Sample.Section;
Returns:
Section ModeAll ModeFirst ModeLast
Section 1 80, 97, 99, 108 80 108
Section 2 104, 131, 140 104 140
Mode by Section and Code, UDF, qryUDF_Mode_BySectionAndCode
SELECT Sample.Section, Sample.Code,
DMode("Value","Sample","[Section] = '" & [Section] & "' And [Code] = '" & [Code] & "'") AS ModeAll,
DMode("Value","Sample","[Section] = '" & [Section] & "' And [Code] = '" & [Code] & "'",-1) AS ModeFirst,
DMode("Value","Sample","[Section] = '" & [Section] & "' And [Code] = '" & [Code] & "'",1) AS ModeLast
FROM Sample
GROUP BY Sample.Section, Sample.Code;
Returns:
Section Code ModeAll ModeFirst ModeLast
Section 1 A 99 99 99
Section 1 B 95, 114, 115, 116 95 116
Section 1 C 81, 108 81 108
Section 2 A 105 105 105
Section 2 B 123, 140, 131 123 140
Section 2 C 104 104 104
Skewness and kurtosis for all data, SQL, qrySQL_SkewKurt_All
SELECT (Sum((t2.Value-z.TheAvg)^3)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^1.5 AS Skewness,
(Sum((t2.Value-z.TheAvg)^4)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^2 AS Kurtosis
FROM Sample AS t2,
[SELECT Avg(t1.Value) AS TheAvg
FROM Sample t1]. AS z
HAVING Count(t2.Value) > 0;
Returns:
Skewness Kurtosis
0.0672 3.0465
Skewness and kurtosis by Code, SQL, qrySQL_SkewKurt_ByCode
SELECT t2.Code,
(Sum((t2.Value-z.TheAvg)^3)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^1.5 AS Skewness,
(Sum((t2.Value-z.TheAvg)^4)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^2 AS Kurtosis
FROM Sample AS t2 INNER JOIN
[SELECT t1.Code, Avg(t1.Value) AS TheAvg
FROM Sample t1 GROUP BY t1.Code]. AS z ON t2.Code = z.Code
GROUP BY t2.Code
HAVING Count(t2.Value) > 0;
Returns:
Code Skewness Kurtosis
A -0.0289 2.7148
B 0.1870 3.3675
C -0.0492 2.7303
Skewness and kurtosis by Section, SQL, qrySQL_SkewKurt_BySection
SELECT t2.Section,
(Sum((t2.Value-z.TheAvg)^3)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^1.5 AS Skewness,
(Sum((t2.Value-z.TheAvg)^4)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^2 AS Kurtosis
FROM Sample AS t2 INNER JOIN
[SELECT t1.Section, Avg(t1.Value) AS TheAvg
FROM Sample t1 GROUP BY t1.Section]. AS z ON t2.Section = z.Section
GROUP BY t2.Section
HAVING Count(t2.Value) > 0;
Returns:
Section Skewness Kurtosis
Section 1 0.0605 3.0673
Section 2 0.0670 2.9209
Skewness and kurtosis by Section and Code, SQL, qrySQL_SkewKurt_BySectionAndCode
SELECT t2.Section, t2.Code,
(Sum((t2.Value-z.TheAvg)^3)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^1.5 AS Skewness,
(Sum((t2.Value-z.TheAvg)^4)/Count(t2.Value))/(Sum((t2.Value-z.TheAvg)^2)/Count(t2.Value))^2 AS Kurtosis
FROM Sample AS t2 INNER JOIN
[SELECT t1.Section, t1.Code, Avg(t1.Value) AS TheAvg
FROM Sample t1 GROUP BY t1.Section, t1.Code]. AS z ON (t2.Section = z.Section) AND (t2.Code = z.Code)
GROUP BY t2.Section, t2.Code
HAVING Count(t2.Value) > 0;
Returns:
Section Code Skewness Kurtosis
Section 1 A -0.0673 2.6510
Section 1 B 0.1870 3.5337
Section 1 C 0.0235 2.5559
Section 2 A 0.0723 2.2061
Section 2 B 0.1778 2.8530
Section 2 C -0.1484 2.9293
Skewness and kurtosis for all data, UDF, qryUDF_SkewKurt_All
SELECT DSkewness("Value", "Sample") AS Skewness, DKurtosis("Value", "Sample") AS Kurtosis;
Returns:
Skewness Kurtosis
0.0672 3.0465
Skewness and kurtosis by Code, UDF, qryUDF_SkewKurt_ByCode
SELECT Sample.Code, DSkewness("Value","Sample","[Code] = '" & Code & "'") AS Skewness,
DKurtosis("Value","Sample","[Code] = '" & [Code] & "'") AS Kurtosiss
FROM Sample
GROUP BY Sample.Code;
Returns:
Code Skewness Kurtosis
A -0.0289 2.7148
B 0.1870 3.3675
C -0.0492 2.7303
Skewness and kurtosis by Section, UDF, qryUDF_SkewKurt_BySection
SELECT Sample.Section, DSkewness("Value","Sample","[Section] = '" & [Section] & "'") AS Skewness,
DKurtosis("Value","Sample","[Section] = '" & [Section] & "'") AS Kurtosiss
FROM Sample
GROUP BY Sample.Section;
Returns:
Section Skewness Kurtosis
Section 1 0.0605 3.0673
Section 2 0.0670 2.9209
Skewness and kurtosis by Section and Code, UDF, qryUDF_SkewKurt_BySectionAndCode
SELECT Sample.Section, Sample.Code,
DSkewness("Value","Sample","[Section] = '" & [Section] & "' And [Code] = '" & [Code] & "'") AS Skewness,
DKurtosis("Value","Sample","[Section] = '" & [Section] & "' And [Code] = '" & [Code] & "'") AS Kurtosiss
FROM Sample
GROUP BY Sample.Section, Sample.Code;
Returns:
Section Code Skewness Kurtosis
Section 1 A -0.0673 2.6510
Section 1 B 0.1870 3.5337
Section 1 C 0.0235 2.5559
Section 2 A 0.0723 2.2061
Section 2 B 0.1778 2.8530
Section 2 C -0.1484 2.9293
Function DMedian(Column As String, Tbl As String, Optional Criteria As String = "")
' Function by Patrick G. Matthews
' Feel free to use and distribute this code, so long as you credit authorship and indicate the
' URL where you found it
' This function calculates the median for a specified column from a specified table/query,
' applying filtering as defined in the Criteria argument. It is intended for use in Microsoft
' Access. This function requires a reference to the Microsoft DAO library.
' Records with null values in the specified column are ignored
' This function is labeled according to the domain aggregate function naming convention as it
' behaves similarly to the other domain aggregates
' Column is the column for which you want to determine the median
' Tbl is the source table or query for the data
' Criteria defines any filtering criteria you wish to apply to the data set. Be sure to enclose
' text items in single quotes and date values in the # date qualifiers
Dim rs As DAO.Recordset
Dim SQL As String
Dim Count As Long
Dim ValueNum As Long
Dim Counter As Long
Static Alias As String
On Error GoTo ErrHandler
' The subquery needs an alias. By creating a random string of 20 characters, it is virtually impossible
' that the alias will duplicate a table name already used in the function call. Since there is no need
' to keep recreating this random string, it is a static variable and thus retains its state between calls
If Alias = "" Then
Randomize
' Build 20-character string using A-Z
For Counter = 1 To 20
Alias = Alias & Chr(65 + Int(Rnd * 26))
Next
End If
' Initialize return value to null
DMedian = Null
' Determine how many records exist for the set we calculate a median for
SQL = "SELECT Count(1) AS TheCount " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Count = rs!TheCount
' ValueNum helps us determine where the "middle" of the set is
ValueNum = Int(Count / 2) + 1
rs.Close
' For an odd number of records, grabbing the median is simple using a subquery. The subquery
' returns the top N records, and then the main query re-sorts that set in descending order
' and returns the top-most record
If Count > 0 And (Count Mod 2) = 1 Then
SQL = "SELECT TOP 1 " & Alias & "." & Column & " AS Median " & _
"FROM " & _
"(SELECT TOP " & ValueNum & " " & Column & " " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null " & _
"ORDER BY " & Column & " Asc) AS " & Alias & " " & _
"ORDER BY " & Alias & "." & Column & " Desc"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
DMedian = rs!median
rs.Close
' For an even number of records, we have a problem. We need to average the two middle values,
' but the subquery approach will not work because in a "SELECT TOP N" query Access returns
' ties. Instead, initialize DMedian to 0, and step through the recordset. Add together the
' values from the two middle records, and then divide by two for the answer
ElseIf Count > 0 Then
SQL = "SELECT TOP " & ValueNum & " " & Column & " " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null " & _
"ORDER BY " & Column & " Asc"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
Counter = 1
DMedian = 0
Do Until Counter > ValueNum Or rs.EOF
If Counter = (ValueNum - 1) Or Counter = ValueNum Then
DMedian = DMedian + rs.Fields(0).Value
End If
rs.MoveNext
Counter = Counter + 1
Loop
rs.Close
DMedian = DMedian / 2
End If
GoTo Cleanup
ErrHandler:
DMedian = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
Function DMode(Column As String, Tbl As String, Optional Criteria As String = "", _
Optional ReturnMode As Long = 0, Optional Delimiter As String = ", ")
' Function by Patrick G. Matthews
' Feel free to use and distribute this code, so long as you credit authorship and indicate the
' URL where you found it
' This function calculates the mode for a specified column from a specified table/query,
' applying filtering as defined in the Criteria argument. It is intended for use in Microsoft
' Access. This function requires a reference to the Microsoft DAO library.
' Records with null values in the specified column are ignored
' This function is labeled according to the domain aggregate function naming convention as it
' behaves similarly to the other domain aggregates
' Column is the column for which you want to determine the median
' Tbl is the source table or query for the data
' Criteria defines any filtering criteria you wish to apply to the data set. Be sure to enclose
' text items in single quotes and date values in the # date qualifiers
' ReturnMode indicates which modal value is returned.
' If 0 or omitted, all modal values are returned in a delimited list
' If negative, returns the modal value returned first in an ascending sort
' If positive, returns the modal value returned first in a descending sort
' Delimiter is the delimiter used in making a list of modal values. Default is ,+space
Dim rs As DAO.Recordset
Dim SQL As String
On Error GoTo ErrHandler
' Build up the SQL statement, then open recordset
SQL = "SELECT TOP 1 " & Column & " " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null " & _
"GROUP BY " & Column & " " & _
"ORDER BY Count(1) Desc" & Switch(ReturnMode = 0, "", ReturnMode < 0, ", " & Column & " Asc", _
ReturnMode > 0, ", " & Column & " Desc")
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
' If ReturnMode = 0, function returns delimited list of all modal values
If ReturnMode = 0 Then
DMode = ""
Do Until rs.EOF
DMode = DMode & Delimiter & rs.Fields(0).Value
rs.MoveNext
Loop
DMode = Mid(DMode, Len(Delimiter) + 1)
' If ReturnMode is negative, function returns the modal value that sorts first ascending, and if
' ReturnMode is positive, function returns the modal value that sorts first descending
Else
DMode = rs.Fields(0).Value
End If
GoTo Cleanup
ErrHandler:
DMode = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
Function DSkewness(Column As String, Tbl As String, Optional Criteria As String = "")
' Function by Patrick G. Matthews
' Feel free to use and distribute this code, so long as you credit authorship and indicate the
' URL where you found it
' This function calculates the sample skewness for a specified column from a specified
' table/query, applying filtering as defined in the Criteria argument. It is intended for use
' in Microsoft Access. This function requires a reference to the Microsoft DAO library.
' Records with null values in the specified column are ignored
' This function is labeled according to the domain aggregate function naming convention as it
' behaves similarly to the other domain aggregates
' Column is the column for which you want to determine the median
' Tbl is the source table or query for the data
' Criteria defines any filtering criteria you wish to apply to the data set. Be sure to enclose
' text items in single quotes and date values in the # date qualifiers
Dim rs As DAO.Recordset
Dim SQL As String
Dim Counter As Long
Static Alias As String
On Error GoTo ErrHandler
' The subquery needs an alias. By creating a random string of 20 characters, it is virtually impossible
' that the alias will duplicate a table name already used in the function call. Since there is no need
' to keep recreating this random string, it is a static variable and thus retains its state between calls
If Alias = "" Then
Randomize
' Build 20-character string using A-Z
For Counter = 1 To 20
Alias = Alias & Chr(65 + Int(Rnd * 26))
Next
End If
' Build up SQL statement
SQL = "SELECT (Sum((" & Column & " - " & Alias & ".TheAvg) ^ 3) / Count(" & Column & ")) / " & _
"(Sum((" & Column & " - " & Alias & ".TheAvg) ^ 2) / Count(" & Column & ")) ^ 1.5 AS Skew " & _
"FROM " & Tbl & ", " & _
"(SELECT Avg(" & Column & ") AS TheAvg, StDev(" & Column & ") AS TheStDev " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null) AS " & Alias & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null " & _
"HAVING Count(" & Column & ") > 2 and " & Alias & ".TheStDev <> 0"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
If Not rs.EOF Then
DSkewness = rs!Skew
Else
DSkewness = Null
End If
rs.Close
GoTo Cleanup
ErrHandler:
DSkewness = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
Function DKurtosis(Column As String, Tbl As String, Optional Criteria As String = "")
' Function by Patrick G. Matthews
' Feel free to use and distribute this code, so long as you credit authorship and indicate the
' URL where you found it
' This function calculates the sample kurtosis for a specified column from a specified
' table/query, applying filtering as defined in the Criteria argument. It is intended for use
' in Microsoft Access. This function requires a reference to the Microsoft DAO library.
' Records with null values in the specified column are ignored
' This function is labeled according to the domain aggregate function naming convention as it
' behaves similarly to the other domain aggregates
' Column is the column for which you want to determine the median
' Tbl is the source table or query for the data
' Criteria defines any filtering criteria you wish to apply to the data set. Be sure to enclose
' text items in single quotes and date values in the # date qualifiers
Dim rs As DAO.Recordset
Dim SQL As String
Dim Counter As Long
Static Alias As String
On Error GoTo ErrHandler
' The subquery needs an alias. By creating a random string of 20 characters, it is virtually impossible
' that the alias will duplicate a table name already used in the function call. Since there is no need
' to keep recreating this random string, it is a static variable and thus retains its state between calls
If Alias = "" Then
Randomize
' Build 20-character string using A-Z
For Counter = 1 To 20
Alias = Alias & Chr(65 + Int(Rnd * 26))
Next
End If
' Build up SQL statement
SQL = "SELECT (Sum((" & Column & " - " & Alias & ".TheAvg) ^ 4) / Count(" & Column & ")) / " & _
"(Sum((" & Column & " - " & Alias & ".TheAvg) ^ 2) / Count(" & Column & ")) ^ 2 AS Skew " & _
"FROM " & Tbl & ", " & _
"(SELECT Avg(" & Column & ") AS TheAvg, StDev(" & Column & ") AS TheStDev " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null) AS " & Alias & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & Column & " Is Not Null " & _
"HAVING Count(" & Column & ") > 3 and " & Alias & ".TheStDev <> 0"
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
If Not rs.EOF Then
DKurtosis = rs!Skew
Else
DKurtosis = Null
End If
rs.Close
GoTo Cleanup
ErrHandler:
DKurtosis = CVErr(Err.Number)
Cleanup:
Set rs = Nothing
End Function
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 (7)
Commented:
Commented:
You fired up a recordset to get the count.
Why not just use a Move to get to the middle, or middle two?
'ok if rs.recordcount is odd then things are easy
If rs.RecordCount Mod 2 = 1 Then
'odd number
rs.Move ((rs.RecordCount - 1) / 2)
MedianValue = rs(FieldName)
Else
rs.Move ((rs.RecordCount - 2) / 2)
MedianValue = rs(FieldName)
rs.MoveNext
MedianValue = MedianValue + rs(FieldName)
MedianValue = MedianValue / 2
End If
The overhead of the recordset has been incurred, why flop back to SQL to get the values?
Just asking.
Commented:
I need to find the median of a calculated field in my table. I get an error when using the calculated field and confirmed in your own file that when using a calculated field as the value it will always return an error? Do you know if there is any easy fix for this? I tried looking at your code but I'm not sure where to start.
Thanks,
Miles
Commented:
Commented:
View More