Finding a Median

[Eric D. Braden]

I'm trying to write a query that will return a median for various
values taken from a previous query. I've seen some suggestions in my
searching, but I haven't been able to get them working. They are also
all from before 2003 and refer to Access 97 and 2000.

Has any functionality been added to 2003 for this? Or is there a non-
code-based way to do it? I've seen it suggested to write a code to
open the query, sort it, find the total number of records, divide it
in half, then seek out the middle record using that value. I'm still
very green when it comes to code, though.

Thank you in advance for any assistance.

 

[Michel Walsh]

It has not been added, neither in Jet, neither in MS SQL Server.

You can write a query that does it (Joe Celko did proposed a solution some
times ago), but the solution with the recordset is faster. Note that part of
the problem is that the procedure is different if there is an odd or an even
number or elements. If there is an even number of elements, the definition
is to average the two middle values. A good VBA solution is to seek at N/2
rounded up, and to get that value in an accumulator, move to the next record
ONLY if N is even, then, in all case, read the value of the record you are
on, and add it to the accumulator, and finally, divide that result by two
(indeed, if N is odd, you have read twice the same value, not having done
any record move). Indeed, if N=40, you will read record at rank 20 and at
rank 21; while if N=41, you read the record at rank 21, twice.

That VBA solution should be faster than an SQL one since the second step is
almost free in VBA, moving to a next record, while SQL will need to reach
the whole table (or half of it) a second time. That is not a very hard
'proof', but it is enough, for me, to not try to seek an SQL solution to
that problem which would be faster than VBA.



Vanderghast, Access MVP