[AccessD] z score to percentile calculator

The problem is that it has to open and close an Excel application every time it is called. 

OK if you are calling it for a single record, but embed it in a query based on your 5-6000 row 
table and it would take a looooong time :-)
  
-- 
Stuart

On 23 Oct 2008 at 6:53, Rocky Smolin at Beach Access  wrote:

> Stuart:
> 
> You think the function would be that slow?   I don't know how many calls
> there would be to it for any inquiry.  But the number of patients in the db
> will probably never be over 5-6000.  
> 
> Still I'll probably go with the table.
> 
> 
> Rocky Smolin
> Beach Access Software
> 858-259-4334
> www.e-z-mrp.com
> www.bchacc.com
>  
>  
> 
> -----Original Message-----
> From: accessd-bounces at databaseadvisors.com
> [mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Stuart McLachlan
> Sent: Thursday, October 23, 2008 4:23 AM
> To: Access Developers discussion and problem solving
> Subject: Re: [AccessD] z score to percentile calculator
> 
> Thinking about this a bit more, I'd probably use the function below once to
> build a look up table.
> 
> On second thoughts, I probably build a lookup table for speed:
> 
> Function CreateTable()
> Dim strsql As String
> Dim i As Currency  'use Currency to avoid rounding errors in ZScores Dim
> xlapp  As New Excel.Application strsql = "Create table tblZScoreToPercentile
> (ZScore Double primary key,Percentile double)"
> CurrentDb.Execute strsql
> For i = -5 To 5 Step 0.01
>    CurrentDb.Execute "Insert into tblZScoreToPercentile (ZScore,Percentile)
> values(" & i & "," 
> & xlapp.NormSDist(i) & ")"
> Next
>  xlapp.Quit
>  Set xlapp = Nothing
> End Function
> 
> --
> Stuart
> 
> 
> On 23 Oct 2008 at 16:57, Stuart McLachlan wrote:
> 
> > Oops, try this:
> > 
> > Simplest way is to set a reference to Excel and then use the NormSDist() 
> > function:
> >  
> >  Function NormSDist(ZScore As Double) As Double
> >  Dim xlapp  As New Excel.Application
> >  NormSDist = xlapp.NormSDist(ZScore)
> >  xlapp.Quit
> >  Set xlapp = Nothing
> >  End Function
> > -- 
> >  Stuart
> > 
> > -- 
> > AccessD mailing list
> > AccessD at databaseadvisors.com
> > http://databaseadvisors.com/mailman/listinfo/accessd
> > Website: http://www.databaseadvisors.com
> 
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com
> 
> -- 
> AccessD mailing list
> AccessD at databaseadvisors.com
> http://databaseadvisors.com/mailman/listinfo/accessd
> Website: http://www.databaseadvisors.com