我该如何解决这个查询？

Question

我收到运行时错误 3122：

您试图执行一个不包含指定表达式 count(*)*t2.Daily_Charge_HKD 作为聚合函数一部分的查询

我想在查询中执行以下操作：

I想要按 event_plan_code 对 Opt_In_Customer_Record 中的所有记录进行分组，并计算每个代码的总计数，然后我通过 t1.event_plan_code = t2.event_plan_code 引用 daily_charge 表中的 daily_charge，并将 daily_charge 乘以每个代码的总计数代码的

这是我的代码：

Private Sub btnGenDaily_Click()
    Dim filename As String
    Dim prefix As String
    Dim qryDef As DAO.QueryDef
    Dim dbs As DAO.Database

    Set dbs = OpenDatabase(CurrentDb.Name)

    If IsNull(txtInputPath.value) Then
        MsgBox "Please enter a valid input file location."
    Else
        If FileExists(txtInputPath.value) Then
            If IsNull(txtOutputPath3.value) Then
                MsgBox "Please enter a valid output file location."
            Else
                prefix = GetFileNamePrefix(txtInputPath.value)

                sql = "select t1.event_plan_code, count(*), count(*)*t2.Daily_Charge_HKD " & _
                      "from Opt_In_Customer_Record t1 Inner Join Daily_Charge t2 " & _
                      "On (t1.event_plan_code=t2.event_plan_code and t2.Home_BMO='" & prefix & "') " & _
                      "group by t1.event_plan_code " & _
                      "order by t1.event_plan_code "

                MsgBox sql

                If ObjectExists("Query", "getDailyCharge") Then
                     DoCmd.DeleteObject acQuery, "getDailyCharge"
                End If

                With dbs
                    .QueryTimeout = 0
                    Set QueryDef = .CreateQueryDef("getDailyCharge", sql)
                End With

                strPathToSave = txtOutputPath3.value

                DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "getDailyCharge", strPathToSave, True
                MsgBox "Daily charge report generated."
            End If
        Else
            MsgBox "Input file does not exist. Please enter again."
        End If
    End If

End Sub

Answer 1

我知道您在评论中提到您不想按“t2.Daily_Charge_HK”进行分组，但要以您访问权限的方式使用它，您将需要对其进行分组，因为您的加入我猜您每个活动计划代码只有一个每日费用值，因此这种情况下的分组不会出现问题。例如，ID 为 1 且 Home BMO 前缀为 x 的所有活动计划代码将具有相同的费率。

更改：

sql = "select t1.event_plan_code, count(*), count(*)*t2.Daily_Charge_HKD " & _
    "from Opt_In_Customer_Record t1 Inner Join Daily_Charge t2 " & _
    "On (t1.event_plan_code=t2.event_plan_code and t2.Home_BMO='" & prefix & "') " & _
    "group by t1.event_plan_code " & _
    "order by t1.event_plan_code "

变为：

sql = "select t1.event_plan_code, count(*), count(*)*t2.Daily_Charge_HKD " & _
    "from Opt_In_Customer_Record t1 Inner Join Daily_Charge t2 " & _
    "On (t1.event_plan_code=t2.event_plan_code and t2.Home_BMO='" & prefix & "') " & _
    "group by t1.event_plan_code, t2.Daily_Charge_HKD " & _
    "order by t1.event_plan_code, t2.Daily_Charge_HKD "

希望这有帮助

Answer 2

最后我想出了这个解决方案并且它有效：

sql = "select t1.event_plan_code, Count, Count*Daily_Charge_HKD As 'Count * Daily_Charge_HKD' " & _

  "from (select event_plan_code, count(*) As Count " & _
  "from Opt_In_Customer_Record " & _
  "group by event_plan_code " & _
  "order by event_plan_code) t1, Daily_Charge t2 " & _
  "where t1.event_plan_code=t2.event_plan_code and t2.Home_BMO='" & prefix & "' " & _
  "order by t1.event_plan_code"