如何在Access 2010中将单个字段的所有记录相乘 [英] How to multiply all records of a single field in Access 2010

问题描述

我想将Access 2010中单个字段的所有记录相乘. 我尝试mult(Field name)和product(field name)无济于事. 谁能帮助我，Access中是否有任何功能?

I want to multiply all records of a single field in Access 2010. I tried mult(Field name) and product(field name) to no avail. Can anyone help me is there any function in Access to do so?

示例:
我有一个具有字段S1的表

Example:
I have a table having the field S1

S1 
---
557
560
563
566
569
572
575
578
581

，输出应该在另一个具有字段结果的表中

and the output should be in another table having the field result

Result
--------
6.25E+24

推荐答案

不幸的是，Access SQL中没有PRODUCT()函数允许您这样做

Unfortunately, there is no PRODUCT() function in Access SQL that would allow you to do

SELECT PRODUCT([S1]) AS Result FROM [YourTable]

但是，您可以使用VBA来滚动自己的" DProduct()域聚合函数，类似于内置的DSum()函数:

However, you can use VBA to "roll your own" DProduct() domain aggregate function, similar to the built-in DSum() function:

Option Compare Database
Option Explicit

Public Function DProduct(Expr As String, Domain As String, Optional criteria) As Variant
    Dim SQL As String, Result As Double
    Dim cdb As DAO.Database, rst As DAO.Recordset
    On Error GoTo DProduct_Error
    Set cdb = CurrentDb
    SQL = "SELECT " & Expr & " AS Expr1 FROM [" & Domain & "]"
    If Not IsMissing(criteria) Then
        SQL = SQL & " WHERE " & criteria
    End If
    Set rst = cdb.OpenRecordset(SQL, dbOpenSnapshot)
    If rst.BOF And rst.EOF Then
        DProduct = Null
    Else
        Result = 1
        Do Until rst.EOF
            Result = Result * rst!Expr1
            rst.MoveNext
        Loop
        DProduct = Result
    End If
    rst.Close
    Set rst = Nothing
    Set cdb = Nothing
    Exit Function

DProduct_Error:
    DProduct = Null
End Function

使用问题中的样本数据进行测试

Testing with the sample data in your question

?DProduct("S1", "YourTable")
 6.24666417941851E+24

这篇关于如何在Access 2010中将单个字段的所有记录相乘的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！