Multiple Median Calculation

[MPgov]

Hello,

I am a novice to Access. I have been reviewing some of the Q&A threads and I
am not sure if they apply to my question. I am trying to calculate the median
of several categories as follows:

Class Name Rate Age MedianRate MedianAge
1 A $20 21 $17.50 19.5
1 B $15 18 $17.50 19.5
2 C $17 19 $17.00 19.0
3 D $18 22 $18.50 21.0
3 E $18.50 Null $18.50 Null
3 F $19 20 $18.50 21.0


I have Class, Person, Rate, and Age in the same table. But, I want to
calculate the Median rate and age for each Class only. I believe I would have
to create a query for each Class, but would these same codes listed
throughout the Q&A threads work for my example? I am grateful for any advice.

 

[Lord Kelvan]

do you want the data to display

class medianrate medianage
1 $17.50 19.5
2 $17.00 19.0
3 $18.50 21

or

Class Name Rate Age MedianRate MedianAge
1 A $20 21 $17.50 19.5
1 B $15 18 $17.50 19.5
2 C $17 19 $17.00 19.0
3 D $18 22 $18.50 21.0
3 E $18.50 $18.50 21.0
3 F $19 20 $18.50 21.0

Regards
Kelvan

 

[Lord Kelvan]

create a new query in design view

click cancel on the add tables window

click view in the menu bar

click sql view

replace what is there with

SELECT classtable.class, Avg(classtable.rate) AS MedianRate,
Format(Avg([age]),"00.0") AS MedianAge
FROM classtable
GROUP BY classtable.class;

then run the query

that will give you the first option
the second option is actually incorrect data

hope this helps

Regards
Kelvan

 

[pietlinden]

Median and Average are NOT the same. There's code on the MS Website to
get median. It's the middle number in a sorted list of values. If
there are an even number of members in the group, it's the average of
the two middle numbers.

Here's code for calculating a Median

http://support.microsoft.com/kb/95918

 

[Dale Fye]

But to use that code, you will need to add a criteria to the function, so
that you can pass it the Class. Then you could write your query like:

SELECT Class,
Median("yourTableName", "Rate", "[Class] = " & [Class]),
Median("yourTableName", "Age", "[Class] = " & [Class])
FROM yourTable

--
HTH
Dale

Don''t forget to rate the post if it was helpful!

email address is invalid
Please reply to newsgroup only.