[AccessD] z score to percentile calculator

Got it.  Table building it is.  And I suppose I only have to build it once
and store it in the front end.  

Thanks again.


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 1:40 PM
To: Access Developers discussion and problem solving
Subject: Re: [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


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