atahim wrote:
I have a query in my database that calculates '# of Days' based on a
start and end date and displays it amongst other fields. What i need to
do now is develop another query that will give me the following 2
columns:
#ofDays Normal Distribution
I have a function that calculates the normal distribution only when
there is a static table/query that holds the mean and stddev of the
#ofDays field. What i wish to do is have a function that uses the
original query with the '# of Days' , calculates the mean and stddev
storing it in a variable and then gives me the normal distribution
values for each value of '#ofDays'.
Any suggestions?
If your table is called tblStartStop with a field called NumberOfDays
(or say qryStartStop that calculates NumberOfDays used everywhere
instead) then:
Create a public function:
Public Function P(X As Double, Mu As Double, Sigma As Double) As Double
P = Your function
End Function
then
qryNormalized:
SELECT NumberOfDays, (SELECT Avg(NumberOfDays) FROM tblStartStop) As
Mean, (SELECT StDev(NumberOfDays) FROM tblStartStop) As StdDev,
P([NumberOfDays], [Mean], [StdDev]) AS Px FROM tblStartStop;
Example:
tblStartStop
ID NumberOfDays
1 3
2 5
3 2
4 6
5 3
!qryNormalized:
NumberOfDays Mean StdDev Px
3 3.8 1.6431676725155 0.744
5 3.8 1.6431676725155 0.382
2 3.8 1.6431676725155 0.848
6 3.8 1.6431676725155 0.253
3 3.8 1.6431676725155 0.744
Px values shown are made up. You will likely want to round Px. Note
that Avg and StDev can use expressions, so calculations could actually
be made using the start and stop values themselves if you prefer. I
hope I understood what you wanted. You can also code your own standard
deviation function if you need to adjust n by one because of the
population versus sample degrees of freedom. I didn't look at how
StDev was defined and I was also too lazy to look for other standard
deviation functions in Access.
James A. Fortune