473,554 Members | 2,282 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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(Ven dorData) as MyAggregate
From Tbl;
Group By VendorID;

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

Nov 13 '05 #1
10 11910
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.co m> wrote in message
news:11******** **************@ f14g2000cwb.goo glegroups.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(Ven dorData) as MyAggregate
From Tbl;
Group By VendorID;

where the function "MyAggregat e" 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,c riteria), where criteria is
like a "where" clause

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

Nov 13 '05 #3

"neb" <ga***@yahoo.co m> schreef in bericht news:11******** **************@ f14g2000cwb.goo glegroups.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(Ven dorData) 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(Ven dorData) 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>="MyAggre gate(VendorData )"

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

Nov 13 '05 #5

"Arno R" <ar***********@ tiscali.nl> wrote in message
news:42******** *************@d reader2.news.ti scali.nl...

"neb" <ga***@yahoo.co m> schreef in bericht
news:11******** **************@ f14g2000cwb.goo glegroups.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(Ven dorData) 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(Ven dorData) 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.co m> schreef in bericht news:11******** **************@ g14g2000cwa.goo glegroups.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>="MyAggre gate(VendorData )"

*Query :
Select VendorID, MyAggregate(Ven dorData) 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(MyAggrega te(VendorData)) as MyTotal
From Tbl
Group By VendorID;
Or:
SELECT VendorID, MyAggregate(Ven dorData) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(Ven dorData);

--
Hope this helps
Arno R
Nov 13 '05 #7
neb
Using the query below is not really an aggregation, because the
function "MyAggregat e" 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(Ven dorData) AS MyTotal
FROM Tbl
GROUP BY VendorID, MyAggregate(Ven dorData);

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(get variant(sql(Ven dorID))) 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.OpenRecordse t(sql, dbOpenDynaset, dbReadOnly)
rst.MoveLast
rst.MoveFirst
getvariant = rst.GetRows(rst .RecordCount)
End Function

Nov 13 '05 #9

"neb" <ga***@yahoo.co m> schreef in bericht news:11******** *************@f 14g2000cwb.goog legroups.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(get variant(sql(Ven dorID))) as MyTotal
from grpQuery;

<snip code>

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

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

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

Arno R

Nov 13 '05 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
1736
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 this require cgi scripting or vb net script or is JavaScript capable?
15
7727
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 pointers. Someone suggested I write a simple emulator. Part of his post is below. I would have emailed him directly but a valid email wasn't included.
6
2604
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 used to read this information (in System.Configuration) like the ConfigurationSettings.AppSettings property, but I was not able to find anything to write app settings in these files. Isn't this kind...
3
1817
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 have one DLL that my program uses. (I needed controls derived from other controls of my own creation. Without the DLL, the form editor would not accept the base class as valid. ---------- Will...
2
5869
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 go to another page so as im able to use the items in an array list. My problem is that I also have had to incorporate some Javascript to see whether the user is ok with moving forward. Everything...
5
2062
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 Server database table. My client recently told me that he wants me to do something through which only one user can login using any given account name. I mean to say, for example, when a user...
1
1144
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 error messages to it OK but we want the Current user name to be displayed in the field USER in the evcent log. We can't find out how this value would be written to this field in the log entry. We...
12
3742
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 programming so you'll have to bear with me... My question is about storing data in a database. Yes I understand that you can link to a database in your program and read and write to the database etc...
1
2576
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 found were either unsatisfying or not specific enough to what i want to do: I'm writing on a (Linux only) python daemon that runs as regular user. Under certain conditions the daemon should...
4
1511
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 defaultvalue =="" for new recs so a beginner-type user knows what goes where. After looking at a customer's database, already the very first record, sure enough, has last name = "". I wish there...
0
7529
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language...
0
7806
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
8047
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
0
6151
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
1
5439
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes...
0
5159
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert...
0
3553
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
2021
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1139
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.

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.