By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,339 Members | 1,973 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,339 IT Pros & Developers. It's quick & easy.

question about regression in Access

P: n/a
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,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
Nov 13 '05 #1
Share this Question
Share on Google+
6 Replies


P: n/a
If you've got a bit of a budget, check out Total Access Statistics, from FMS
http://www.fmsinc.com/products/statistics/index.html

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"Terrell Miller" <mi******@bellsouth.net> wrote in message
news:9i*******************@bignews6.bellsouth.net. ..
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,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

Nov 13 '05 #2

P: n/a
Douglas J. Steele wrote:
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,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
Nov 13 '05 #3

P: n/a
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?
Nov 13 '05 #4

P: n/a
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.

--
PC Datasheet
Your Resource For Help With Access, Excel And Word Applications
re******@pcdatasheet.com
www.pcdatasheet.com
"Terrell Miller" <mi******@bellsouth.net> wrote in message
news:9i*******************@bignews6.bellsouth.net. ..
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,

--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison

Nov 13 '05 #5

P: n/a
PC Datasheet wrote:
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
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
Nov 13 '05 #6

P: n/a
Terrell Miller wrote:

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.
--
Terrell Miller
mi******@bellsouth.net

"Every gardener knows nature's random cruelty"
-Paul Simon RE: George Harrison
Nov 13 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.