Calculating the Median for specified groups

S

soppelt

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
 
J

James A. Fortune

soppelt said:
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)
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculation for Median 3
median 31
Calculate the median value before a specified list 0
YES 0
Calculating median in a group by query 4
Median in Report 1
Median in Report 1
Please help 6

Top