Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.)
    Wendell

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    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

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    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.
    Wendell

  5. #5
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks, I will look into that as well...
    Vicky

  6. #6
    New Lounger
    Join Date
    Dec 2009
    Location
    Birmingham, Alabama, USA
    Posts
    7
    Thanks
    1
    Thanked 0 Times in 0 Posts
    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •