I've gotten the medianf fuction that is pasted on these boards working in the attached db example. The problem I am running into now is the medianf function (correctly) takes the median for an entire table at once. My dataset example looks like:

type num
t1 1
t1 2
t1 3
t1 6
t1 6
t1 100
t2 1
t2 2
t2 200

I'm trying to end up with median t1 = 4.5, t2 = 2. With the way that function works, it sets them both to 3 which is the median of the entire set. I have the query saved in here already setup so far. Any ideas on getting the grouping to work properly?

Your Function MedianF doesn't contain a filter for the group. This means that it will perform the action for all records that match the current criterea.

Because I don't know where you call the function in your database I changed your function to:

Function MedianF(pTable As String, pfield As String, Optional pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************

Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single

If Len(pgroup) > 0 Then
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close
End Function

Perfect, got it working. Thanks!

I was able to get your function to work but I noticed that it only returns the value of one type (t1, t2) in the table. Is there a function to have the query return the median value for each type and show ALL median values by type in the same query (refer to original sample date in this string)?

I was able to get your function to work but I noticed that it only returns the value of one type (t1, t2) in the table. Is there a function to have the query return the median value for each type and show ALL median values by type in the same query (refer to original sample date in this string)?

By grouping Your query you should be able to get the results you want.
Something like this could work.
SELECT YourTableName.Type, First(MedianF("YourTableName","num",[type])) AS GroupMedian, First(MedianF("YourTableName","num")) AS TotalMedian
FROM YourTableName
GROUP BY YourTableName.Type;

This is great. Thanks! Is is possible to group by multi levels in the query?

This is great. Thanks! Is is possible to group by multi levels in the query?

You have to change the function to be able to group on multi levels. Depending on how many fields you want to use for grouping you need to ad these in the function like I did for the first group.

Can you provide an example in how to add additional grouping levels (to the Function Module and to the Query)

Hi,

I am trying to get this to work in a query but when I run the query I get a Run-time error '3061': Too few parameters. Expected 1.

I am a VBA newbie, what do you put in the function for the "pgroup" string? From the above dataset would you put in the actual type value, i.e. "T1". I think I understand that but not sure how to modify the code to get medians for every type in the output table. Any help is appreciated, thanks

OK I got this to work, but where do you put the SQL code calculates median for all types? Is this a seperate query?

Your Function MedianF doesn't contain a filter for the group. This means that it will perform the action for all records that match the current criterea.

Because I don't know where you call the function in your database I changed your function to:

Function MedianF(pTable As String, pfield As String, Optional pgroup As String) As Single
'*******************************************
'Purpose: Return median value from a recordset
'Coded by: raskew
'Inputs: ? medianF("Orders", "Freight",[Value of Grouping field]) <enter>
'Output: 41.36 (may vary according to how much
' you've fiddled with this table).
'*******************************************

Dim rs As Recordset
Dim strSQL As String
Dim n As Integer
Dim sglHold As Single

If Len(pgroup) > 0 Then
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE type= '" & pgroup & "' and " & pfield & ">0 Order by " & pfield & ";"
Else
strSQL = "SELECT " & pfield & " from " & pTable & " WHERE " & pfield & ">0 Order by " & pfield & ";"
End If
Set rs = CurrentDb.OpenRecordset(strSQL)
rs.MoveLast
n = rs.RecordCount
rs.Move -Int(n / 2)

If n Mod 2 = 1 Then 'odd number of elements
MedianF = rs(pfield)
Else 'even number of elements
sglHold = rs(pfield)
rs.MoveNext
sglHold = sglHold + rs(pfield)
MedianF = sglHold / 2
End If
rs.Close
End Function



Excellent peice of code. I altered this slightly to calculate mean in my queries. Thanks for the great code!