median function

[Jonathan Snyder via AccessMonster.com]

I am trying to obtain a median for a subset of records from a tbale. I have
found code on this site (from Douglas J. Steele) to do what I want but I do
not understand part of the code. Can anyone help.

my table (tblages) is as follows:

Sample_ID Reader Age
S040001 js 12
S040001 js 13
s040001 js 11
S040001 bb 14
S040001 bb 13
s040001 bb 12
S040002 js 23
s040002 js 21
S040002 js 20

I which to obtain a median value for each sample_ID for each reader.

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

In the Function statement I do not understand what the optional whereclause
is: Can anyone help?

Thanks

 

[BAC]

A "Proper" SQL statement would be:

SELECT [Field List]
FROM [Table or recordset]
WHERE [THese conditions apply]
This function simply builds the SQL statement for your median values.

In your specific case, let's say you only want the median age for reader js
in sample S040001

Then you would call your function:

median_for_js = DMedian("Age", _

"tblages", _
"WHERE Reader" = "js" _
) As Single

Sounds silly, but if you had been testing js for several years, this may be
meaningful, or if he had taken a battery of tests, his median score across
all tests might be of interest.

The WHERE clause in this Function is optional, so you only have to create
one if you want to limit a single variable as in my example

bac

I am trying to obtain a median for a subset of records from a tbale. I have
found code on this site (from Douglas J. Steele) to do what I want but I do
not understand part of the code. Can anyone help.

my table (tblages) is as follows:

Sample_ID Reader Age
S040001 js 12
S040001 js 13
s040001 js 11
S040001 bb 14
S040001 bb 13
s040001 bb 12
S040002 js 23
s040002 js 21
S040002 js 20

I which to obtain a median value for each sample_ID for each reader.

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

In the Function statement I do not understand what the optional whereclause
is: Can anyone help?

Thanks

 

[Klatuu]

The Where clause is to limit the records to be included in the calculation.
For example, If you wanted to only look at reader BB then your where clause
would be:
"[reader] = 'bb'" (notice single quotes because reader is a text field)
For a number field say you want to include only 13 year olds:
"[Age] = 13" (no quotes)

 

[Douglas J. Steele]

Except that would have to be

"WHERE Reader = 'js'"

or

"WHERE Reader = " & Chr(34) & "js" & Chr(34)

or

"WHERE Reader = """js"""

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

A "Proper" SQL statement would be:

SELECT [Field List]
FROM [Table or recordset]
WHERE [THese conditions apply]
This function simply builds the SQL statement for your median values.

In your specific case, let's say you only want the median age for reader
js
in sample S040001

Then you would call your function:

median_for_js = DMedian("Age", _

"tblages", _
"WHERE Reader" = "js" _
) As Single

Sounds silly, but if you had been testing js for several years, this may
be
meaningful, or if he had taken a battery of tests, his median score across
all tests might be of interest.

The WHERE clause in this Function is optional, so you only have to create
one if you want to limit a single variable as in my example

bac

I am trying to obtain a median for a subset of records from a tbale. I
have
found code on this site (from Douglas J. Steele) to do what I want but I
do
not understand part of the code. Can anyone help.

my table (tblages) is as follows:

Sample_ID Reader Age
S040001 js 12
S040001 js 13
s040001 js 11
S040001 bb 14
S040001 bb 13
s040001 bb 12
S040002 js 23
s040002 js 21
S040002 js 20

I which to obtain a median value for each sample_ID for each reader.

Function DMedian(FieldName As String, _
TableName As String, _
Optional WhereClause As String = "" _
) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & FieldName & _
"] FROM [" & TableName & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
' strSQL = strSQL & "WHERE [" & FieldName & "] IS NOT NULL "
' If Len(WhereClause) > 0 Then
' strSQL = strSQL & "AND (" & WhereClause & ") "
' End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
If Len(WhereClause) > 0 Then
strSQL = strSQL & "WHERE " & WhereClause & " "
End If
strSQL = strSQL & "ORDER BY [" & FieldName & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(FieldName)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(FieldName)
rsMedian.MovePrevious
dblTemp2 = rsMedian(FieldName)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

In the Function statement I do not understand what the optional
whereclause
is: Can anyone help?

Thanks

 

[Jonathan Snyder via AccessMonster.com]

Thanks for the assistance. I do not wish to include a where statement as I
would like to return a median value for each sample_ID for each reader. The
code works without the optional where statement, but the querey returns the
median value of ALL records in the table for each Sample_ID for each reader.
I want the median value where Sample_id's and readers are the same. Here is
my code:

Function DMedian(tblAges As String, Age As String) As Single

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim lngLoop As Long
Dim lngOffSet As Long
Dim lngRecCount As Long
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim strSQL As String

Set dbMedian = CurrentDb()
strSQL = "SELECT [" & Age & _
"] FROM [" & tblAges & "] "
' NOTE: To ignore nulls when calculating the median value, use
' the following 4 lines:
strSQL = strSQL & "WHERE [" & Age & "] IS NOT NULL "
If Len(whereclause) > 0 Then
strSQL = strSQL & "AND (" & whereclause & ") "
End If
' NOTE: The following 3 lines will include nulls. Remove them
' (and use the 4 lines above) if you want to ignore nulls.
'If Len(WhereClause) > 0 Then
' strSQL = strSQL & "WHERE " & WhereClause & " "
' End If
strSQL = strSQL & "ORDER BY [" & Age & "]"
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffSet = ((lngRecCount + 1) / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
DMedian = rsMedian(Age)
Else
lngOffSet = (lngRecCount / 2) - 2
For lngLoop = 0 To lngOffSet
rsMedian.MovePrevious
Next lngLoop
dblTemp1 = rsMedian(Age)
rsMedian.MovePrevious
dblTemp2 = rsMedian(Age)
DMedian = (dblTemp1 + dblTemp2) / 2
End If
End If

End_DMedian:
On Error Resume Next
rsMedian.Close
dbMedian.Close
Set dbMedian = Nothing
Exit Function

Err_DMedian:
Err.Raise Err.Number, "DMedian", Err.Description
Resume End_DMedian

End Function

Here is my SQL: statement from the query which runs the function:

SELECT DISTINCT tblAges.Sample_ID, tblAges.Reader, DMedian("tblAges","age")
AS Expr1
FROM tblAges
GROUP BY tblAges.Sample_ID, tblAges.Reader;

Thanks for your assistance, it is very much appreciated!

 

[Douglas J. Steele]

There was no reason to change the function from what I'd originally posted.

That was the reason for making WhereClause optional: if you didn't want a
Where clause, you could simply omit it. In other words,
DMedian("tblAges","age") would work fine with my original code.

 

[Jonathan Snyder via AccessMonster.com]

I omitted the where statement, but the function is still returning a median
based on every record in the table. I want a median based on records where
sample_ID values are the same and Reader values are the same.

 

[Douglas J. Steele]

I just went back and reread your original post. You definitely need a WHERE
clause to get what you want!

Create a query that returns each unique combination of Sample_ID and Reader:

SELECT DISTINCT Sample_ID, Reader FROM tblages

Save that query (for argument's sake, name the query qrySampleData)

Create a second query based on that query that uses the original DMedian
function as a computed field:

SELECT Sample_ID, Reader, _
DMedian("Age", "tblages", "Sample_ID = '" & _
[Sample_ID] & "' AND Reader = '" & _
[Reader] & "'") AS MedianAge
FROM qrySampleData

 

[Jonathan Snyder via AccessMonster.com]

Thanks,

It worked, but I'm still not sure that I understand! This is me first shot
at working with complex functions.