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
)