472,342 Members | 2,511 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,342 software developers and data experts.

Clue for writing user-defined aggregate functions

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
10 11748
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
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

"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
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

"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

"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
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
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

"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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: James D. Marshall | last post by:
I have a need to write user gathered input to a file as a single line. I not found any tutorials or code examples that would accomplish this. Would...
15
by: Douglas Garstang | last post by:
All, I posted a newsgroup question here a few weeks back, asking some questions that related to my 10 year quest (so far) to understand...
6
by: msnews.microsoft.com | last post by:
Hi all, I was trying to find an easy way to write in the standard configuration (like app.config) xml-based files. I found classes that can be...
3
by: Will Pittenger | last post by:
I have C# .NET Standard 2003 (not Professional) but did not a Installer project template in the project template list. How would I use that? I...
2
by: Stephen | last post by:
I have code which checks whether a datagrid is empty and if it is it shows a panel on my page. If its not empty then Im using the server.transfer to...
5
by: Michelle Stone | last post by:
Hi everybody I am writing a simple asp.net application using form authentication. I store the list of all users and their passwords in an SQL...
1
by: Bob Dufour | last post by:
Using the .NET eventlog class to log application errors that are viewable in the Windows event viewer. We write to a custom log. WE can Write our...
12
by: Chris Springer | last post by:
I'd like to get some feedback on the issue of storing data out to disk and where to store it. I've never been in a production environment in...
1
by: =?ISO-8859-1?Q?Andr=E9?= Wyrwa | last post by:
Hei, I know this kind of stuff has probably been asked a couple of times already, and did quite a bit of googling about it, but the answers i...
4
by: ARC | last post by:
I have a form with a first name and last name field right next to each other. The last name field has no label. I'm using: defaultvalue="" and...
0
by: concettolabs | last post by:
In today's business world, businesses are increasingly turning to PowerApps to develop custom business applications. PowerApps is a powerful tool...
0
better678
by: better678 | last post by:
Question: Discuss your understanding of the Java platform. Is the statement "Java is interpreted" correct? Answer: Java is an object-oriented...
0
by: teenabhardwaj | last post by:
How would one discover a valid source for learning news, comfort, and help for engineering designs? Covering through piles of books takes a lot of...
0
by: Kemmylinns12 | last post by:
Blockchain technology has emerged as a transformative force in the business world, offering unprecedented opportunities for innovation and...
0
jalbright99669
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
0
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
0
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
0
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
0
by: Matthew3360 | last post by:
Hi, I have been trying to connect to a local host using php curl. But I am finding it hard to do this. I am doing the curl get request from my web...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.