在 Access VBA 中编程数据透视表

Programming a PivotTable in Access VBA

提问by Makki

Let me start off by putting things in context first. I have three questions and was hoping to recieve some help on a problem I've encountered. I am developing an application where risk assessments are carried out on substances used in specific workplace/activity combinations. What I wanted to do was create a matrix report as follows:

让我首先将事情放在上下文中。我有三个问题，希望能就我遇到的问题得到一些帮助。我正在开发一个应用程序，对特定工作场所/活动组合中使用的物质进行风险评估。我想做的是创建一个矩阵报告如下：

[Workplace Name] (as a column heading)

[工作场所名称]（作为列标题）

[Activity Name] (as a row heading)

[活动名称]（作为行标题）

[Risk Assessment Score] (as the data)

【风险评估评分】（作为数据）

All of these values can be retrieved via a query. I tried using a crosstab query but it seems they can only be used for summary data. I was woundering If my assumption is correct, since that is what I concluded after looking on the microsoft website. However, since I am no seasoned Acces programmer I could be wrong.

所有这些值都可以通过查询检索。我尝试使用交叉表查询，但它们似乎只能用于汇总数据。我很受伤如果我的假设是正确的，因为这是我在查看微软网站后得出的结论。然而，由于我不是经验丰富的 Acces 程序员，所以我可能是错的。

Based on my assumption I figured a PivotTable was the way to go. I found an example on http://msdn.microsoft.com/en-us/library/aa662945(office.11).aspxthat I tried to replicate. The reason for programming one is so that a PivotTable can be generated based on the users criteria in a form.

基于我的假设，我认为数据透视表是要走的路。我在http://msdn.microsoft.com/en-us/library/aa662945(office.11​​).aspx上找到了一个 我试图复制的示例。编程的原因是可以根据表单中的用户标准生成数据透视表。

The code below is a slight variation on the sample code given in the aforementioned link.

下面的代码与上述链接中给出的示例代码略有不同。

Private Sub cmdPivotTable_Click()

Dim strDefaultName As String
Dim strRecordSource As String

'Create an empty form with a PivotTable
'default view and a record source
strRecordSource = "SELECT *" _
& " FROM ((OEL RIGHT JOIN Substance ON OEL.OEL_ID = Substance.OEL_ID) INNER JOIN" _
& " ((Product INNER JOIN Proportions ON (Product.Product_Name = Proportions.Product_Name) AND (Product.Product_ID = Proportions.Product_ID) AND (Product.Product_Name = Proportions.Product_Name) AND (Product.Product_ID = Proportions.Product_ID)) INNER JOIN (STM_Workplace INNER JOIN (Respiratory_PPE INNER JOIN (STM_LocalControls INNER JOIN (STM_Diffuse INNER JOIN ((STM_Vent_NF_FF INNER JOIN Workplace ON (STM_Vent_NF_FF.STM_Vent_FF_ID = Workplace.STM_Vent_NF_ID) AND (STM_Vent_NF_FF.STM_Vent_FF_ID = Workplace.STM_Vent_NF_ID) AND (STM_Vent_NF_FF.STM_Vent_FF_ID = Workplace.STM_Vent_FF_ID)) INNER JOIN (STM_Activity_Score INNER JOIN (Activity INNER JOIN (PBMCode INNER JOIN Product_Activity ON PBMCode.ID_PBMCode = Product_Activity.ID_PBMCode) " _
& " ON Activity.Activity_ID = Product_Activity.ID_Activity) ON STM_Activity_Score.STM_ActivityScore_ID = Activity.STM_ActivityScore_ID) ON (Workplace.Workplace_ID = Product_Activity.Workplace_ID) AND (Workplace.Workplace_ID = Product_Activity.Workplace_ID)) ON STM_Diffuse.STM_Diffuse_ID = Workplace.STM_Diffuse_ID) ON STM_LocalControls.STM_LocalControls_ID = Workplace.STM_LocalControls_ID) ON (Respiratory_PPE.STM_PPE_ID = PBMCode.STM_PPE_ID) AND (Respiratory_PPE.STM_PPE_ID = PBMCode.STM_PPE_ID)) ON STM_Workplace.STM_Workplace_ID = Workplace.STM_Workplace_ID) ON (Product.Product_Name = Product_Activity.Product_Name) AND (Product.Product_ID = Product_Activity.Product_ID) " _
& " AND (Product.Product_Name = Product_Activity.Product_Name) AND (Product.Product_ID = Product_Activity.Product_ID)) ON (Substance.Substance_Name = Proportions.Substance_Name) AND (Substance.Substance_ID = Proportions.Substance_ID)) INNER JOIN Risk_Assessment ON (Substance.Substance_Name = Risk_Assessment.Substance_Name) AND (Substance.Substance_ID = Risk_Assessment.Substance_ID) " _
& " WHERE Product_Activity.ID_Product_Task = Product_Activity_ID"

strDefaultName = CreatePivotTable(strRecordSource)

'Rename the form from its default name
Dim strFormName As String
strFormName = "TestCreatePivotTableForm"
If (AssignPivotTableName(strDefaultName, strFormName)) = False Then
    Exit Sub
End If

'Configure the PivotTable
ConfigurePivotTable (strFormName)
End Sub



Public Function AssignPivotTableName(strDefaultName As String, strFormName As String As Boolean
Dim acc1 As AccessObject

AssignPivotTableName = True

For Each acc1 In CurrentProject.AllForms
    If acc1.Name = strFormName Then
        MsgBox "Choose a form name other " & _
            "than '" & strFormName & "' that " & _
            "does not match an existing form."
        AssignPivotTableName = False
        DoCmd.DeleteObject acForm, strDefaultName
        Exit Function
    End If
Next acc1

DoCmd.Rename strFormName, acForm, strDefaultName
End Function



Public Function CreatePivotTable(strRecordSource As String) As String
Const acFormPivotTable = 3
Dim frm1 As Access.Form

Set frm1 = CreateForm
frm1.DefaultView = acFormPivotTable
frm1.RecordSource = strRecordSource

CreatePivotTable = frm1.Name
DoCmd.Close acForm, CreatePivotTable, acSaveYes
End Function



Public Sub ConfigurePivotTable(strFormName As String) 
Dim fst1 As PivotFieldSet

DoCmd.OpenForm strFormName, acFormPivotTable
Set frm1 = Forms.Item(strFormName)

With frm1.PivotTable.ActiveView
    ***Set fst1 = .FieldSets("Product_Name")***
    .FilterAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("WorkPlace_Name")
    .ColumnAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("Activity_Name")
    .RowAxis.InsertFieldSet fst1
    Set fst1 = .FieldSets("Imission_Score")
    .DataAxis.InsertFieldSet fst1
End With

DoCmd.Close acForm, frm1.Name, acSaveYes
End Sub

The problem is as follows: I am getting an error 13 Type mismatch when setting the PivotFieldSet and am clueless as to what is going wrong. The form is created with the appropriate recordset and opens in PivotTableView and that's as far as it goes. I was hoping to retrieve some suggestions as to what the problem could be.

问题如下：我在设置 PivotFieldSet 时收到错误 13 Type mismatch 并且我对出了什么问题一无所知。表单是用适当的记录集创建的，并在 PivotTableView 中打开，就这样。我希望检索一些关于问题可能是什么的建议。

Also, could there be another way to achieve my goal of making a matrix as described? Since an export to Excel would be needed to print out the PivotTable which is a detour I'd rather the end-user wouldn't have to take.

另外，是否有另一种方法可以实现我制作矩阵的目标？由于需要导出到 Excel 来打印数据透视表，这是一个绕路，我宁愿最终用户不必采取。

Thanks in advance for any suggestions or replies to my post.

在此先感谢您对我的帖子的任何建议或回复。

采纳答案by Gord Thompson

Your aversion to using a Crosstab query is unfounded. If you have at most one observation for each row/column combination then your "summary data" is just the value itself: it doesn't matter if you use Min(), Max(), or Sum().

您对使用交叉表查询的厌恶是没有根据的。如果您对每一行/列组合至多有一个观察值，那么您的“汇总数据”只是值本身：使用 Min()、Max() 或 Sum() 并不重要。

Example: For sample data [Risk_Data]

示例：对于样本数据 [Risk_Data]

Activity        Workplace        Risk_Ratio
--------------  ---------------  ----------
Activity_Name   Workplace_Name          1.2
Activity_Name   Workplace_Name2         2.3
Activity_Name2  Workplace_Name          3.4
Activity_Name2  Workplace_Name2         4.5

the query

查询

TRANSFORM Max(Risk_Ratio) AS MaxOfRisk_Ratio
SELECT Activity
FROM Risk_Data
GROUP BY Activity
PIVOT Workplace

returns

返回

Activity        Workplace_Name  Workplace_Name2
--------------  --------------  ---------------
Activity_Name              1.2              2.3
Activity_Name2             3.4              4.5