Terrell Miller wrote:
I am developing an Access/SQL2k app for a science lab. One of the lab
folks asked me this, and I don't know the answer:
Is it possible to do simple, straight-line trends somehow in Access
without having to export the data to a statistics package or Excel?
IOW, can I create a query (or at the very least a chart) that will show
the trendline for a data series?
Thanks for your help,
From an old (1998) post ; you can get slope and y-intercept with
something like this.
Sub sRegressionLine()
Dim dbs As Database, rcs As Recordset
Set dbs = CurrentDb()
Set rcs = dbs.OpenRecordset("SELECT Sum(Data.X) AS SumX, " & _
"Sum([X]*[X]) AS SumXX, Sum(Data.Y) AS SumY, Sum([X]*[Y]) AS SumXY,
" & _
"Count(Data.X) AS N FROM Data " & _
"WHERE (((Data.X) Is Not Null) AND ((Data.Y) Is Not Null));")
m = (rcs!N * rcs!SumXY - rcs!SumX * rcs!SumY) / (rcs!N * rcs!SumXX -
rcs!SumX ^ 2)
b = (rcs!SumY * rcs!SumXX - rcs!SumX * rcs!SumXY) / (rcs!N *
rcs!SumXX - rcs!SumX ^ 2)
End Sub
I guess today I would do it a bit differently .. at least to use Count(*).
You can then interpolate with
"UPDATE Data SET Data.Y = " & _
"(" & m & ")" & " * [X] + (" & b & ")" & _
"WHERE (((Data.Y) Is Null) AND ((Data.X) Is Not Null));"
You could use the same query to extrapolate; need I say that
extrapolation of itself must be suspect?
