Microsoft Access Median function

[pfuesting]

Please help....I am trying to calculate the median based on a grouping. I am using the following code and it seems to be working. The only probelm is that there are also null values that should not be considered. How do I exclude the null records from the record count?

Function fMedian(SQLOrTable, GroupFieldName, GroupFieldValue, MedianFieldName)
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs1 = db.OpenRecordset(SQLOrTable, dbOpenDynaset)
If IsDate(GroupFieldValue) Then
GroupFieldValue = "#" & GroupFieldValue & "#"
ElseIf Not IsNumeric(GroupFieldValue) Then
GroupFieldValue = "'" & Replace(GroupFieldValue, "'", "''") & "'"
End If
rs1.Filter = GroupFieldName & "=" & GroupFieldValue
rs1.Sort = MedianFieldName

Set rs = rs1.OpenRecordset()
rs.Move (rs.RecordCount / 2)
rs.MovePrevious

If rs.RecordCount Mod 2 = 0 Then
varMedian1 = rs.Fields(MedianFieldName)
rs.MoveNext
fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
Else
fMedian = rs.Fields(MedianFieldName)
End If

End Function

 

[PHV]

I'd try this:
rs1.Filter = GroupFieldName & "=" & GroupFieldValue & " AND " & MedianFieldName & " IS NOT NULL"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[pfuesting]

Thanks for the response, that works great, but there is only one problem. It errors when only 1 record has a value.
How can I change it to only look at the records when it is >1?

 

[PHV]

I'd try this:

...
Set rs = rs1.OpenRecordset()
If Not (rs.BOF Or rs.EOF) Then
  rs.MoveLast
  If rs.RecordCount = 1 Then
    fMedian = rs.Fields(MedianFieldName)
  Else
    rs.Move (rs.RecordCount / 2)
    rs.MovePrevious
    If rs.RecordCount Mod 2 = 0 Then
      varMedian1 = rs.Fields(MedianFieldName)
      rs.MoveNext
      fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2
    Else
      fMedian = rs.Fields(MedianFieldName)
    End If
  End If
End If
rs.Close
Set rs = Nothing

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[strongm]

It's possible to do this in a single SQL query. Not sure how the performance compares, though.

 

[PHV]

It's possible to do this in a single SQL query
I'm curious to see that single JetSQL query.
strongm, could you please share this code ?

 

[strongm]

Sure. But may have to wait until tomorrow, because I'm not in the same office as the machine I put it together on.

 

[strongm]

Found time to knock a new version together

[blue]SELECT (Max(x.myData)  + Min(y.myData))/2  AS  Median
FROM [SELECT Top 50 percent myData from Table1 WHERE myData is not null ORDER BY MyData ]. AS x, [Select Top 50 percent myData from Table1 where myData is not null ORDER BY myData DESC]. AS y;[/blue]

 

[PHV]

Thanks strongm for sharing.
I often forget the TOP predicate.