Median in a query

Topic

New Reply

Hi all,
I’ve been searching on the site (threads since 2001)-the easiest way to calculate a median in a query in four different columns of times that are recordered as, for instance, 21:50 in the query.
I have tried to reference the two suggested posts: 301021 and post 468774 -but the links are broken. I am not very good at VB – but I think I can work my way through it if I could get the info.
Thank you,
Vicky

Reply | Quote

Replies

Those do appear to be broken for sure – we lost some threads in the conversion to IPB. Doing a median using code is not a trivial exercise. You can find a sample database at Roger’s Access Library – it uses some fairly complex SQL statements to find the median. On the other hand, doing it in a manual fashion is pretty easy. Sort the table (or query) by the field in question from smallest to largest, take the record count and divide it by two, and go to that record number in the table using the navigation controls. The value in the field is the median. (If the record count, you have two values that you can consider as being the median – if the record count is odd, the median record is the result of the division by two rounded up.)

Reply | Quote

Thanks Wendell,
I downloaded the zip from your reference. Mine doesn’t sound as complicated but I will see what I can do.
I will give them the median the way you suggested temporarily but they will need to be able to run it monthly for performance improvement statistics and are not, let’s say, comfortable with computers enough to go into Access and manipulate -then report the numbers when needed.
Vicky

Reply | Quote

It might be easier to export your data to Excel – it does have a median function that is easy to use. Just another way to approach the problem.

Reply | Quote

Thanks, I will look into that as well…
Vicky

Reply | Quote

I was looking for something similar, and found this code on Microsoft’s website. I cleaned the code up to my own standards and it worked like a charm: http://support.microsoft.com/kb/210581

Reply | Quote