Median Problem

[Bruion]

Could someone help me with this module for Microsoft Access. At the moment, the module calculates the median of my entire population of patients. However, in my report I have patients grouped by carepath, hospital, month. Could I incorporate these groupings so that my report gives me the carepath median, hospital median, and carepath by month median.


Public Function median(qryCarepaths As String, LOS As String) As Single
Dim MedianDB As DAO.Database
Dim ssMedian As DAO.Recordset
Dim RCount As Integer, i As Integer, x As Integer, y As Integer, _
OffSet As Integer
Set MedianDB = CurrentDb()
Set ssMedian = MedianDB.OpenRecordset("SELECT [" & LOS & _
"] FROM [" & qryCarepaths & "] WHERE [" & LOS & _
"] IS NOT NULL ORDER BY [" & LOS & "];&quot
'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(LOS)
Else
OffSet = (RCount / 2) - 2
For i = 0 To OffSet
ssMedian.MovePrevious
Next i
x = ssMedian(LOS)
ssMedian.MovePrevious
y = ssMedian(LOS)
median = (x + y) / 2
End If
ssMedian.Close
MedianDB.Close
End Function

 

[MichaelRed]

Cross posted. Asked and answered elsewhere.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[Bruion]

Thank you for your reply, I am going to look into it more when I get another free moment.

Thank you very much again for the input.

 

[MichaelRed]

Bruion,

Still looking at some of this. I think there are additional problems, but you are CORRECT in the averaging of the two values for recordsets (e.g. lists) where the recordcount is EVEN (at least according to Ms. Excel).

It appears (to me) that the decrementing of the record

[tab]For I = 0 to Offset ...

should actually be For I = 1 to Offset

w/o your table & query, I can't test some of this, so it is possible that I am missing something, but the construction of the sql for ssmedian does not look like most of the ones I concoct. Then again, I don't pass query names and field names to functions like this. To do some testing for my own purposes, I removed theese arguments and just referenced fields from my sub set query directly in the function. The sql is somewhat easier to 'read' this way, but you probably have reason(s) for doing it the way you have set it up.

I think you should set up a test case to check that the values ou are getting are REALLY what you should be getting. Something like the offset being off by 1 could skew the results.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over