How to calculate the median in a GROUP BY query

[Guest]

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

 

[strive4peace]

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

*

 

[Guest]

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:

 

[strive4peace]

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

*

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

*

 

[Guest]

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

*

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

 

[Jamie Collins]

Currency data type carries 4 decimal places after the
decimal point and 15 places before.

Currency is the most accurate numeric data type

What about the DECIMAL data type? It can accommodate 28 digits before
the decimal and up 28 to digits after (scale) to a combined (precision)
maximum of 38 digits. It also does not round (CURRENCY performs
banker's rounding, which may not be desired).

What do you mean by 'the most accurate'?

Jamie.

--

 

[strive4peace]

Hi Jamie,

Decimal is not a valid data type in Access 2000, which is
what I am using.

In fact, if you do a make-table query from Oracle, for
instance, and one of the fields is decimal data type, you
need to convert it in your query to something else or it
will cause problems.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

You're welcome, Droopy

sounds like to need to send the Where clause of your SQL
string to the function so that you can make it more flexible.

The first step is to understand the code that was given to
you so that you can modify it.

Email me and I will send the first 3 chapters of a book I am
writing on VBA -- that will give you some foundation. Put
"VBA chapters" in the subject line

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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

*

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

 

[Jamie Collins]

Decimal is not a valid data type in Access 2000, which is
what I am using.

I don't think that is correct. The following MSDN article has "Access
2000" in the title and details the 'new' DECIMAL data type:

Intermediate Microsoft Jet SQL for Access 2000

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acintsql.asp

"With the new DECIMAL data type, you can also set the precision and
scale of the number. The precision is the total number of digits that
the field can contain, while the scale determines how many of those
digits can be to the right of the decimal point. For the precision, the
default is 18 and the maximum allowed value is 28. For the scale, the
default is 0 and the maximum allowed value is 28."

Regardless, I think that if you are limiting your advice to a release
of the product which has been superceded twice (by Access2002 and
Access2003) you make this clear at the time.

Jamie.

--

 

[Guest]

Hello,

Whats your e-mail for emailing you ?

Regards,
Droopy

"strive4peace" <"strive4peace2006 at yaho" schreef:

You're welcome, Droopy

sounds like to need to send the Where clause of your SQL
string to the function so that you can make it more flexible.

The first step is to understand the code that was given to
you so that you can modify it.

Email me and I will send the first 3 chapters of a book I am
writing on VBA -- that will give you some foundation. Put
"VBA chapters" in the subject line

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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

 

[strive4peace]

Hi Jamie,

thanks for pointing that out -- I have, however, had
troubles using it with A2K (maybe it is buggy).

I do have 2003... but will probably skip really using it
much as 2007 is a whole lot different and that is where I am
shifting my focus.

A lot of people that I work with still use 2K (which is why
I do as well since I do training as well as programming)

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

 

[strive4peace]

its in my siggy ...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

Hello,

Whats your e-mail for emailing you ?

Regards,
Droopy

"strive4peace" <"strive4peace2006 at yaho" schreef:

You're welcome, Droopy

sounds like to need to send the Where clause of your SQL
string to the function so that you can make it more flexible.

The first step is to understand the code that was given to
you so that you can modify it.

Email me and I will send the first 3 chapters of a book I am
writing on VBA -- that will give you some foundation. Put
"VBA chapters" in the subject line

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*

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

 

[Jamie Collins]

thanks for pointing that out -- I have, however, had
troubles using it with A2K (maybe it is buggy).

You probably use DECIMAL without realizing e.g. try

SELECT TYPENAME(0.5) FROM AnyTable;

and the result will show that Jet considers 'decimal' values (within a
certain range) as DECIMAL. So if Jet 4.0 has used DECIMAL natively for
5+ years and three version of Access, if it really was that buggy I
think we'd have heard about a lot more problems by now. Sure, it has
some known issues (show me functionality of a MSFT product that doesn't
<g>) but none makes it unusable.

Jamie.

--

 

[strive4peace]

Hi Jamie,

I was basing my evaluation on converting data of decimal
data type from other systems into Access... not the internal
use Access does for decimal data type -- but Thanks!!! It
is nice to learn this.

My experience has been that until the data type is
converted, having numbers defined as decimal in the table
structure causes problems (in my limited use so far) with
programs I have written. Because of this, I convert when I
see decimal and have not used them as much as I could have
(in an attempt to eliminate possible problem sources).

thanks for passing along the extra info, I appreciate that
and will keep a more open mind.

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*