MartyConnelly
martyconnelly at shaw.ca
Thu Aug 26 13:19:13 CDT 2004
Here is some snippet code to call Excel's Descriptive Statistical Functions
The first calls the basic Excel Functions, The second calls the Excel
Solver and Analysis Pack functions
which are essentially .xla files, these may have to installed via full
install of Excel.
If doing a heavy duty statistical analysis which has to be strongly QA'ed,
I might be tempted to uses SAS or SPSS. Excel still has some petty glitches.
like the Random function failing to being close to a truely random
distribution after a point beyond a million calls.
SAS has a training educational version for $125, it is limited to
Datasets of 1000 points.
I believe SAS came out with an Office addin in April, that allows calls
from Word, Excel or Access.
http://support.sas.com/news/insider/msaddin.html
The graphing tool available to Office products is limited even in
considering aesthetic presentations.
So you might want to consider a 3'd party Active X control for graphical
display ie. putting a jpeg in a report..
Sub FindMedian()
'set a reference to Excel object library
'uses linked Northwinds Product table
Dim appXL As Excel.Application
Dim dbs As Database, rst As Recordset
Dim strSQL As String, intI As Integer
Dim sngArray() As Single, sngMedian As Single
' Create SQL string.
strSQL = "SELECT DISTINCTROW UnitPrice FROM Products ORDER BY UnitPrice;"
' Return reference to current database.
Set dbs = CurrentDb
' Open dynaset-type recordset.
Set rst = dbs.OpenRecordset(strSQL)
' Populate recordset.
rst.MoveLast
' Return to beginning of recordset.
rst.MoveFirst
' Redimension array based on recordset size.
ReDim sngArray(0 To rst.RecordCount - 1)
' Populate array with values from recordset.
For intI = 0 To UBound(sngArray)
sngArray(intI) = rst!UnitPrice
rst.MoveNext
Next
' Create new instance of Excel Application object.
Set appXL = CreateObject("Excel.Application")
' Pass array to Excel's Median function.
sngMedian = appXL.Application.Median(sngArray())
Debug.Print sngMedian
' Close Excel.
appXL.Quit
' Free object variables.
Set appXL = Nothing
Set dbs = Nothing
End Sub
------------
'sample call
'?fLCM(24,36)
'
Function fLCM(intA As Integer, intB As Integer) As Integer
'Least Common Multiple of Integers function
'set a reference to Excel object library
Dim objXL As Excel.Application
Dim strText As String
Dim blnCheck As Boolean
Set objXL = New Excel.Application
strText = objXL.Application.LibraryPath
Debug.Print strText
With objXL
blnCheck = .RegisterXLL(.Application.LibraryPath & "\solver\solver.dLL")
Debug.Print blnCheck
'If .AddIns("Analysis Toolpak").Installed Then
.Workbooks.Open (objXL.Application.LibraryPath & _
"\Analysis\atpvbaen.xla")
.Workbooks("atpvbaen.xla").RunAutoMacros (xlAutoOpen)
fLCM = .Application.Run("atpvbaen.xla!lcm", intA, intB)
' Else
' .Workbooks.VBProject.References.AddFromFile
Filename:=obj.Application.LibraryPath & "\Analysis\atpvbaen.xla"
'fLCM = 0
'MsgBox "Can't Find Analysis Toolpak atpvbaen.xla"
'End If
End With
objXL.Quit
Set objXL = Nothing
End Function
Mitsules, Mark S. (Newport News) wrote:
>Jim,
>
>I wasn't offended by any means...honestly. It was all in jest to remind
>myself that I can't ALWAYS do it all myself.
>
>Mark
>
>
>
>
>-----Original Message-----
>From: Jim Hewson [mailto:JHewson at karta.com]
>Sent: Wednesday, August 25, 2004 4:15 PM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] Reference Material
>
>
>Mark,
>I didn't mean to insinuate any limitation of your skill set.
>Rather, it's better to have two sets of eyes on the prize...;)
>Jim
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark
>S. (Newport News)
>Sent: Wednesday, August 25, 2004 10:18 AM
>To: 'Access Developers discussion and problem solving'
>Subject: RE: [AccessD] Reference Material
>
>
>Jim,
>
>Well thank you for pointing out my obvious limitations;) I have a sneaking
>suspicion that a mathematician may indeed be needed before this is all said
>and done. I will have to keep that in mind so that I can bring in
>assistance sooner rather than later.
>
>
>Mark
>
>
>
>-----Original Message-----
>From: Jim Hewson [mailto:JHewson at karta.com]
>Sent: Wednesday, August 25, 2004 10:42 AM
>To: Access Developers discussion and problem solving
>Subject: RE: [AccessD] Reference Material
>
>
>
>Mark,
>We have a contract to do an analysis of the probability of detection (POD)
>of inspectors.
>The inspectors would "inspect" specimens similar to aircraft parts (e.g.
>lugs).
>All the data for the specimens, inspectors and inspections (including
>equipment used) is stored in an Access database. Statistical analysis is
>done to determine the POD.
>When developing the database we looked for reference material to help with
>the statistical analysis in Access. We did not find any. We purchased the
>"FMS Total Access Statistics" to help the analysis. Our mathematician
>didn't like it. We also felt it created a lot of overhead that wasn't
>needed.
>Bottom-line. We rolled our own. Statistical formulas can be broken down
>into their components. Then queries are created to "populate" the formula.
>For example, the Chi-square analysis to determine the "Coefficient of
>Contingency" (our mathematician's words) uses one query that calculates the
>components. The sequence of events for the calculations begins with a
>select query that uses eight tables and a form for the criteria. That query
>is used in another select query using six tables (some are repeated). Using
>the previous query, another query does some calculations. And the final
>query is created using the previous query to do more calculations.
>The final query results is the components of the formula. The formula is
>placed in the text box of the report.
>The formula is:
>=Sqr((1/([C1]*[R1])*(([A]-([C1]*[R1]/[N]))*([A]-([C1]*[R1]/[N]))))+(1/([C2]*
>[R1])*(([B]-([C2]*[R1]/[N]))*([B]-([C2]*[R1]/[N]))))+(1/([C1]*[R2])*(([C]-([
>C1]*[R2]/[N]))*([C]-([C1]*[R2]/[N]))))+(1/([C2]*[R2])*(([D]-([C2]*[R2]/[N]))
>*([D]-([C2]*[R2]/[N])))))
>
>For our purposes this works.
>
>My suggestion is to take it step by step, slowly.
>Have a mathematician involved that can calculate the correct answer manually
>on your sample data. Determine the data you need to do the calculations and
>analyze the data collection process. Determine if you are actually
>collecting the correct data for the statistical analysis. At every step
>when creating the queries, determine if the results are correct. I found it
>was best to work backwards. Look at the formula and determine where each
>component should come from the data. After all the queries, criteria, etc.
>have been created compare the results with the mathematicians answer - do
>they match? If not, go back and find out why. If so, CELEBRATE! You've
>earned it.
>
>Triple - no - quadruple the time you think it might take to do this. Each
>formula could take up to a week or more to perfect. Access will do the
>calculations, but be prepared to add user interface feedback. One report
>that has several sub reports that have numerous calculations (using many
>queries) has taken several minutes to finish.
>
>HTH
>
>Best of luck.
>
>Jim
>
>
>
>-----Original Message-----
>From: accessd-bounces at databaseadvisors.com
>[mailto:accessd-bounces at databaseadvisors.com]On Behalf Of Mitsules, Mark
>S. (Newport News)
>Sent: Wednesday, August 25, 2004 8:14 AM
>To: [AccessD]
>Subject: [AccessD] Reference Material
>
>
>Group,
>
>Can anyone suggest some reference material that discusses developing
>sophisticated Access reports which will include statistical analysis and
>graphing?
>
>
>Mark
>
>
--
Marty Connelly
Victoria, B.C.
Canada