[AccessD] z score to percentile calculator

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