We help IT Professionals succeed at work.

Custom Aggregate Function in MS Access

midfde
midfde asked
on
1,556 Views
Last Modified: 2013-11-27
OK, I know aggregate functions MIN(...), AVG(...), SUM(...).
How can I write PROD(...) function that is similar to SUM?

         x1 + x2 + ... + xN
but computes product
         x1 * x2 *... xN

SELECT sum(salary), prod(rate) from whateverTable group by depID
Comment
Watch Question

Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
Take a look at this:
http://www.access-programmers.co.uk/forums/showthread.php?t=141914

The gist is to create your function to take in deptID for example, so usage like this:
SELECT sum(salary), prod(depID) from whateverTable group by depID

Or to make it more flexible, pass in tablename, columnname and where filters or just pass sql string itself.

Code sample below.  You would just keep multiplying by each rate you find in loop and then return.

Dim strSQL  As String
Dim db      As DAO.Database
Dim rs      As DAO.Recordset
 
  Set db = CurrentDb()
  
  strSQL = "SELECT ..." 
  Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)
 
  Do While Not rs.EOF
    'do your thing
    rs.MoveNext
  Loop
 
  set rs = nothing
  set db = nothing

Open in new window

CERTIFIED EXPERT
Top Expert 2010
Commented:
Unlock this solution and get a sample of our free trial.
(No credit card required)
UNLOCK SOLUTION

Author

Commented:
Thanks. I wonder what about custom aggregate functions in SQL Server environment
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a sample view!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.