access兑现自定义聚合函数

access实现自定义聚合函数
使用access对数据进行处理，遇到一个如何自定义聚合函数的问题：需要将字段进行连乘，类似于sum()的实现。


经过研究找到一种方法与大家分享：


access定义聚合函数需要使用vba函数：

Function sql(ByVal str As String) As String
    sql =  “select value from table where id = '”; + str +“'"
End Function

Function getResult(sql As String) As Double
    Static db As DAO.Database
    Dim rst As DAO.Recordset
    Set db = CurrentDb()
    Set rst = db.OpenRecordset(sql, dbOpenDynaset, dbReadOnly)
    rst.MoveLast
    rst.MoveFirst
    Dim result As Double
    result = 1#
    Do While Not rst.EOF
        If rst.Fields(0).Value Then
            result = result * rst.Fields(0).Value
        End If
        rst.MoveNext
    Loop
    Set rst = Nothing
    Set db = Nothing
    getResult = result
End Function

sql语句改为：
select
*,
getResult(sql(id))
from
(
select distinct id
from table
)