[AccessD] Reference Material

Mitsules, Mark S. (Newport News) Mark.Mitsules at ngc.com
Thu Aug 26 13:39:54 CDT 2004


Thanks Marty.

Desktop SAS...who knew?  Thankfully, all the mainframe SAS code I utilize
was written long before I came on board...all I have to do is maintain and
hope nothing goes wrong.  The only hiccup we've had was an OS update that
necessitated a few library changes.

Mark



-----Original Message-----
From: MartyConnelly [mailto:martyconnelly at shaw.ca] 
Sent: Thursday, August 26, 2004 2:19 PM
To: Access Developers discussion and problem solving
Subject: Re: [AccessD] Reference Material


 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



-- 
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list