Custom aggregate function

I am trying to write a custom function that can be used

in an aggregate query (i.e., with "totals" or other

aggregations shown and a "GROUP BY" clause in the SQL.

The article below from Microsoft illustrates how to code

the type of function I am interested in, but it does not

work in these type of "GROUP BY" queries. Access has

built in functions for average, sum, min, max, etc., but

does not have median or other statistical functions. I

am trying to build functions to calculate median, 25%ile,

and 75%ile within a "GROUP BY" query. Any advice?

Thanks sincerely,

Chris Fry

----------

ACC2000: How to Use Code to Derive a Statistical Median

The information in this article applies to:

Microsoft Access 2000

This article was previously published under Q210581

Advanced: Requires expert coding, interoperability, and

multiuser skills.

This article applies only to a Microsoft Access database

(.mdb).

SUMMARY

This article shows you how to create a Visual Basic for

Applications procedure to open a table, read the data,

and find the statistical median. The median is a measure

of central tendency, another "middle" measurement of a

data set, like the mean or average). The data set

consisting of the numbers 1, 2, 3, 6, and 100 has a

median of 3, the middle of the set. The data set

consisting of the numbers 1, 2, 6, and 10 has a median of

4, the middle of the set [(2 + 6) / 2 = 4].

MORE INFORMATION

Microsoft provides programming examples for illustration

only, without warranty either expressed or implied,

including, but not limited to, the implied warranties of

merchantability and/or fitness for a particular purpose.

This article assumes that you are familiar with the

programming language being demonstrated and the tools

used to create and debug procedures. Microsoft support

professionals can help explain the functionality of a

particular procedure, but they will not modify these

examples to provide added functionality or construct

procedures to meet your specific needs. If you have

limited programming experience, you may want to contact a

Microsoft Certified Partner or the Microsoft fee-based

consulting line at (800) 936-5200. For more information

about Microsoft Certified Partners, please visit the

following Microsoft Web site:

http://www.*-*-*.com/ %3a%2f%

2fwww.microsoft.com%2fpartner%2freferral%2f

For more information about the support options that are

available and about how to contact Microsoft, visit the

following Microsoft Web site:

http://www.*-*-*.com/ ;EN-

US;CNTACTMS

NOTE: The sample code in this article uses Microsoft Data

Access Objects. For this code to run properly, you must

reference the Microsoft DAO 3.6 Object Library. To do so,

click References on the Tools menu in the Visual Basic

Editor, and make sure that the Microsoft DAO 3.6 Object

Library check box is selected.

To create a procedure that determines the statistical

median of a set of numbers, follow these steps:

Create a module and type the following line in the

Declarations section:

Option Explicit

Type or paste the following procedure:Function Median

(tName As String, fldName As String) As Single

Dim MedianDB As DAO.Database

Dim ssMedian As DAO.Recordset

Dim RCount As Integer, i As Integer, x As Double, y As

Double, _

OffSet As Integer

Set MedianDB = CurrentDB()

Set ssMedian = MedianDB.Openrecordset("SELECT [" &

fldName & _

"] FROM [" & tName & "] WHERE [" & fldName &

_

"] IS NOT NULL ORDER BY [" & fldName & "];")

'NOTE: To include nulls when calculating the median

value, omit

'WHERE [" & fldName & "] IS NOT NULL from the example.

ssMedian.MoveLast

RCount% = ssMedian.RecordCount

x = RCount Mod 2

If x <> 0 Then

OffSet = ((RCount + 1) / 2) - 2

For i% = 0 To OffSet

ssMedian.MovePrevious

Next i

Median = ssMedian(fldName)

Else

OffSet = (RCount / 2) - 2

For i = 0 To OffSet

ssMedian.MovePrevious

Next i

x = ssMedian(fldName)

ssMedian.MovePrevious

y = ssMedian(fldName)

Median = (x + y) / 2

End If

ssMedian.Close

MedianDB.Close

End Function

How to Use the Median() Function

Create a form and add a text box control where you want

to display the median values of a data set. Set the

ControlSource property of the text box control to the

following: =Median("<TableName>", "<FieldName>")

The value of this control is the median of the data set.

Another way to use this function is to call it from

within another function that compares the median from

different data sets. For example: Function

CompareMedians()

Dim MyDB as Database

.

.

.

X = Median("<TableName>", "<FieldName>")

Y = Median("<TableName>", "<FieldName>")

If X > Y Then Debug.Print "The median for X is

greatest."

End Function

Last Reviewed: 4/22/2003

Keywords: kbhowto kbProgramming KB210581