Many thanks. It helps me a lot but how can I add more fields in the WHERE
because sometimes I have 5 different criteria from 5 different fields.
Thanks
Regards
DroopyII
"strive4peace" <"strive4peace2006 at yaho" schreef:
Hi Droopy,
firstly, even though you aren't working with money, you may
want to consider using the currency data type.
Currency data type carries 4 decimal places after the
decimal point and 15 places before.
Currency is the most accurate numeric data type that has digits
Single and Double precision numbers are stored in floating
point format (exponential format: 1.234 x 10^^4) are
susceptable to creating "Ghost" digits ... this makes them
invalid for exact comparisons.
In order to limit the calculation to a specific group, send
the ID fieldname and value for that group
Public Function Percentile( _
fldName As String, _
tblName As String, _
p As Currency, _
fldNameID as string, _
ValueID as long _
) As Currency
and modify the SQL string accordingly:
sqlSort = "SELECT [" & fldName & "] " _
& " FROM [" & tblName & "] " _
& " WHERE [" & fldNameID & "] = " _
& ValueID _
& " ORDER BY [" & fldName & "];"
'~~~~~~~~~~~~~~
if your group field is a string:
ValueID as long --> ValueID as string
and
& "'" & ValueID & "'" _
'~~~~~~~~~~~~~~
if it is a date:
ValueID as long --> ValueID as date
and
& "#" & ValueID & "#" _
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
DROOPY wrote:
Hello,
Here is the code :
Public Function Percentile(fldName As String, tblName As String, p As
Double) As Double
'VERIFY VALID PERCENTILE (0-100) WAS GIVEN
If (p <= 0 Or p >= 100) Then
Percentile = -555555555 'Something to stick out!
Exit Function
End If
'ENSURE DESIRED DATA IS SORTED
Dim sqlSort As String
sqlSort = "SELECT [" & fldName & "] " & "FROM [" & tblName & "] " &
"ORDER BY [" & fldName & "]"
Dim cnn As ADODB.Connection
Set cnn = CurrentProject.Connection
Dim rst As ADODB.Recordset
Set rst = CreateObject("ADODB.Recordset")
rst.Open sqlSort, cnn, adOpenStatic, adLockReadOnly, 1
rst.MoveLast
'How many observatons? For example, N=12
Dim N As Long
N = rst.RecordCount
'Which observation would, theoretically, be the pTH "true"
'percentile.
'e.g., for 25th percentile would be the 0.25*(12+1)=3.25th
'observation
Dim break_pt As Double
break_pt = (p / 100) * (N + 1) '3.25 = (25/100)*(12+1)
'There's 2 special extreme cases we need to worry about!
If break_pt < 1 Then break_pt = 1 'small sample for small
'Percentile
If break_pt > N Then break_pt = N 'small sample for large
'Percentile
'But since there's no such thing as a 3.25th observation, we
'estimate it
'somewhere between the 3rd and 4th observations. It'll be
'approximately:
'p = r1*low_obs + r2*high_obs
Dim low_obs As Long, high_obs As Long
low_obs = Int(break_pt) '3 = int(3.25)
high_obs = low_obs + 1 '4 = 3 + 1
'Now, we have to interprolate between the "boundaries"
Dim r1 As Double, r2 As Double
r1 = high_obs - break_pt '0.75 = 4 - 3.25
r2 = 1 - r1 '0.25 = 1 - 0.75
'Since we have determined the needed observations and their
'weights
'we can loop through the recordset until we reach these
'observations
Dim recno As Long
rst.MoveFirst: recno = 0
Dim x As Double
Do Until rst.EOF
recno = recno + 1
If recno = low_obs Then x = r1 * rst(0)
If recno = high_obs Then
x = x + r2 * rst(0)
Exit Do
End If
rst.MoveNext
Loop
'We now have our percentile!
Percentile = x
End Function
I have a table which is called [Dosimetrie RX]. All my data are in this table.
From this table, I made a query called [RX Verslag per plaats]. In this
query I have the following fields :
Klant2
Jaar
TypeOnderzoek
TypeSub
TypeSub2
Lokaal
DAP
For the first 6 fields, the user can enter an expression as criteria in my
query.
I need in fact to calculate the median (and also the Percentile 75) of the
value called DAP in function of the different criteria. It's the reason why I
use a group by query.
Many many thanks for your help.
DroopyII
"strive4peace" <"strive4peace2006 at yaho" schreef:
please post the code you are using to determine median,
specify your data structure, and exactly what you want a
median of -- we can help you modify the code
Warm Regards,
Crystal
Microsoft Access MVP 2006
*
Have an awesome day
remote programming and training
strive4peace2006 at yahoo.com
*
DROOPY wrote:
Hello,
I found a module that's calculate the median of a certain field in a table.
I need to calculate the median value of a group of data in a GROUP BY QUERY.
Can somebody help me ?
Thanks