如何在Access中统计单条记录内各状态字段的数量

方法1：使用查询中的IIF函数直接计算 #

针对每个状态，通过IIF函数判断每个datapoint字段是否匹配目标状态，再累加计数。示例SQL查询：

SELECT 
    DocumentID,
    IIF([datapoint1]="Confirmed",1,0) + IIF([datapoint2]="Confirmed",1,0) + ... + IIF([datapoint67]="Confirmed",1,0) AS ConfirmedCount,
    IIF([datapoint1]="Unconfirmed",1,0) + IIF([datapoint2]="Unconfirmed",1,0) + ... + IIF([datapoint67]="Unconfirmed",1,0) AS UnconfirmedCount,
    IIF([datapoint1]="Issue",1,0) + IIF([datapoint2]="Issue",1,0) + ... + IIF([datapoint67]="Issue",1,0) AS IssueCount
FROM 你的表名;

执行该查询后，可直接将其作为表单或报表的数据源，绑定统计字段展示结果。

方法2：编写VBA自定义函数简化计算 #

如果不想重复编写67个IIF语句，可创建自定义VBA函数遍历字段计数：

1. 按Alt+F11打开VBA编辑器，插入新模块，粘贴以下代码：

Function CountStatus(rec As Recordset, status As String) As Integer
    Dim fld As Field
    Dim count As Integer
    count = 0
    ' 遍历所有以datapoint开头的字段
    For Each fld In rec.Fields
        If Left(fld.Name, 9) = "datapoint" Then
            If fld.Value = status Then
                count = count + 1
            End If
        End If
    Next fld
    CountStatus = count
End Function

2. 在查询中调用该函数：

SELECT 
    DocumentID,
    CountStatus(你的表名.*, "Confirmed") AS ConfirmedCount,
    CountStatus(你的表名.*, "Unconfirmed") AS UnconfirmedCount,
    CountStatus(你的表名.*, "Issue") AS IssueCount
FROM 你的表名;

后续新增datapoint字段无需修改查询，函数会自动遍历计数。

方法3：转置表结构（规范化改造） #

原横向字段结构属于非规范化设计，转置为纵向结构后统计更灵活：

1. 创建新表，结构为：
   • DocumentID（与原表类型一致）
   • DatapointName（文本，存储datapoint1、datapoint2等字段名）
   • Status（文本，存储Confirmed/Unconfirmed/Issue）
2. 用VBA将原表数据转插入新表：

Sub TransposeTable()
    Dim db As Database
    Dim rsSource As Recordset
    Dim rsDest As Recordset
    Dim fld As Field
    
    Set db = CurrentDb
    Set rsSource = db.OpenRecordset("你的原表名")
    Set rsDest = db.OpenRecordset("新表名")
    
    Do While Not rsSource.EOF
        For Each fld In rsSource.Fields
            If Left(fld.Name, 9) = "datapoint" Then
                rsDest.AddNew
                rsDest!DocumentID = rsSource!DocumentID
                rsDest!DatapointName = fld.Name
                rsDest!Status = fld.Value
                rsDest.Update
            End If
        Next fld
        rsSource.MoveNext
    Loop
    
    rsSource.Close
    rsDest.Close
    Set rsSource = Nothing
    Set rsDest = Nothing
    Set db = Nothing
    MsgBox "转置完成"
End Sub

3. 转置完成后，用GROUP BY统计：

SELECT 
    DocumentID,
    Status,
    Count(*) AS StatusCount
FROM 新表名
GROUP BY DocumentID, Status;

若需一行展示三个状态，使用交叉查询：

TRANSFORM Count(*) AS StatusCount
SELECT DocumentID
FROM 新表名
GROUP BY DocumentID
PIVOT Status;

内容的提问来源于stack exchange，提问作者danf