时间戳

首页 > 解决方案 > 除了 DAvg 之外，还有其他方法可以找到平均值吗？

vba - 除了 DAvg 之外，还有其他方法可以找到平均值吗？

问题描述

我在工作中继承了这个访问数据库，我正在寻找一种更快的方法来编写一些代码。目前运行它需要将近 1.5 分钟，我需要它更快。

目前我将它全部设置为 DAvg 和 DCount 但问题在于它基本上打开和关闭了它使用它的每一行的查询。就其本身而言，它很好，但我需要这样做 36 次。

Private Sub Form_Load()
On Error GoTo errhndlr
    Me.Visible = True
    Dim FY_EngMiss, Prev_EngMiss, LThree_EngMiss, LNine_EngMiss, FY_DraftMiss, Prev_DraftMiss, LThree_DraftMiss, LNine_DraftMiss As Integer
    Dim FY_Eng, Prev_Eng, LThree_Eng, LNine_Eng, FY_Draft, Prev_Draft, LThree_Draft, LNine_Draft As Integer
    Dim FY_Fups, Prev_Fups, LThree_Fups, LNine_Fups, FY_pjs, Prev_Pjs, LThree_Pjs, LNine_Pjs, EngMissed As Integer
    Dim FY_Quote, Prev_Quote, LThree_Quote, LNine_Quote As Double
    Dim FY_Avg, Prev_AVG, LThree_Avg, LNine_Avg As Double
    Dim SummaryDate, FiscalDate, LThreeDate As Date
    Dim rst As DAO.Recordset

    Me.Printer.Orientation = acPRORLandscape
    Me.SummaryDate = Forms!ReportForm.Form.SDate
    Me.FiscalDate = Forms!ReportForm.Form.FiscalStartDate
    SummaryDate = CDate(DateValue(Me.SummaryDate))
    FiscalDate = CDate(DateValue(Me.FiscalDate))
    LThreeDate = CDate(DateValue(DateAdd("m", -1, Me.SummaryDate)))

    'Quality Metrics

    Me.JW_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.CD_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.FM_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.JP_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.MB_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.BE_DAYRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")

    Me.JW_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.CD_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.FM_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.JP_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.MB_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")
    Me.BE_DAYQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and Format([CompletedDate], 'm/d/yyyy') = '" & SummaryDate & "'")

    Me.JW_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.CD_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.FM_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.JP_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.MB_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.BE_MRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")

    Me.JW_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.CD_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.FM_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.JP_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.MB_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")
    Me.BE_MQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE' and [CompletedDate] Between #" & SummaryDate + 1 & "# And #" & LThreeDate & "#")

    Me.JW_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW'")
    Me.CD_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD'")
    Me.FM_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM'")
    Me.JP_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP'")
    Me.MB_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB'")
    Me.BE_YRNK = DAvg("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE'")

    Me.JW_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JW'")
    Me.CD_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'CD'")
    Me.FM_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'FM'")
    Me.JP_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'JP'")
    Me.MB_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'MB'")
    Me.BE_YQTY = DCount("AvgOfProjectRank", "Email_Ranking", "[Approver] = 'BE'")
Exit Sub
errhndlr:
    Call Errhndler.ErrorHandler(Err.Number, Err.Description, Me.Name, "Form_Load")
End Sub

这也是 Email_Ranking 查询的 SQL 文本。

SELECT DISTINCTROW
       archived_email.ProjectID, 
       archived_email.subject, 
       archived_email.approver, 
       archived_email.completeddate, 
       Avg(timestamp.projectrank) AS AvgOfProjectRank 
FROM   fy_current_external, 
       archived_email 
       INNER JOIN [timestamp] 
               ON archived_email.projectid = timestamp.projectid 
GROUP  BY archived_email.projectid, 
          archived_email.subject, 
          archived_email.approver, 
          archived_email.completeddate, 
          fy_current_external.date_value 
HAVING ( ( ( archived_email.approver ) = "mb" 
            OR ( archived_email.approver ) = "fm" 
            OR ( archived_email.approver ) = "jw" 
            OR ( archived_email.approver ) = "be" 
            OR ( archived_email.approver ) = "cd" 
            OR ( archived_email.approver ) = "jp" ) 
         AND ( ( archived_email.completeddate ) >= [date_value] ) 
         AND ( ( LEFT([archived_email]![projectid], 2) ) = "e-" 
                OR ( LEFT([archived_email]![projectid], 2) ) = "pl" ) ) 
ORDER  BY archived_email.completeddate DESC;

它正在搜索的查询包含 6,000 多个项目。运行此代码的时间平均为 88 秒。此代码位于多个位置。我想将此时间减少至少一半，最好减少到 10 秒或更短。

标签： vbams-access

解决方案

做这样的事情：

Dim SummaryDate As String
Dim FiscalDate As String
Dim LThreeDate As String
Dim Sql As String
Dim rst As DAO.Recordset

Me.Printer.Orientation = acPRORLandscape
Me.SummaryDate = Forms!ReportForm.Form.SDate
Me.FiscalDate = Forms!ReportForm.Form.FiscalStartDate
SummaryDate = Format(Me.SummaryDate.Value, "yyyy\/mm\/dd")
FiscalDate = Format(Me.FiscalDate, "yyyy\/mm\/dd")
LThreeDate = Format(DateAdd("m", -1, Me.SummaryDate.Value), "yyyy\/mm\/dd")

Sql = "Select Approver, Avg(AvgOfProjectRank) As PrjRnk, Count(*) As PrjQty " & _
      "From [Email_Ranking] " & _
      "Where DateValue([CompletedDate]) = #" & SummaryDate & "# " & _
      "Group By Approver"

Set rst = CurrentDb.OpenRecordset(Sql)

' Loop the approvers and their controls.
While Not rst.EOF
    Me(rst!Approver.Value & "_DAYRNK").Value = rst!PrjRnk.Value
    Me(rst!Approver.Value & "_DAYQTY").Value = rst!PrjQty.Value
    rst.MoveNext
Wend
rst.Close

推荐阅读