Problem with Median Function

[silversurfer19]

Hi,

I am trying to write a query which aggregates data from a table by year, month and postcode, then produces info:average price of houses sold that year, min price, max price and median price. I've got functions for all of these, but the Median function I'm using is causing problems. If I use it a normal select query it works fine, but as soon as I try to use it as part of the group query it complains that I tried to exectute a query that does not contain MEDIAN(decPrice) as part of an aggregate function. I get the same message if I use MedianPrice. Fair enough, but it doesn't complain about this for any of the other functions, and if I do group by MEDIAN(decPrice) it says 'data mismatch in expression'

Here's the offending query, I'd appreciate any advice anyone can offer.

SELECT application_year, application_month, postout, AVG(decPrice) AS AvgDecPrice, MAX(decPrice) AS MaxDecPrice, MIN(decPrice) AS MinDecPrice, MEDIAN(decPrice)
FROM DatesSplit
GROUP BY postout, application_year, application_month, MEDIAN(decPrice)
ORDER BY application_Year, application_month, postout;

 

[MarkK]

Maybe you need an 'As' keyword in there, and a new name for the field?

MEDIAN(decPrice) As PriceMedian

???

 

[silversurfer19]

sorry I meant to mention that I have actually tried that as well, it still tells me that I haven't used MEDIAN(DecPrice) in the aggregate function

 

[Tigs81]

the problem is median is a user defined function not a standard function (eg sum avg etc). and its trying to do the calculations on each record, not a group of records as required by the group by statement.

Unfortunately i dont know how but a quick google search will help you to find out how to do it.

Just Google
user-defined aggregate functions ms access

 

[vbaInet]

Like Tigs has rightly pointed out, Median is not an aggregate function. If you look in the list of functions in the drop down you will not find Median there.

You can use a recordset to get the median and you can call it from within your query. But only do this if your query is returning one record. If you're using a form then call it from within the form instead. Here's what the function could look like:

Public Function GetMedian() as variant
     dim rs as dao.recordset
     dim firstVal as Double, recCount as Long

     set rs = currentdb.openrecordset("Select [decPrice] From DatesSplit Order By [decPrice];", dbopensnapshot)

     recCount = rs.recordcount

     if reccount < 2 then
          select case reccount
               case 0
                    getmedian = null
               case 1
                    rs.movefirst
                    getmedian = rs![decPrice]
          end select
          set rs = nothing
          exit function
     end if

     rs.movelast
     rs.movefirst

     if (reccount mod 2) = 0 then
          rs.move (reccount / 2) - 1
          firstVal = rs![decPrice]
          rs.movenext
          getmedian = (firstVal + rs![decPrice]) / 2
     else
          rs.move fix(reccount / 2) - 1
          getmedian = rs![decPrice]
     end if

     set rs = nothing
End Function

Aircode but it should work.