select queries that calculate the Median base on group by

[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.

 

[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