除了DAvg之外，还有其他方法可以找到平均值吗? [英] Is there another way of finding the Average besides DAvg?

查看：50
本文介绍了除了DAvg之外，还有其他方法可以找到平均值吗?的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！

问题描述

我在工作时继承了此访问数据库，我正在寻找一种编写一些代码的更快方法.目前运行此程序需要将近1.5分钟，而我需要更快得多.

I inherited this access database at work and I am looking for a faster way to write some code. Currently it takes almost 1.5 minutes to run this and I am needing it to be much faster.

当前，我将它们全部设置为DAvg和DCount，但是这样做的问题是，它基本上会打开和关闭使用它的每一行的查询.单靠它可以，但是我需要做36次.

Currently I have it all set up as DAvg and DCount but the problem with this is that it basically opens and closes the query for each line that its used it. On its own its fine but I need to do this 36 times.

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文本.

Here is the SQL Text for the Email_Ranking Query as well.

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秒或更短.

The query that it is searching is contains over 6,000 items. The time to run this code is 88 seconds on average. This code is in multiple locations. I would like to reduce this time by at least half, preferably down to 10 seconds or less.

推荐答案

执行以下操作:

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

这篇关于除了DAvg之外，还有其他方法可以找到平均值吗?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！

查看全文
相关文章
其他开发最新文章
热门教程
热门工具
登录 关闭
扫码关注1秒登录
发送“验证码”获取 | 15天全站免登陆