Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2002
    Posts
    23

    Question Unanswered: Median like function in Access???

    Hi,

    I have been trying to figure how to do this for several hour and don't know if it is possible. I am trying to run a query and want something like a median function but I know that access don't have that function. Is there a way to go around it. I don't want to use the average function.

    Example: Field Name: Zip Code, Rent
    Trying to find the median for Rent.

  2. #2
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Newbie,

    What do you mean by median!? Do you really want a median, because median, as i understand, statistical median is a median point in a succession of numbers, for example: the median of 7 is 4.

    So if you want this, it's just apply the mathematical function:

    (n+1)/n

    Now if in your query you want this by row, then put an expression

    ([rent value]+1)/[rent value]

    if you want by total, then it's better to use two querys or do it by code.

  3. #3
    Join Date
    Feb 2002
    Posts
    12
    I'm interested in the answer to this too. Here's an example, if I'm understanding the original message right:

    ZIP code Rent
    98501 400
    22512 1000
    45678 1500
    35275 600
    95115 20000


    The median of these numbers would be: 1000. It's the middle number when they are listed in order by rent. The median is helpful for "ignoring" very high and very low numbers when doing an average. In this example, the average is 4700 (add them up and divide by 5). Because the 20000 is so high, the average is outside the range of the other 4, whereas the median is in there.

    I was trying to do this recently and I couldn't figure it out, but I got around it by using iterative queries. I made a query that sorted my data for only those records whose value is within 10% of the average. Then I ran a query to find new averages based on these records only. Then I created a query based on that one that sorted my data for only those records whose value is within 10% of the new averages. After doing 5 iterations, the values stabilized somewhat and were "ignoring" the extreme highs and lows in my data.

    Any body have any other ideas?
    Diane

  4. #4
    Join Date
    Apr 2002
    Location
    Portugal
    Posts
    146
    Diane,

    How do you see a median value!?
    A midle number in a serie or a midle average number in a serie?

    Because, statistical median, it's only the midle number of a serie, in your example:

    ZIP code Rent
    98501 400
    22512 1000
    45678 1500
    35275 600
    95115 20000

    The median number is not 1000, but 1500. you just can't order the values, because rents are payed by month, week, or day, it doesn't matter, what i mean is that we are dealing with a specific time, which belongs the record, so the second record is previous to the third and fourth, so the value 1000 can't be puted in third place. If dates and the order of statistical collected data, are no problem, then you are right, ordering the values, you have a median with 1000.

    But i don't understand what do you mean with:

    "The median is helpful for "ignoring" very high and very low numbers when doing an average. In this example, the average is 4700 (add them up and divide by 5). Because the 20000 is so high, the average is outside the range of the other 4, whereas the median is in there.

    I was trying to do this recently and I couldn't figure it out, but I got around it by using iterative queries. I made a query that sorted my data for only those records whose value is within 10% of the average".

    No. In first place, median just marks the center of a distribuiton, and not for very high and very low, ignoring purpposes.
    Second, the median and average are totaly diferent. You can have an average of " -10500 " and a median of " 1 ".
    So when you say you sorted your values within 10% of the average, you were doing a wrong calculation for the median. Don't mistake median with "Standard Deviation" which is given by:

    SQR( SUM (i=1 to n) ((Valuei - Average)^2/(n - 1)) )

    which in this case will be: 8339.51
    This means that the deviation from the average is in 8339.51, due to the great value of 20000.

    What i mean with this, is: You can not calculate a median only with values with 10% of the average, because median doesn't have nothing to do with average. Median is calculated by: (n+1)/2,
    being n the size of the serie.

    If you have:
    -15; -30; 20; 45; 150; 20000; -800000

    The median value is: (7+1)/2 = 4, the value which the position in the serie ranges 4, is: 45 ->( Median of the distribuition. Ranging 3 to the left and 3 to the right, centered by 45. If you had a non integer median value, then the distribuition had no median, the left and right range would be equal without a number to center.)
    The average is: -111404.29
    Now if you sorted the values within 10% from -111404.29, you wouldn't have a median, because no values would be returned.


    See the point!?


    To calculate a median, you should count the records in the table, use the Dcount function. for example:
    Dim dbs as Database
    Dim n as single
    Dim median as single

    set dbs=CurrentDB()

    etc...etc...

    n=Dcount("[Rent]","[Table_Rent]")
    ' Or use the MoveFirst and MoveLast

    If ( (n+1) Mod 2 ) <> 0 then
    Msgbox "No median was find!" & VbCrlf & VbCrlf & "The result is a non integer number"
    else
    median=(n+1)/2

    ' After this use the property Move, so the records can move to the median record. Or just use the Dlookup function to search for the record wich the field "number of record" is the median.
    Last edited by Miguel; 04-23-02 at 21:23.

Posting Permissions

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