creating custom aggregate functions
Author 
Message 
Andre Borea #1 / 5

creating custom aggregate functions
Greetings all!!! Has anyone ever created their own custom aggregate function (to be used in a query..) Just like dsum or davg, I want to create a aggregate "product" (multiplication) function to be used in a query that performs this function based upon a range of values as determined by my "group by" criteria. Any hints or starters???? thanks very much, Andre Boreas

Sun, 06 Jan 2002 03:00:00 GMT 


Radu Lasca #2 / 5

creating custom aggregate functions
Hi Andre, Trevor (Best ???) has written some replacements for the agregate functions, see http://www.trevor.easynet.co.uk/AccFAQ/downloads.htm#baslookup I haven't looked at them in quite a while so I don't know if you can use it, but it's worth giving a try. Hth, Radu Lascae Quote:
> Greetings all!!! > Has anyone ever created their own custom aggregate function (to be used > in a query..) > Just like dsum or davg, I want to create a aggregate "product" > (multiplication) function to be used in a query that performs this > function based upon a range of values as determined by my "group by" > criteria. > Any hints or starters???? > thanks very much, > Andre Boreas

Sun, 06 Jan 2002 03:00:00 GMT 


Michel Wals #3 / 5

creating custom aggregate functions
Hi, You cannot add any function to the "list", but you can combine with arithmetic operators existing ones: choose "expression" (instead of GroupBy, Min, Max, Last, First, Count, Sum, .... ) and write the expression in the "field" line. As example, you can compute the Skewness if you know Count(X), Sum(X), Sum( X^2) and Sum(X^3). Sure, you can use any user defined function as usual, but that won't work as "aggregation" function, that won't make many records contribute to the aggregated result given the actual group(s), automatically. Hoping it may help, Vanderghast, Access MVP.
Quote: > Greetings all!!! > Has anyone ever created their own custom aggregate function (to be used > in a query..) > Just like dsum or davg, I want to create a aggregate "product" > (multiplication) function to be used in a query that performs this > function based upon a range of values as determined by my "group by" > criteria. > Any hints or starters???? > thanks very much, > Andre Boreas

Mon, 07 Jan 2002 03:00:00 GMT 


Andre Borea #4 / 5

creating custom aggregate functions
Thanks for your reply to my posting.... I understand using arithmetic expressions and then applying an aggregate function, but what I'm trying to do is have the values in a field multiply each other based upon the group by in another field. For example: table: firm A return GM 2 GM 3 GM 1 IBM 5 IBM 2 IBM 3 so the query result would be: GM 6 IBM 30 I just assuming I have to write this in VBA, or is there any other way to do this?? Seems simple. maybe I'm just not getting it. Thanks, Andre Boreas
Quote:
> Hi, > You cannot add any function to the "list", but you can combine with > arithmetic operators existing ones: choose "expression" (instead of GroupBy, > Min, Max, Last, First, Count, Sum, .... ) and write the expression in the > "field" line. As example, you can compute the Skewness if you know > Count(X), Sum(X), Sum( X^2) and Sum(X^3). > Sure, you can use any user defined function as usual, but that won't work as > "aggregation" function, that won't make many records contribute to the > aggregated result given the actual group(s), automatically. > Hoping it may help, > Vanderghast, Access MVP.
> > Greetings all!!! > > Has anyone ever created their own custom aggregate function (to be used > > in a query..) > > Just like dsum or davg, I want to create a aggregate "product" > > (multiplication) function to be used in a query that performs this > > function based upon a range of values as determined by my "group by" > > criteria. > > Any hints or starters???? > > thanks very much, > > Andre Boreas

Mon, 07 Jan 2002 03:00:00 GMT 


Michel Wals #5 / 5

creating custom aggregate functions
Hi, Antilog the Sum of the log: Exp(Sum(Log(X))) where X >0 Hoping it may help, Vanderghast, Access MVP.
Quote: > Thanks for your reply to my posting.... > I understand using arithmetic expressions and then applying an aggregate > function, > but what I'm trying to do is have the values in a field multiply each other > based upon the group by in another field. > For example: > table: > firm A return > GM 2 > GM 3 > GM 1 > IBM 5 > IBM 2 > IBM 3 > so the query result would be: > GM 6 > IBM 30 > I just assuming I have to write this in VBA, or is there any other way to do > this?? Seems simple. maybe I'm just not getting it. > Thanks, > Andre Boreas
> > Hi, > > You cannot add any function to the "list", but you can combine with > > arithmetic operators existing ones: choose "expression" (instead of GroupBy, > > Min, Max, Last, First, Count, Sum, .... ) and write the expression in the > > "field" line. As example, you can compute the Skewness if you know > > Count(X), Sum(X), Sum( X^2) and Sum(X^3). > > Sure, you can use any user defined function as usual, but that won't work as > > "aggregation" function, that won't make many records contribute to the > > aggregated result given the actual group(s), automatically. > > Hoping it may help, > > Vanderghast, Access MVP.
> > > Greetings all!!! > > > Has anyone ever created their own custom aggregate function (to be used > > > in a query..) > > > Just like dsum or davg, I want to create a aggregate "product" > > > (multiplication) function to be used in a query that performs this > > > function based upon a range of values as determined by my "group by" > > > criteria. > > > Any hints or starters???? > > > thanks very much, > > > Andre Boreas

Mon, 07 Jan 2002 03:00:00 GMT 


