Calculating a median in a query

[caitliniag]

I am writing a statistical report for a weekly reporting database and need to include the median as well as the average in the report. The SQL for the report is:

SELECT vw_a.Assessor, Count(vw_a.Award) AS CountOfAward, Avg(vw_a.Award) AS AvgOfAward, Max(vw_a.Award) AS MaxOfAward, Min(vw_a.Award) AS MinOfAward
FROM vw_a
GROUP BY vw_a.Assessor
ORDER BY vw_a.Assessor;

vw_a is a query that contains two fields:
Assessor - Text
Award - Currency (there can be many Awards for each Assessor)

What I would like is to add another item to my select clause like:
Median(vw_a.Award) as MedianOfAward
with a VBA module to calculate the median. How can I do this?


Thanks
Caitlin

 

[PHV]

Doesen't the following suffice ?
, (Max(vw_a.Award)+Min(vw_a.Award))/2 AS MedianOfAward

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[caitliniag]

Thanks for the reply PH.

I hope I can clarify exactly what I need help with.

I can code an algorithm for calculating the median:
sort by award then
take the middle value if there are an odd number of values or the mean of the two middle values if there are an even number of values

So that part is OK but how do I incorporate my algorithm into a VBA function that uses DAO or ADO to get the data and then calculate the median. The function would need to calculate the median for each grouping of assessor.

I have seen code that can calculate the median for an entire field but I have not come across anything that can be applied in a query that uses group by.


Thanks
Caitlin

 

[MichaelRed]

PHV (amongst others?) has posted complete SQL queries to derive the Median of a dataset. A "Procedure" is not required. If you choose to use the procedure approach, you would simply call the procedure as a 'calculated field' within the query, passing the dataset to it. The DataSet would then be of your construction, most probably being either an array or a reference to the recordset & field of interest. You could, alternatively, either research these fora for median calculations and find the 'pure' SQL approach and incorporate it as a subquery within a calculated field.





MichaelRed