select queries that calculate the Median base on group by

Z

Zb Kornecki

I have 2 questions.
1)Is it possible to create additional aggregate functions in MS Access 2007?
If yes than this leads to: 2) I would like my select quries to calculate
the Median and mode (along with the mean) of each group by row.

I can export the entire table to Excel and do it this way but it would be
easier to have Access handle it so i can just export the summary table.
 
G

ghetto_banjo

Well you can calculate the Mode directly by a query, see this example:

SELECT TOP 1 Table1.Data
FROM Table1
GROUP BY Table1.Data
ORDER BY Count(Table1.Data) DESC;

This query grabs the first value, when the values are sorted in
descending order of their Count.


For the median, i believe you would have to use some VB code. Using a
recordset is the way to go in this case. This site has an example
that you can copy and paste to fit your needs.

http://www.fabalou.com/Access/Modules/recordset_median.asp
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Calculate the median value before a specified list 0
Calculating median in a group by query 4
group and median 1
Trying to create a report that will list unmatched records by Ticket Primary Key 0
How do I calculate the median/other stats 7
How to calculate the median in a GROUP BY query 10
Clicking link tasks again increases duration 0
Calculat Median for each data group? 3

Top