访问& SQL Server：自日期以来的使用次数聚合问题 - 新的报告问题（已解决的聚合问题）

Question

背景：
我一直在努力简化在我的程序中运行报告所涉及的工作。最近，我必须提供已使用仪器的工作编号列表以及成本/效益分析项目列表。主要是为了了解仪器自上次维修/校准以来以及上次有人使用它的频率。我希望将其集成到有助于生成报告的查询中 - 但我一直在使用次数方面遇到障碍 - 因为我希望该聚合基于仪器上次校准的日期（基于字段的日期）在同一查询中）。我可以让它给我系统总的使用次数 - 但它不会接受我希望它只计算自上次校准以来使用的次数的限制

问题：
尝试在我的报告中添加一个聚合函数来计算使用次数，因为该项目的校准要么出现不期望的结果，要么出现可怕的“聚合丢失”错误（不记得确切的警告）。

-- 编辑添加 8/12/2011 @ 16:09 --

对于从未使用过且被此查询排除的工具，发现了使用 Max 聚合的另一个问题。

详细信息：

这是迄今为止有效的查询：

SELECT
  dbo_tblPOGaugeDetail.intGagePOID,
  dbo_tblPOGaugeDetail.strGageDetailID,
  dbo_Gage_Master.Description,
  dbo_Gage_Master.Manufacturer,
  dbo_Gage_Master.Model_No,
  dbo_Gage_Master.Gage_SN,
  dbo_Gage_Master.Unit_of_Meas,
  dbo_Gage_Master.User_Defined,
  dbo_Gage_Master.Calibration_Frequency,
  dbo_Gage_Master.Calibration_Frequency_UOM,
  dbo_tblPOGaugeDetail.bolGageLeavePriceBlank,
  dbo_tblPOGaugeDetail.intGageCost,
  dbo_Gage_Master.Last_Calibration_Date,
  dbo_Gage_Master.Next_Due_Date,
  dbo_tblPOGaugeDetail.bolGageEvaluate,
  dbo_tblPOGaugeDetail.bolGageExpedite,
  dbo_tblPOGaugeDetail.bolGageAccredited,
  dbo_tblPOGaugeDetail.bolGageCalibrate,
  dbo_tblPOGaugeDetail.bolGageRepair,
  dbo_tblPOGaugeDetail.bolGageReturned,
  dbo_tblPOGaugeDetail.bolGageBER,
  dbo_tblPOGaugeDetail.intTurnaroundDaysOut,
  qryRCEquipmentLastUse.MaxOfdatDateEntered
FROM (dbo_tblPOGaugeDetail
  INNER JOIN dbo_Gage_Master ON dbo_tblPOGaugeDetail.strGageDetailID = dbo_Gage_Master.Gage_ID)
  INNER JOIN qryRCEquipmentLastUse ON dbo_Gage_Master.Gage_ID = qryRCEquipmentLastUse.Gage_ID
ORDER BY dbo_tblPOGaugeDetail.strGageDetailID;

但我似乎无法使用以下字段从 tblGageActivity 聚合使用计数（制作 Count(strCustomerJobNum)）：

strGageID
strCustomerJobNum
datDateEntered
datTimeEntered

我尝试在之前列出的查询中添加一个字段以执行 Count(strCustomerJobNum)，其中 datDateEntered 与调用查询中的 Last_Calibration_Date 匹配 - 但我得到了“缺少聚合”错误。如果我忽略这个条件 - 它会运行 - 但只会列出所有发出的仪器，前提是它的使用次数至少为一（遗憾的是，这根本不是我想要的）。

我还想确保，如果我应该得到零使用计数 - 我将得到零返回，而不是我的预期记录减去空结果。

我希望有人能告诉我哪里出了问题——每当我想生成此报告时，我想节省目前在另一个程序中运行活动报告所花费的时间。预先感谢，如果您需要我发布更多信息，请告诉我。

-- 编辑添加 08/15/2011 @ 14:41 --

我设法通过创建“纯”第一步查询来解决 Max() 聚合问题，以获取具有最新日期作为 qryRCEquipmentUsed 的所有仪器的列表。

qryRCEquipmentLastUse：

SELECT dbo.tblGageActivity.strGageID, Max(dbo.tblGageActivity.datDateEntered) AS datLastDateUsed
FROM dbo.tblGageActivity
GROUP BY dbo.tblGageActivity.strGageID;

然后，我创建了一个“纯”列表，列出了完全没有使用过的所有仪器，作为名为 qryRCEquipmentNeverUsed 的查询。

qryRCEquipmentNeverUsed：

SELECT dbo_Gage_Master.Gage_ID, NULL AS datLastDateUsed
FROM dbo_Gage_Master LEFT JOIN dbo_tblGageActivity ON dbo_Gage_Master.Gage_ID = dbo_tblGageActivity.strGageID
WHERE (((dbo_tblGageActivity.strGageID) Is Null));

注意：插入 NULL 是为了使第三个组合 UNION 查询不会因从表中检索的字段数量不匹配而失败。

最后，我创建了一个名为 qryCombinedUseEquipment 的 UNION 查询，将两者组合成一个列表：

qryCombinedUseEquipment：

SELECT *
FROM qryRCEquipmentLastUse
UNION SELECT *
FROM qryRCEquipmentNeverUsed;

使用最后一个联合查询将上次使用日期提供给父查询在数据表视图中工作，但是当报告中调用父查询 - 我得到一个空白报告；因此，如果能朝着正确的方向推动，我们仍然会非常感激。

---

附录

与上面相同的脚本，但表别名更短（以防有人发现更清晰）：

SELECT
  gd.intGagePOID,
  gd.strGageDetailID,
  gm.Description,
  gm.Manufacturer,
  gm.Model_No,
  gm.Gage_SN,
  gm.Unit_of_Meas,
  gm.User_Defined,
  gm.Calibration_Frequency,
  gm.Calibration_Frequency_UOM,
  gd.bolGageLeavePriceBlank,
  gd.intGageCost,
  gm.Last_Calibration_Date,
  gm.Next_Due_Date,
  gd.bolGageEvaluate,
  gd.bolGageExpedite,
  gd.bolGageAccredited,
  gd.bolGageCalibrate,
  gd.bolGageRepair,
  gd.bolGageReturned,
  gd.bolGageBER,
  gd.intTurnaroundDaysOut,
  lu.MaxOfdatDateEntered
FROM (dbo_tblPOGaugeDetail gd
  INNER JOIN dbo_Gage_Master gm ON gd.strGageDetailID = gm.Gage_ID)
  INNER JOIN qryRCEquipmentLastUse lu ON gm.Gage_ID = lu.Gage_ID
ORDER BY gd.strGageDetailID;

Answer 1

一点一点...

首先——我怀疑您试图一次回答太多问题（正如 SELECT 中的 23 个字段所证明的那样），这将使聚合几乎不可能。首先缩小查询的范围——这个查询试图回答什么问题？ （您随时可以提出更多查询来回答其他问题...:-)

1) 自上次校准以来有多少次使用？
2) 自上次使用以来有多少次使用...使用？ （不确定您的意思 - 也许是上次注销，或上次租赁等？）

提示 - 学习使用表别名。大型查询难以阅读；由于重复的表名而变得更糟。

1) 例如：dbo_tbl_POGaugeDetail.intGagePOID 变为 d.intGagePOID

以下是一个可以帮助您入门的示例：

SELECT
   d.strCustomerJobNum,
   Max(d.last_calibration_date) -- not sure what you named that field
   Count(d.strCustomerJobNum)
FROM
   dbo_tblPOGaugeDetail d
GROUP BY
   d.strCustomerJobNum

Answer 2

这有效吗：

    SELECT dbo_tblPOGaugeDetail.intGagePOID, dbo_tblPOGaugeDetail.strGageDetailID, 

        OuterGageMaster.Description, OuterGageMaster.Manufacturer,   OuterGageMaster.Model_No, 

        OuterGageMaster.Gage_SN, OuterGageMaster.Unit_of_Meas, OuterGageMaster.User_Defined, 

        OuterGageMaster.Calibration_Frequency, OuterGageMaster.Calibration_Frequency_UOM, 

        dbo_tblPOGaugeDetail.bolGageLeavePriceBlank, dbo_tblPOGaugeDetail.intGageCost, 

        OuterGageMaster.Last_Calibration_Date, OuterGageMasterNext_Due_Date, 

        dbo_tblPOGaugeDetail.bolGageEvaluate, dbo_tblPOGaugeDetail.bolGageExpedite, 

        dbo_tblPOGaugeDetail.bolGageAccredited, dbo_tblPOGaugeDetail.bolGageCalibrate, 

        dbo_tblPOGaugeDetail.bolGageRepair, dbo_tblPOGaugeDetail.bolGageReturned, 

        dbo_tblPOGaugeDetail.bolGageBER, dbo_tblPOGaugeDetail.intTurnaroundDaysOut, 

        qryRCEquipmentLastUse.MaxOfdatDateEntered,

        (Select Count(strCustomerJobNum) 

             FROM tblGageActivity WHERE   

              OuterGageMaster.Last_Calibration_Date=tblGageActivity.datDateEntered) As   JobCount

FROM 

    (dbo_tblPOGaugeDetail INNER JOIN dbo_Gage_Master OuterGageMaster ON 

    dbo_tblPOGaugeDetail.strGageDetailID = OuterGageMaster.Gage_ID) INNER JOIN 

    qryRCEquipmentLastUse ON OuterGageMaster.Gage_ID = qryRCEquipmentLastUse.Gage_ID

ORDER BY

 dbo_tblPOGaugeDetail.strGageDetailID;

还是你尝试过的？

Answer 3

总结问题：

尝试在我的报告中添加一个聚合函数来计算使用次数，因为该项目的校准要么出现不期望的结果，要么出现可怕的“聚合缺失”错误。

解决方案：

我决定让查询单独驱动报表 - 相反，选择适当使用 DLookup 和 DCount 从提供所有数据的上次使用日期的查询中检索上次使用的日期。仪器，以及仪器自上次校准以来的使用次数，分别使用上述域聚合。

使用问题描述中描述的查询，我能够检索所有仪器的上次使用日期。我使用 =DLookup 语句作为报表子报表上处理各种项目的文本框的源，如下所示：

=IIf((DLookUp("[qryRCCombinedUseEquipment]![datLastDateUsed]","[qryRCCombinedUseEquipment]","[qryRCCombinedUseEquipment]![strGageID]=[strGageDetailID]")) Is Null Or ([bolGageReturned]=True),"",DLookUp("[qryRCCombinedUseEquipment]![datLastDateUsed]","[qryRCCombinedUseEquipment]","[qryRCCombinedUseEquipment]![strGageID]=[strGageDetailID]"))

这允许从未使用过的项目返回 NULL 结果，该结果将显示为空白文本框。

然而，使用次数不会通过使用 =DCount 的查询来提供（我尝试过，如果有的话，检索结果将需要十多分钟）。然而，使用底层活动表，我使用了以下语句：

=IIf([bolGageReturned],"","Used " & DCount("[dbo_tblGageActivity]![strGageID]","[dbo_tblGageActivity]","[dbo_tblGageActivity]![strGageID] = [strGageDetailID] And [dbo_tblGageActivity]![datDateEntered]  Between [txtLastCalibrationDate] And date()") & " times since last calibration")

它将检索自上次校准仪器以来的使用次数，但不会检索当天之前或之后的使用情况（奇怪的是，有些作业是过时的）。当然，这很慢（对于包含三十或四十台仪器的大型文档来说大约三十秒）。

其他人对此有更好的解决方案吗？还是我必须承受性能损失？如果没有人有更好的想法，我会在五天（8/21/2011）后接受这个答案。