Prev: SQL question - Cross Tab query
Next: Getting total sales by customer
From: Zb Kornecki Zb on 11 Mar 2010 12:21
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.
From: ghetto_banjo on 11 Mar 2010 13:23
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
 | 
Pages: 1
Prev: SQL question - Cross Tab query
Next: Getting total sales by customer