Results 1 to 4 of 4

042202, 17:31 #1Registered User
 Join Date
 Mar 2002
 Posts
 23
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.

042202, 19:01 #2Registered User
 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.

042302, 17:03 #3Registered User
 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

042302, 20:14 #4Registered User
 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; 042302 at 20:23.