Grouped Median

  • Thread starter Thread starter Nich
  • Start date Start date
N

Nich

I created a function to get the median of a recordset, but
it returns the same value for all records. I need it to
calculate the median based on the grouping as well, so
need a new median function or a way to fix mine.

This is how the detail is:

Partner1 January 5
Partner1 January 6
Partner1 January 7
Partner1 February 3
Partner2 January 4
Partner2 January 5
Partner2 February 9


I want it to return

Partner1 January 6
Partner1 February 3
Partner2 January 4.5
Partner2 February 9


Any advice?
 
Can't fix your code if you don't post it ;)

This SQL statement will return the MEAN average of your
grouped data. You could base a recordset on it and
retrieve your data from that.

SELECT Partner, Month, Avg(Amount) AS AvgOfAmount
FROM myTable
GROUP BY Partner, Month;

If you really want the Median then try using the following
function;

Function GetMed(strP as String, strM as String) as Variant
Dim rst as DAO.Recordset
Dim strSQL as String
Dim dblLow as Double
Dim dblHigh as Double
strSQL = "SELECT myTable.* FROM myTable " _
& "WHERE Partner='" & strP & "' " _
& "And Month='" & strM & "' " _
& "ORDER BY Amount"
Set rst = CurrentDb.OpenRecordset(strSQL,dbOpenSnapshot)
If rst.EOF Then
GetMed = Null
Else
dblLow = rst("Amount")
rst.MoveLast
dblHigh = rst("Amount")
GetMed = dblLow + ((dblHigh - dblLow)/2)
End If
rst.Close
Set rst = Nothing
End Function

Use it in code like this;
x = GetMed("Partner1","January")

Don't forget to make a reference to the DAO 3.6 Object
Library from your visual basic project. Good luck.
 
Back
Top