## Unanswered: Median help? how do I get "Grouped" Medians?

Hi there... i am stuck. I found some lovely code on the web that helped me get the median of a data set.... unfortunately, I have been unable to figure out how to get it to do "grouping". I am using this information to get salary data of my company's employees.... so I need the median for all job code 1, then 2, etc.

There is one table holding all salary information.... I have a form where I am looking at each job's information, and I want a text box that essentially shows me the median for EACH record (job code)..... here is the code I snagged....

can anyone help?

Function Median(tName As String, fldName As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Double, y As Double, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & fldName & _
"] FROM [" & tName & "] WHERE [" & fldName & _
"] IS NOT NULL ORDER BY [" & fldName & "];")
'NOTE: To include nulls when calculating the median value, omit
'WHERE [" & fldName & "] IS NOT NULL from the example.
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
If Not ssMedian Is Nothing Then
ssMedian.Close
Set ssMedian = Nothing
End If
Set MedianDB = Nothing
End Function

And here is what i tried to do..... doesn't really work ... sigh

Private Sub Form_Current()
Dim curMedian As Currency
curMedian = Median("PayDetail_Median", "FteSalary")
Text25.Value = curMedian
Debug.Print curMedian
Set ssMedian = Nothing
End Sub

Thanks in advance if anyone can help me "reset the median" for each record...