Linear regression in MS Access

C

clausoh

I am developing an MS Access database that should forecast a manning
load. This forecast is based on linear regression (which I until now
have performed in MS Excel). However, I would like to automate the
actual linear regression part into Access. But how do I do this.
I cannot find any methods or anything explaining this on the net. I
would rather not through the long way of implementing the actual matrix
calculations based on my stats books from university.

One solution is to embed excel into access but that seems a little bit
cumbersome.

any ideas?
 
P

Peter Hibbs

Hi Clausoh

You don't say which version of Access you are using but my version (A2000)
comes with an ActiveX control Spreadsheet. To use it create a new form and go
to Insert - ActiveX Control and scroll down to Microsoft Office Spreasheet
9.0 (or whatever version you have) and click OK.

I have no idea whether it will do Linear Regression but there are extensive
Help files provided with the control.

HTH
 
T

Tom Wickerath

The following two examples use early bound code. Early binding means that you
will need to set a reference to the "Microsoft Excel X.0 Object Library",
where X = 9 for Excel 2000, 10 for Excel 2002 and 11 for Excel 2003. To set
this reference, create a new module. Then click on Tools > References. Scroll
down the list until you find this object library. Place a check in it. Also,
make sure that you have a reference set to the "Microsoft DAO 3.6 Object
Library".

You can likely convert this code to late bound code (does not require a
reference to Excel), but first things first. Get your code working early
bound, where you can use Intellisense, before trying to convert it to the
more robust late bound. These examples were tested using Access 2003 with
Excel 2003.

Example # 1 uses the same values found in the Excel Help file for the
Forecast function, as hardcoded values:

Sub ForecastHardcodedTest()
On Error GoTo ProcError
' Should return 10.60725

Dim objExcel As Excel.Application
Dim Y(5) As Variant
Dim X(5) As Variant

Set objExcel = CreateObject("Excel.Application")

Y(0) = 6
Y(1) = 7
Y(2) = 9
Y(3) = 15
Y(4) = 21

X(0) = 20
X(1) = 28
X(2) = 31
X(3) = 38
X(4) = 40

MsgBox objExcel.Application.Forecast(30, Y, X)


ExitProc:
'Cleanup
objExcel.Quit: Set objExcel = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ForecastTest..."
Resume ExitProc

End Sub


To run this procedure, make sure your blinking mouse cursor is anywhere
within the procedure. Then press the F5 button.

The second example uses the same X,Y values, but this time we are going to
store them in a new table. Create a new table. Add the following three fields:

Field Name Data Type
pkDatum Autonumber (set as primary key)
YValue Number (Long Integer)
XValue Number (Long Integer)

Save the table with the name: tblForecast
Populate your table with the following values (the autonumber will increment
itself):

pkDatum YValue XValue
1 6 20
2 7 28
3 9 31
4 15 38
5 21 40

(These are the same X,Y values shown in the Excel Help file). Create the
following subroutine:

Sub ForecastTest()
On Error GoTo ProcError

Dim db As Database
Dim rs As DAO.Recordset
Dim strSQL As String
Dim i As Long
Dim j As Long
Dim Y() As Variant
Dim X() As Variant
Dim objExcel As Excel.Application

Set db = CurrentDb()
Set objExcel = CreateObject("Excel.Application")

strSQL = "SELECT YValue, XValue FROM tblForecast"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount > 0 Then
rs.MoveLast
rs.MoveFirst
j = rs.RecordCount
ReDim Y(j)
ReDim X(j)

'Populate Arrays
With rs
For i = 0 To j - 1
Y(i) = rs("YValue")
X(i) = rs("XValue")
rs.MoveNext
Next i
End With

' For i = 0 To j - 1
' Debug.Print Y(i) & ", " & X(i)
' Next i

'Call Forecast Function
MsgBox objExcel.Application.Forecast(30, Y, X)

End If

ExitProc:
'Cleanup
rs.Close: Set rs = Nothing
db.Close: Set db = Nothing
objExcel.Quit: Set objExcel = Nothing
Exit Sub
ProcError:
MsgBox "Error " & Err.Number & ": " & Err.Description, _
vbCritical, "Error in procedure ForecastTest..."
Resume ExitProc

End Sub


So, the only hardcoded value that remains is the X value that we are
evaluating (30). This is easy enough to change, so that it references a
textbox in a form, or a value in a table, etc.


Tom Wickerath
Microsoft Access MVP

http://www.access.qbuilt.com/html/expert_contributors.html
http://www.access.qbuilt.com/html/search.html
__________________________________________
 
T

Tom Wickerath

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top