SELECT Avg(X * Y) - Avg(X) * Avg(Y) AS Covariance
FROM Set1;
Adapted from "qrySet1_SQL_CorrCov_Code":
SELECT Code, Avg(X * Y) - Avg(X) * Avg(Y) AS Covariance
FROM Set1
GROUP BY Code;
Adapted from "qrySet1_SQL_CorrCov_Sect":
SELECT Section, Avg(X * Y) - Avg(X) * Avg(Y) AS Covariance
FROM Set1
GROUP BY Section;
Adapted from "qrySet1_SQL_CorrCov_SectCode":
SELECT Section, Code, Avg(X * Y) - Avg(X) * Avg(Y) AS Covariance
FROM Set1
GROUP BY Section, Code;
Adapted from "qrySet1_UDF_CorrCov_All"
SELECT DCovariance("X","Y","Set1") AS Covariance;
Adapted from "qrySet1_UDF_CorrCov_Code"
SELECT Code, DCovariance("X","Y","Set1","[Code] = '" & [Code] & "'") AS Covariance
FROM Set1
GROUP BY Code;
Adapted from "qrySet1_UDF_CorrCov_Sect"
SELECT Section, DCovariance("X","Y","Set1","[Section] = '" & [Section] & "'") AS Covariance
FROM Set1
GROUP BY Section;
Adapted from "qrySet1_UDF_CorrCov_SectCode"
SELECT Section, Code, DCovariance("X","Y","Set1","[Section] = '" & Section & "' And [Code] = '" & Code & "'") AS Covariance
FROM Set1
GROUP BY Section, Code;
Adapted from "qrySet1_SQL_CorrCov_All"
SELECT (Avg(X * Y) - Avg(X) * Avg(Y)) / (StDevP(X) * StDevP(Y)) AS Correlation
FROM Set1;
Adapted from "qrySet1_SQL_CorrCov_Code"
SELECT Code, (Avg(X * Y) - Avg(X) * Avg(Y)) / (StDevP(X) * StDevP(Y)) AS Correlation
FROM Set1
GROUP BY Code;
Adapted from "qrySet1_SQL_CorrCov_Sect"
SELECT Section, (Avg(X * Y) - Avg(X) * Avg(Y)) / (StDevP(X) * StDevP(Y)) AS Correlation
FROM Set1
GROUP BY Section;
Adapted from "qrySet1_SQL_CorrCov_SectCode"
SELECT Section, Code, (Avg(X * Y) - Avg(X) * Avg(Y)) / (StDevP(X) * StDevP(Y)) AS Correlation
FROM Set1
GROUP BY Section, Code;
Adapted from "qrySet1_SQL_CorrCov_All"
SELECT DCorrelation("X","Y","Set1") AS Correlation;
Adapted from "qrySet1_SQL_CorrCov_Code"
SELECT Code, DCorrelation("X","Y","Set1","[Code] = '" & [Code] & "'") AS Correlation
FROM Set1
GROUP BY Code;
Adapted from "qrySet1_SQL_CorrCov_Sect"
SELECT Set1.Section, DCorrelation("X","Y","Set1","[Section] = '" & [Section] & "'") AS Correlation
FROM Set1
GROUP BY Set1.Section
ORDER BY Set1.Section;
Adapted from "qrySet1_SQL_CorrCov_SectCode"
SELECT Section, Code, DCorrelation("X","Y","Set1","[Section] = '" & Section & "' And [Code] = '" & Code & "'") AS Correlation
FROM Set1
GROUP BY Section, Code;
Function DCovariance(X_Column As String, Y_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 covariance for two sets of data, and is intended for use in Microsoft
' Access. This function requires a reference to the Microsoft DAO library.
' If either or both corresponding values in the paired X, Y data set are null, those records are ignored
' This function is labeled according to the domain aggregate function naming convention as it behaves
' similarly to the other domain aggregates
' X_Column, Y_Column are the columns having the data for which you want to calculate correlation
' 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
' For each of the arguments, I strongly recommend that you encase column and table names in square
' brackets. This is mandatory of the column/table name does not follow the usual rules for naming
' database objects
Dim rs As DAO.Recordset
Dim SQL As String
Dim Counter As Long
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
' Build up SQL string
SQL = "SELECT Avg(" & X_Column & " * " & Y_Column & ") - Avg(" & X_Column & ") * Avg(" & Y_Column & ") As Covar " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & X_Column & " Is Not Null And " & _
Y_Column & " Is Not Null"
' Open recordset
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
' Set return value
DCovariance = rs!Covar
rs.Close
GoTo Cleanup
ErrHandler:
DCovariance = CVErr(Err)
Cleanup:
Set rs = Nothing
End Function
Function DCorrelation(X_Column As String, Y_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 correlation between two sets of data, and is intended for use in Microsoft
' Access. This function requires a reference to the Microsoft DAO library.
' If either or both corresponding values in the paired X, Y data set are null, those records are ignored
' This function is labeled according to the domain aggregate function naming convention as it behaves
' similarly to the other domain aggregates
' X_Column, Y_Column are the columns having the data for which you want to calculate correlation
' 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
' For each of the arguments, I strongly recommend that you encase column and table names in square
' brackets. This is mandatory of the column/table name does not follow the usual rules for naming
' database objects
Dim rs As DAO.Recordset
Dim SQL As String
Dim Counter As Long
On Error GoTo ErrHandler
' Build up SQL string
SQL = "SELECT (Avg(" & X_Column & " * " & Y_Column & ") - Avg(" & X_Column & ") * Avg(" & Y_Column & ")) / " & _
"(StDevP(" & X_Column & ") * StDevP(" & Y_Column & ")) As Correl " & _
"FROM " & Tbl & " " & _
"WHERE " & IIf(Trim(Criteria) <> "", Criteria & " And ", "") & X_Column & " Is Not Null And " & _
Y_Column & " Is Not Null"
' Open recordset
Set rs = CurrentDb.OpenRecordset(SQL, dbOpenSnapshot)
' Set return value
DCorrelation = rs!Correl
rs.Close
GoTo Cleanup
ErrHandler:
DCorrelation = CVErr(Err)
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:
Big YES vote above.
Commented:
Commented:
Commented:
Author
Commented:Not zero, but rather undefined: arithmetically, division by zero is undefined. So, if at least one of the variances is zero, then the denominator is going to be zero.
Patrick
View More