Custom aggregate function 
Author Message
 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



Sun, 30 Oct 2005 10:27:12 GMT  
 Custom aggregate function
AFAIK, you can not add your own to that list.

The only thing you can do is write a function to determine the value, then
return it.  That's what the code snippet you included does.

Chris


Quote:
> 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://support.microsoft.com/default.aspx?scid=http%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://support.microsoft.com/default.aspx?scid=fh;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



Mon, 31 Oct 2005 01:58:29 GMT  
 
 [ 2 post ] 

 Relevant Pages 

1. creating custom aggregate functions

2. Custom Aggregate Function Brainwave?

3. Custom Aggregate Function

4. Custom Aggregate Function for GROUP BY

5. new type of aggregate function (product function)

6. Aggregate functions

7. Domain Aggregate Function problem

8. Domain Aggregate function to return Collection/array

9. Help coding an aggregate dSum function

10. Aggregate function for values of fields in record

11. Determine if SQL-Aggregate functions are ready

12. Aggregate product function?

 

 
Powered by phpBB® Forum Software