By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,480 Members | 3,113 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,480 IT Pros & Developers. It's quick & easy.

Clue for writing user-defined aggregate functions

P: n/a
neb

Dear member of the forum,

Ms access has built-in aggregate function like:
-Sum, Max, First, Avg, ...

Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to post)

*I'm running a total query, of the form

Select VendorID, MyAggregate(VendorData) as MyAggregate
From Tbl;
Group By VendorID;

where the function "MyAggregate" cannot be replicate by any combination
of existing aggregate functions.

Nov 13 '05 #1
Share this Question
Share on Google+
10 Replies


P: n/a
While a liitle of out date, Trevor Best has an example of creating your own
domain functions at
http://easyweb.easynet.co.uk/~trevor...les.htm#doamin

--
Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

"neb" <ga***@yahoo.com> wrote in message
news:11**********************@f14g2000cwb.googlegr oups.com...

Dear member of the forum,

Ms access has built-in aggregate function like:
-Sum, Max, First, Avg, ...

Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to post)

*I'm running a total query, of the form

Select VendorID, MyAggregate(VendorData) as MyAggregate
From Tbl;
Group By VendorID;

where the function "MyAggregate" cannot be replicate by any combination
of existing aggregate functions.

Nov 13 '05 #2

P: n/a
neb
Trevor Best Solution enable to write Domain aggregate function, such as
:
-DLookup, DMax, DSum,...
But, as far as I know, those function can only be apply on prexisting
object (table or query).

A solution that enables to write user-defined aggregate function,
seems more flexible to me.

Is it possible or should I restraint myself to domain aggregate
function?

*The flexibility can be size by comparing syntax :

- Domaine aggregate function :
Dfunction(Field, ExistingTable,criteria), where criteria is
like a "where" clause

- Aggregate function :
MyAggregate(Field) + All the sql power

Nov 13 '05 #3

P: n/a

"neb" <ga***@yahoo.com> schreef in bericht news:11**********************@f14g2000cwb.googlegr oups.com...

Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to post)

*I'm running a total query, of the form

Select VendorID, MyAggregate(VendorData) as MyAggregate
From Tbl;
Group By VendorID;


I am not sure if this is what you are asking but ...
Of course you can write your own function and use it in a Totals-query.

Select VendorID, MyAggregate(VendorData) as MyTotal 'don't use the function-name here for the sake of clarity...
From Tbl 'remove ';' here ...
Group By VendorID;

Arno R

Nov 13 '05 #4

P: n/a
neb
It's not straigh foward to use user-defined function in totals-query.

With the query below Access will report the following error :

-You tried to execute a query that does not include the specified
expression <name> as
part of an aggregate function. (Error 3122)

where <name>="MyAggregate(VendorData)"

*Query :
Select VendorID, MyAggregate(VendorData) as MyTotal
From Tbl
Group By VendorID;

Nov 13 '05 #5

P: n/a

"Arno R" <ar***********@tiscali.nl> wrote in message
news:42*********************@dreader2.news.tiscali .nl...

"neb" <ga***@yahoo.com> schreef in bericht
news:11**********************@f14g2000cwb.googlegr oups.com...

Is it possible to build user-defined aggregate?
(if you have any clue, do not hesitate to post)

*I'm running a total query, of the form

Select VendorID, MyAggregate(VendorData) as MyAggregate
From Tbl;
Group By VendorID; I am not sure if this is what you are asking but ...
Of course you can write your own function and use it in a Totals-query.

Select VendorID, MyAggregate(VendorData) as MyTotal 'don't use the function-name here for the sake of clarity...From Tbl 'remove ';' here ...
Group By VendorID;

Arno R

I'm also intrigued by this. How would you structure your own function for
an aggregate query? I can see for Domain Aggregate, it would not be
difficult. But an aggregate function needs to "operate" on each group
within the query.

Say for instance, instead of SUM(), you wanted SUMofTopHalf(). Something
that would sum only the largest half of the values in each group. It isn't
apparent to me how to go about doing that.

Nov 13 '05 #6

P: n/a

"neb" <ga***@yahoo.com> schreef in bericht news:11**********************@g14g2000cwa.googlegr oups.com...
It's not straigh foward to use user-defined function in totals-query.

With the query below Access will report the following error :

-You tried to execute a query that does not include the specified
expression <name> as
part of an aggregate function. (Error 3122)

where <name>="MyAggregate(VendorData)"

*Query :
Select VendorID, MyAggregate(VendorData) as MyTotal
From Tbl
Group By VendorID;


I did test it, but obviously I did something wrong I guess ...
Can't repeat the original query now. Sometimes these things are puzzling me ...

Change the query to:
Select VendorID, First(MyAggregate(VendorData)) as MyTotal
From Tbl
Group By VendorID;
Or:
SELECT VendorID, MyAggregate(VendorData) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(VendorData);

--
Hope this helps
Arno R
Nov 13 '05 #7

P: n/a
neb
Using the query below is not really an aggregation, because the
function "MyAggregate" will act on individual records, not on a group
of records (eg : all records having the same VendorID).

*Query
From Tbl

Group By VendorID;
Or:
SELECT VendorID, MyAggregate(VendorData) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(VendorData);

Nov 13 '05 #8

P: n/a
neb
One solution I've found (thanks to the member of the forum), is :

- Build a query that include only grouping fields
- Retrieve datas to aggregate using a GetRows method based on the
previously isolated grouping field
- Do the aggregate calculation

*An implementation of this solution can be as follow :

1. Build a query that isolate grouping field
grpQuery = Select VendorID from tbl Group By VendorID;

2. Build a query where the aggregate are calculated
Select VendorID, MyAggregate(getvariant(sql(VendorID))) as MyTotal
from grpQuery;
where
MyAggregate : function that acts upon a variant object/array
getvariant : function that retrieve datas specified by an sql
statement (see code below)
sql(x) : is a function that create an sql statement (string)
according to argument x
eg: sql(10) = "Select VendorData From tbl where VendorID=10;"

'###code :
' getvariant(sql) : extract an object as a variant array; object is
specified by a sql statement (a string)

Public Function getvariant(sql As String) As Variant
Static db As DAO.Database
Dim rst As DAO.Recordset
Set db = CurrentDb()
Set rst = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
rst.MoveLast
rst.MoveFirst
getvariant = rst.GetRows(rst.RecordCount)
End Function

Nov 13 '05 #9

P: n/a

"neb" <ga***@yahoo.com> schreef in bericht news:11*********************@f14g2000cwb.googlegro ups.com...
One solution I've found (thanks to the member of the forum), is :

- Build a query that include only grouping fields
- Retrieve datas to aggregate using a GetRows method based on the
previously isolated grouping field
- Do the aggregate calculation

*An implementation of this solution can be as follow :

1. Build a query that isolate grouping field
grpQuery = Select VendorID from tbl Group By VendorID;

2. Build a query where the aggregate are calculated
Select VendorID, MyAggregate(getvariant(sql(VendorID))) as MyTotal
from grpQuery;

<snip code>

I think you don't need your grpQuery.
Is this:

SELECT VendorID, MyAggregate(getvariant(sql(VendorID))) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(getvariant(sql(VendorID))) ;

not working ???
I would be interested also to see your function MyAggregate(varinput)

Arno R

Nov 13 '05 #10

P: n/a
neb
The query below will work, but suffer from a lack of efficiency.
The "Group By" statement will force the function "MyAggregate" to be
evaluate on each records of the field "VendorID".
A "Group By" statement is effectively invoke only after each value of
the "Select ... From Tbl" statement has been evaluate.

SELECT VendorID, MyAggregate(getvariant(sql(Ven*dorID))) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(getvariant(sql(Ven*dorID))) ;

As the input of the function "MyAggregate" is a variant array, the
specific task of the function is entirely up to you.
For example, if you want to do the Sum of First half for the Vendor
with ID=10 :

1. sql(10) = "Select VendorData From tbl where VendorID=10;"

2. getvariant(sql(10)) = Variant Array with shape 0 To n
n is the number of records in VendorData associate to VendorID
eg : with n=3 : getvariant(sql(10)) = (100, 200, 300, 400)

3. MyAggregate(getvariant(sql(Ven*dorID))) = SumOfFirstHalf(
VariantArray_shape(0 To n) )
eg : SumOfFirstHalf( (100, 200, 300, 400) ) = 300 (any code that
achieve the calculation is good here)

Nov 13 '05 #11

This discussion thread is closed

Replies have been disabled for this discussion.