aggregate function to calculate the median 1

[ddiamond]

Does anyone know how I can simulate an aggregate function to calculate the median of a group of values? My query would look something like the following:

select field1, sum(field2), avg(field2), median(field2)
from myTable
group by field1

As far as I can tell, access does not have an aggregate median function. If I coded a median function in VBA, I don't think I could include it in the select line as an aggregate. Any suggestions?

 

[Remou]

You could build your own function (UDF). For example:

Some SQL:

SELECT Statistics.Month, Sum(([SentTo])) AS [Sum Sent], fMedian("Statistics","Month",[Month],"SentTo") AS [Median Sent]
FROM Statistics
GROUP BY Statistics.Month;

UDF:

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)

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

 

[Remou]

Oops:

fMedian = (varMedian1 + rs.Fields(MedianFieldName)) / 2

 

[MichaelRed]

There is / are VB median UDF's in the FAQ'as in these (Tek-Tips) fora




MichaelRed

 

[Remou]

Hi MichaelRed
Can you say where, please? All I can find is in the Microsoft SQL Server: Programming Forum.

 

[MichaelRed]

not fair to ask an ancient crone of tired body and withered body for testimony in the wee dark hours ...


using "advanced" search and the term "basMedian" (I usually preface my VB/VBA UDFs with "bas") I found

thread705-918027

I don't think it is materially different than yours except, perhaps, in some options I included.




MichaelRed

 

[Remou]

Thank you, and I hope you were not troubled by the albatross.

 

[ddiamond]

Remou,

In your example, wouldn't you have to include the median function as part of the group by?

SELECT Statistics.Month, Sum(([SentTo])) AS [Sum Sent], fMedian("Statistics","Month",[Month],"SentTo") AS [Median Sent]
FROM Statistics, fMedian("Statistics","Month",[Month],"SentTo")
GROUP BY Statistics.Month;

 

[Remou]

No. It is an Expression.

 

[ddiamond]

Remou,

Works great. Performance is poor, however, when my data source is another query. It performs well when my source is a table. I will see if I can tune my query to resolve this. If not I will need to make use of a temp table (not a terrible thing...). Thanks for all of your help.