Median Calculation, Dates, & Group By

[Axess08]

I want to calculate the medians for a string of values that are going to be
"group by" date, specifically the year (and I would like to group by the most
current quarter as well, but let me just deal with the year first).

I have tried the median modules that were posted by John Nurick,
TimFerguson, Matthias Klay, Tom Wickerath, (in the Median Calculation post
dated 1/4/2007 to I believe 1/6/2007) and the generic one from Microsoft as
well as a few other median modules that I found elsewhere. Not all of the
modules work for me the way that they are designed because I need to get the
median for a set of values known as 'Gravita' (G) and 'Parity' (P) (which are
single integers) and I would like to filter them by year or group them by
date. Matthias's module is close, but it relies upon text values which my
years are not (and I prefer to retain as years because I have grouped other
values as this in the same query). I have this working copy of another module
which is quite accurate, but displays the median value for the entire column
of 'G' values regardless of grouping or filtering. The module is known as
"mdlmedian" and the aggregate is DMedian (which would probably explain why it
works similar to DAvg in that it refuses to be grouped). (I am not sure of
who created this module but if anyone recognizes it as their please let me
know and I will of course include their names and whatever info they want in
it):

Option Compare Database

Option Explicit
Function DMedian( _
Expr As String, _
Domain As String, _
Optional Criteria As String = "" _
) As Variant

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim lngOffset As Long
Dim lngRecCount As Long
Dim strSQL As String
Dim varMedian As Variant

strSQL = "SELECT " & Expr & " AS Data " & _
"From " & Domain & " "
strSQL = strSQL & _
"Where " & Expr & " IS NOT NULL "
If Len(Criteria) > 0 Then
strSQL = strSQL & "AND (" & Criteria & ") "
End If
strSQL = strSQL & "ORDER BY " & Expr

Set dbMedian = CurrentDb()
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.BOF = False And _
rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffset = ((lngRecCount + 1) / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
varMedian = rsMedian("Data")
Else
lngOffset = (lngRecCount / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
dblTemp1 = rsMedian("Data")
rsMedian.MovePrevious
dblTemp2 = rsMedian("Data")
varMedian = (dblTemp1 + dblTemp2) / 2
End If
Else
varMedian = Null
End If

rsMedian.Close
Set rsMedian = Nothing
Set dbMedian = Nothing

DMedian = varMedian

End Function



Does anyone have any suggestions as to how to get it to work with a group?


These are my headings for the queries/table that are relevant:

[Fiscal Year] (2006, 2007, etc. are the values that fall within the columns)
[Fiscal Quarter] (FY2009 Qtr 1, FY2009 Qtr2, etc. are the values that fall
within the columns) (Fiscal Year and Fiscal Quarter are Group by in the
totals in 2nd query)
[G] (Median in 2nd query)
[P] (Median in 2nd query)
[Maternal Age] (Avg in 2nd query)
[BW] (i.e. Birth Weight) (Avg in 2nd query)
Plus other Labels that are averaged in 2nd query


Any help with this would be much appreciated.

 

[Graham Mandeno]

Hi

I can't claim to have written the code you posted, but my DMedian function
(and probably many others') looks very similar

I think your confusion is in the fact that the Criteria argument is a
string. It is only a string because it becomes part of a SQL string to open
a recordset for determining the median.

The actual criteria can include WHERE conditions that compare non-string
fields, for example:
"[MyNumericField]<100"
or
"[MyDateField]>#01/01/2000#"

In your case, I expect the Criteria would be something like:
"Year([YourDateField])=" & [Fiscal year]

--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I want to calculate the medians for a string of values that are going to be
"group by" date, specifically the year (and I would like to group by the
most
current quarter as well, but let me just deal with the year first).

I have tried the median modules that were posted by John Nurick,
TimFerguson, Matthias Klay, Tom Wickerath, (in the Median Calculation post
dated 1/4/2007 to I believe 1/6/2007) and the generic one from Microsoft
as
well as a few other median modules that I found elsewhere. Not all of the
modules work for me the way that they are designed because I need to get
the
median for a set of values known as 'Gravita' (G) and 'Parity' (P) (which
are
single integers) and I would like to filter them by year or group them by
date. Matthias's module is close, but it relies upon text values which my
years are not (and I prefer to retain as years because I have grouped
other
values as this in the same query). I have this working copy of another
module
which is quite accurate, but displays the median value for the entire
column
of 'G' values regardless of grouping or filtering. The module is known as
"mdlmedian" and the aggregate is DMedian (which would probably explain why
it
works similar to DAvg in that it refuses to be grouped). (I am not sure of
who created this module but if anyone recognizes it as their please let me
know and I will of course include their names and whatever info they want
in
it):

Option Compare Database

Option Explicit
Function DMedian( _
Expr As String, _
Domain As String, _
Optional Criteria As String = "" _
) As Variant

Dim dbMedian As DAO.Database
Dim rsMedian As DAO.Recordset
Dim dblTemp1 As Double
Dim dblTemp2 As Double
Dim lngOffset As Long
Dim lngRecCount As Long
Dim strSQL As String
Dim varMedian As Variant

strSQL = "SELECT " & Expr & " AS Data " & _
"From " & Domain & " "
strSQL = strSQL & _
"Where " & Expr & " IS NOT NULL "
If Len(Criteria) > 0 Then
strSQL = strSQL & "AND (" & Criteria & ") "
End If
strSQL = strSQL & "ORDER BY " & Expr

Set dbMedian = CurrentDb()
Set rsMedian = dbMedian.OpenRecordset(strSQL)
If rsMedian.BOF = False And _
rsMedian.EOF = False Then
rsMedian.MoveLast
lngRecCount = rsMedian.RecordCount
If lngRecCount Mod 2 <> 0 Then
lngOffset = ((lngRecCount + 1) / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
varMedian = rsMedian("Data")
Else
lngOffset = (lngRecCount / 2) - 2
If lngOffset >= 0 Then
rsMedian.Move -lngOffset - 1
End If
dblTemp1 = rsMedian("Data")
rsMedian.MovePrevious
dblTemp2 = rsMedian("Data")
varMedian = (dblTemp1 + dblTemp2) / 2
End If
Else
varMedian = Null
End If

rsMedian.Close
Set rsMedian = Nothing
Set dbMedian = Nothing

DMedian = varMedian

End Function



Does anyone have any suggestions as to how to get it to work with a group?


These are my headings for the queries/table that are relevant:

[Fiscal Year] (2006, 2007, etc. are the values that fall within the
columns)
[Fiscal Quarter] (FY2009 Qtr 1, FY2009 Qtr2, etc. are the values that fall
within the columns) (Fiscal Year and Fiscal Quarter are Group by in the
totals in 2nd query)
[G] (Median in 2nd query)
[P] (Median in 2nd query)
[Maternal Age] (Avg in 2nd query)
[BW] (i.e. Birth Weight) (Avg in 2nd query)
Plus other Labels that are averaged in 2nd query


Any help with this would be much appreciated.