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,
--
Terrell Miller
mill...@bellsouth.net
"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)
"Terrell Miller" <mill...@bellsouth.net> wrote in message
news:9iXId.21756$8W4....@bignews6.bellsouth.net...
Thanks, Doug. Budget is a theoretical concept for this government agency
these days, but I'll check it out. Looks like you can buy a single-seat
license for $600 and then include a free runtime add-in to just get the
raw stats coming out, which is all we'd need.
That may be preferable to pulling the data into SAS.
Thanks again,
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?
--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
reso...@pcdatasheet.com
www.pcdatasheet.com
"Terrell Miller" <mill...@bellsouth.net> wrote in message
news:9iXId.21756$8W4....@bignews6.bellsouth.net...
me likee, thanks!
folks, just a heads-up that you can add trendlines to a Pivot Chart.
Finally got around to fiddling around with that today. Choice of linear,
exponential or log. You can show the equation and r-squared. No
supersophisticated analysis, but for a simple fitted curve Pivot Charts
will do you just fine.