Access comes loaded with mathematical functions for all kinds of things, and it's easy to determine the sum, average, maximum or minimum of a field in a table, using either a query or some VBA. But what about some more complicated functions? What if you want to, for example, know the median value of a field in a table? For those of you whose maths is a bit rusty, the median is the value below which 50% of the data falls - the physical midpoint in a range, if you will. Access does provide a MEDIAN() worksheet function but this only works on a series of numbers rather than table data. It's still useful for illustrating exactly what a median is though - check out these examples:
MEDIAN(1, 2, 3, 4, 5) equals 3 MEDIAN(1, 2, 3, 4, 5, 6) equals 3.5, the average of 3 and 4
So anyway, you want to work out the median of a table field. The following function takes two parameters, the name of the table and the name of the field, both as strings. Note that if you have spaces in either name (naughty you) you'll need to enclose them in square brackets before you pass them to the function. Anyway, the function just returns the median value.
Function fctnMedian(strTable As String, strField As String) As Variant Dim dbs As DAO.Database, rst As DAO.Recordset 'You must add a reference to the DAO Object Library too Dim booEven as Boolean Set dbs = DBEngine(0)(0) Set rst = dbs.OpenRecordset("SELECT * FROM " & strTable & " ORDER BY " & strField) If rst.EOF = False Then rst.MoveLast booEven = (rst.RecordCount Mod 2 = 0) 'Is there an even number of records in the recordset? rst.PercentPosition = 50 'Rounds down if there is an even number of records... fctnMedian = rst.Fields(strField) If booEven Then rst.MoveNext fctnMedian = (fctnMedian + rst.Fields(strField)) / 2 'Takes average of this & the next value up End If End If rst.Close Set rst = Nothing Set dbs = Nothing End Function