question about regression in Access
Terrell Miller
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,
--
Terrell Miller
mill...@bellsouth.net
"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
Douglas J. Steele
http://www.fmsinc.com/products/statistics/index.html
--
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...
Terrell Miller
> If you've got a bit of a budget, check out Total Access Statistics, from FMS
> http://www.fmsinc.com/products/statistics/index.html
>
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,
Lyle Fairfield
> 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?
PC Datasheet
needs two columns of data (X and Y) so you export your data from a table or
query. Excel puts the regression results in a table in a specified location
on the spreadsheet so that makes importing the results into Access very
easy. All the while this is going on, Excel doesn't even have to appear on
the screen. The results will include the slope of the line, the Y intercept
and statistics about the goodness of the fit of the data.
--
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...
Terrell Miller
> The simplest thing to do is to use Access automation with Excel. Excel only
> needs two columns of data (X and Y) so you export your data from a table or
> query. Excel puts the regression results in a table in a specified location
> on the spreadsheet so that makes importing the results into Access very
> easy. All the while this is going on, Excel doesn't even have to appear on
> the screen. The results will include the slope of the line, the Y intercept
> and statistics about the goodness of the fit of the data.
me likee, thanks!
Terrell Miller
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.