在Access VBA编程数据透视表 [英] Programming a PivotTable in Access VBA

问题描述

让我通过把事情在上下文中第一个开始。 我有三个问题，并希望能接受她在我遇到一个问题，一些帮助。 我发展的地方风险评估进行了具体的工作场所/活性组合使用的物质的应用程序。我想要做的是创造一个矩阵报告如下：

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)

所有这些值可以通过查询来检索。我试图用一个交叉表查询，但似乎它们只能用于汇总数据。我woundering如果我的假设是正确的，因为这是我找上了微软的网站后得出的结论。但是，由于我没有经验丰富的艾策斯程序员，我可能是错的。

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.

根据我的假设我想通数据透视表是要走的路。我发现 http://msdn.microsoft.com为例/en-us/library/aa662945(office.11​​).aspx 我试图复制。其原因编程一个是使得可以基于在一个形式的用户的标准来产生一个数据透视表。

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).aspx that 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.

在code以下是在上述链接中给出的示例code稍有不同。

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

现在的问题是如下：我收到一个错误13类型不匹配设置PivotFieldSet和我无能，什么错误的时候。的形式与适当的记录创建，并在PivotTableView打开，这就是，只要它的推移。我希望能找回一些建议，哪些问题可以。

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.

此外，可能有另一种方式来实现我做一个矩阵的目标描述？由于出口到Excel将需要打印出这是一个弯路，我宁愿最终用户就不必把数据透视表。

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.

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

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

推荐答案

你所厌恶的使用交叉表查询是没有根据的。如果你有最多一个观察的每一行/列的组合，那么你的汇总数据仅仅是本身的价值：如果您使用MIN（），MAX（）或SUM（）也没关系。

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().

例：对于样本数据[Risk_Data]

Example: For sample 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

查询

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

返回

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

这篇关于在Access VBA编程数据透视表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！