Finding Median - Gouped by field

B

Bob

I have been looking at the code for MedianFind(pDte As String) from the
following thread from UtterAccess.com: "Finding Median average grouped
by field".


I have been able to get it to run using Northwind no problem. I am
having some trouble though converting it to my specific purpose which
may be less complicated than the solution Bob (raskew) provided in the
thread.


Here are my specifics:


1) I only have one table (FactorTable) with two fields (neighborhood,
ratio).
2) I am looking for the Median of the 'ratio' field for EACH
'neighborhood'.


For Example:
Hood Ratio
501 1
501 2
501 3
601 2
601 4
601 6
etc....


I need a resulting table to spit out:


Hood Median
501 2
601 4


....or at least:


Hood Median
501 2
501 2
501 2
601 4
601 4
601 4
etc....


I have seen this questions posted in several forums (and groups) but
have yet to see an answer.

Can this be done????
Thanks alot in advance!
Sorry for the cross-posting.

Bob
 
R

Ron Weiner

Whew this is Butt Ugly but seems to work, and might even be flexible enough
to work in a variety of places. This solution consists of a Query that
calls a Public Function.

The query

SELECT Hood,
Avg(Ratio) AS AvgOfRatio,
FindMedian("tblFindMedian","Hood","'" & [Hood] & "'","Ratio") AS
MeanOfRatio
FROM tblFindMedian
GROUP BY Hood;

The Function

Public Function FindMedian(strTablename As String, strGroupByField As
String, _
strGroupByValue As Variant, strMedianField As String) As Variant

Dim rs As ADODB.Recordset
Dim strSql As String
Dim lngCount As Long
Dim varMedian As Variant

strSql = "Select * from " & strTablename & " Where " _
& strGroupByField & " = " & strGroupByValue _
& " Order By " & strMedianField
Set rs = New ADODB.Recordset
rs.Open strSql, CurrentProject.Connection, adOpenKeyset,
adLockOptimistic
If Not rs.EOF Then
rs.MoveLast
lngCount = rs.RecordCount
rs.MoveFirst
If lngCount = 1 Then
varMedian = rs(strMedianField)
Else
rs.Move CLng(lngCount \ 2)
varMedian = rs(strMedianField)
If lngCount Mod 2 = 0 Then
rs.MovePrevious
varMedian = (varMedian + rs(strMedianField)) / 2
End If
End If
Else
varMedian = Null
End If
FindMedian = varMedian
End Function

NOTE:
In my case the field Hood was a text field so it needed to be surrounded by
a single quote delimiter in the Sql Statement. If Hood was numeric thanthe
Sql would have looked like:

SELECT Hood,
Avg(Ratio) AS AvgOfRatio,
FindMedian("tblFindMedian","Hood", [Hood],"Ratio") AS MeanOfRatio
FROM tblFindMedian
GROUP BY Hood;

AND:
There is no error checking in the function as written, I leave it to you to
add this.

Ron W
 
Top