Help creating custom aggregate function for query

[ejc00]

Hello,

I am trying to build an aggregate function in VBA to be used in an Access query. Here is an example of the data that I want to query:

Field1 Field2
Test1 High
Test1 Medium
Test1 Low
Test2 Low
Test2 Medium

Here are the results I would like from my aggregate (Group By) query:

Field1 Field2
Test1 High
Test2 Medium

When I create a function, it is evaluated for every record. If someone could share the code behind Access's native "Sum" or "Count" functions, I could develop my code from that.

I realize that a way around this would be to assign a numberic value associated with each Field2 value and then take the MIN or MAX. But I would like to figure out how to create my own aggregate function. The VBA function would be a series of If statements to determine the appropriate value.

Thanks,
ejc

 

[SkipVought]

Hi,

Aggregaton, uses the Group By field values as Distinct values and based on those values, Counts, Sums, Averages, finds the Min or Max...

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ejc00]

Skip, is there a way to create a custom function, similar to "Max" or "Min"?

 

[SkipVought]

Sure, but what's the reason?

Sort the table

loop thru the items testing the by items

keep testing the aggragation value for a min or max value.

Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]

 

[ejc00]

In the case where a common Field1 like "Test1" has the values "High", "Medium", and "Low", and I want it to return "High". "High" is neither a Min or a Max... can you give me an example of a custom aggregate function? Or do you know where I can find the code behind inherent functions like Sum, Count, Min, Max, etc.?

 

[SkipVought]

Well, in the testing ctiteria you have
[tt]
3 - High
2 - Medium
1 - Low
[/tt]



Skip,
[sub]
When a diminutive clarvoyant had disappeared from detention, headlines read...
Small Medium at Large[/sub]