How to create custom aggregated function

[ben_mtl]

Hi

I’m trying to create my own custom aggregated function:
Ex:
SELECT Obs_Date, Item, MyAvg(Px)
FROM Sales
GROUP BY Obs_Date, Item;

I know how to create a vba function that will deal with single record… but somehow, I can’t figure how to create one that will deal with aggregated data (ie: receive and array populated by an sql statement… a little bite like the “avr()” function does)

Thanks in advance for your help

Ben

 

[boblarson]

What are you trying to aggregate? What is the definition of what you want?

 

[ben_mtl]

Well.. the "aggregated" come from the error message that I'm getting when i try to pass a array to my function:
"You tried to execute a query that does not include the specified exception 'MyAvg(Px)' as part of an aggregate function"


Public Function MyAvg(Px() As Double) As Double
...
End Function


The bottom line is that i do not know the syntax to use to create such a function

 

[boblarson]

Again, I will ask (sorry but you didn't answer the question) -

What is your function MyAvg supposed to do? Are you collecting a bunch of different values and then operating on them? If so, you need to pass an array of objects to it and then work on it. But in your query you are showing yourself calling the function with a single value. So, I want to know what the function is supposed to do, what are you intending and how does it relate to the rest of your query?

 

[ben_mtl]

No Problem Bob, here are the details
I have 2 parameters that could be observed multiple times for an item on a given day. I then want to take all the observations and make a fancy average out of it.

Ex:
Item Par1 Par2
A 10 11
A 10.5 12
A 9 14
B 4 5
B 6 7

Solution:
A 10.3
B 5


It will simply the development of my application if I was able to call my fancy average function within my sql statement (a little bite like when I call the normal average function)

 

[Banana]

The part about "Par1" and "Par2" concerns me somehow as this suggests that the table may not normalized. If Par1 and Par2 are fundamentally same measures that happens to measure a different aspect (e.g. length and width as example), then it may make more sense to split those column into their own table so it's:

Item ParType ParValue
A 1 10
A 1 10.5
A 1 9
A 2 11
A 2 12
A 2 14
B 1 4
B 1 6
B 2 5
B 2 7

Then that way you don't need any custom aggregates.

And to answer the original question technically - AFAIK, Jet/ACE does not support custom aggregate functions. The closest thing you can get to a custom aggregate value is to build your own VBA function that's somehow similar to DMax/DAvg/DSum and walk the recordset to calculate your own aggregation. However, this probably will be very slow in performance.