Calculating the Median for specified groups

[Guest]

Hi All,

I am trying to find the median value for a group of records. For example, I
want to find the median age, grouped by Quarter, Location, Gender so that you
get the median age for Females in location 1 for Q2 2005 and median age for
Males in Location 1 for Q2 2005, etc. We used this to calculate the median,
but can not find the way to get the grouping to calculate correctly. Can
anyone help?

Function Median(sql As String, fldName As String) As Single
Dim ssMedian As DAO.Recordset
Dim RCount%, i%, x%, y%, OffSet%
Set ssMedian = CurrentDb.Openrecordset(sql)

ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName)
ssMedian.MovePrevious


y% = ssMedian(fldName)
Median = (x% + y%) / 2
End If
ssMedian.Close
End Function

 

[James A. Fortune]

Hi All,

I am trying to find the median value for a group of records. For example, I
want to find the median age, grouped by Quarter, Location, Gender so that you
get the median age for Females in location 1 for Q2 2005 and median age for
Males in Location 1 for Q2 2005, etc. We used this to calculate the median,
but can not find the way to get the grouping to calculate correctly. Can
anyone help?

Function Median(sql As String, fldName As String) As Single
Dim ssMedian As DAO.Recordset
Dim RCount%, i%, x%, y%, OffSet%
Set ssMedian = CurrentDb.Openrecordset(sql)

ssMedian.MoveLast
RCount% = ssMedian.RecordCount
x% = RCount% Mod 2
If x% <> 0 Then
OffSet% = ((RCount% + 1) / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
Median = ssMedian(fldName)
Else
OffSet% = (RCount% / 2) - 2
For i% = 0 To OffSet%
ssMedian.MovePrevious
Next i
x% = ssMedian(fldName)
ssMedian.MovePrevious


y% = ssMedian(fldName)
Median = (x% + y%) / 2
End If
ssMedian.Close
End Function

Try the technique I used in:

http://groups.google.com/group/comp.databases.ms-access/msg/7fb6eb508f22fa9d

James A. Fortune
(e-mail address removed)