访问&SQL Server:自日期聚合问题以来的使用次数 - 新报告问题(已解决聚合问题)
背景:
我一直在尝试简化在我的程序中运行报告所涉及的工作.最近，我不得不提供一份工作编号列表，其中包含用于成本/收益分析的项目列表.主要是查看仪器自上次维修/校准以来的使用频率以及任何人上次使用它的频率.我希望将其集成到有助于生成报告的查询中 - 但我一直在使用次数遇到各种各样的砖墙 - 因为我希望该聚合基于仪器上次校准的日期(基于字段的在同一个查询中).我可以让它给我系统总使用次数 - 但它不会接受我希望它只计算自上次校准以来使用的次数的限制
BACKGROUND:
I've been trying to streamline the work involved in running a report in my program. Lately, I've had to supply a listing of job numbers an instrument has been used on with the listing of items for cost/benefit analysis. Mostly to see how often an instrument is used since it was last serviced/calibrated and the last time anyone did use it. I was looking to integrate this into the query that helps generate the report - but I keep hitting a brick wall of sorts with the number of uses - since I want that aggregate to be based on the date the instrument was last calibrated (a field based in the same query). I can get it to give me the number of uses in the system total - but it will not accept the limitation that I want it to be only counting the times used since the last time it was calibrated
问题:
尝试在我的报告中为使用次数添加一个聚合函数，因为项目的校准遇到了不想要的结果，或者可怕的聚合丢失"错误(不记得确切的警告).
PROBLEM:
Attempts to put an aggregate function in my report for the number of uses since the item's calibration are met either with undesired results, or the dreaded 'aggregate missing' error (don't remember the exact warning).
-- 编辑添加 8/12/2011 @ 16:09 --
-- Edited to add 8/12/2011 @ 16:09 --
发现使用 Max 聚合的另一个问题是从未使用过的工具被此查询排除.
An additional problem with the use of the Max aggregate has been found for instruments that have never been used being excluded by this query.
详情:
以下是目前有效的查询:
Here is the query that does work so far:
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)):
But I can't seem to aggregate a count of Uses (making a Count(strCustomerJobNum)) from the tblGageActivity with the following fields:
strGageID
strCustomerJobNum
datDateEntered
datTimeEntered
我尝试向先前列出的查询添加一个字段以执行 Count(strCustomerJobNum)，其中 datDateEntered 与调用查询中的 Last_Calibration_Date 匹配 - 但我收到了缺少聚合"错误.如果我不考虑这个条件 - 它会运行 - 但只有当它的使用次数至少为一个(遗憾的是，这根本不是我想要的)时，才会列出所有发出过的乐器.
I tried to add a field to the formerly listed query to do a Count(strCustomerJobNum) where datDateEntered matched the Last_Calibration_Date from the calling query - but I got the 'missing aggregate' error. If I leave this condition out - it will run - but will list every instrument ever sent out only if it's had a usage count of at least one (not what I want at all, sadly).
我还想确保如果我应该得到零使用计数 - 我会得到零，而不是我的预期记录减去空结果.
I also want to make sure that if I should get a zero uses count - I will get a zero back instead of my expected records minus the null results.
我希望有人能告诉我我哪里出错了 - 我想节省我目前在另一个程序中运行活动报告所花费的时间，每当我想生成此报告时.提前致谢，如果您需要我发布更多信息，请告诉我.
I hope someone out there can tell me where I am going wrong with this - I want to save the time I am currently spending running an activity report in another program whenever I want to generate this report. Thanks in advance, and let me know if you need me to post more information.
-- 编辑添加 08/15/2011 @ 14:41 --
-- Edited to add 08/15/2011 @ 14:41 --
我设法解决了 Max() 聚合问题，方法是创建一个纯"的第一步查询，以获取最新日期为 qryRCEquipmentUsed 的所有工具的列表.
I managed to solve the Max() aggregate problem by creating a 'pure' first-step query to get a listing of all instrument with most modern date as qryRCEquipmentUsed.
qryRCEquipmentLastUse:
SELECT dbo.tblGageActivity.strGageID, Max(dbo.tblGageActivity.datDateEntered) AS datLastDateUsed
FROM dbo.tblGageActivity
GROUP BY dbo.tblGageActivity.strGageID;
然后我创建了一个纯"列表，列出了所有根本没有使用的工具作为名为 qryRCEquipmentNeverUsed 的查询.
Then I created a 'pure' listing of all instruments that have no usage at all as a query named 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 查询不会因为从表中检索的字段数不匹配而失败.
NOTE: The NULL was inserted so that the third combining UNION query will not fail due to a mismatch in the number of fields being retrieved from the tables.
最后，我创建了一个名为 qryCombinedUseEquipment 的 UNION 查询，将两者组合成一个列表:
At last, I created a UNION query named qryCombinedUseEquipment to combine the two into a list:
qryCombinedUseEquipment:
SELECT *
FROM qryRCEquipmentLastUse
UNION SELECT *
FROM qryRCEquipmentNeverUsed;
使用最后一个联合查询将上次使用日期提供给父查询在数据表视图中工作，但是当在报告中调用父查询时 - 我得到一个空白报告；因此，在正确方向上的推动仍然会受到极大的赞赏.
Using this last union query to feed the Last Used date to the parent query works in datasheet view, but when the parent query is called in the report - I get a blank report; so a nudge in the right direction would still be wonderfully appreciated.
附录
与上面相同的脚本，但表别名更短(以防有人发现更清楚):
Same script as above, but with shorter table aliases (in case someone finds that clearer):
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;
推荐答案
总结问题:
尝试在我的报告中添加一个聚合函数以了解使用次数，因为项目的校准遇到了不希望的结果或可怕的聚合缺失"错误.
Attempts to put an aggregate function in my report for the number of uses since the item's calibration are met either with undesired results, or the dreaded 'aggregate missing' error.
解决方案:
我决定让查询单独驱动报告 - 而不是选择使用适当的 DLookup 和 DCount 从提供所有工具的最后使用日期的查询中检索最后使用的日期，以及使用自上次校准以来的仪器，分别使用上述域聚合.
I decided to leave the query driving the report alone - instead choosing to employ the use of DLookup and DCount as appropriate to retrieve the last used date from a query that provides the last used date of all the instruments, and the number of uses an instrument has had since it's last calibration, using the aforementioned domain aggregates respectively.
使用问题描述中描述的查询，我能够检索所有仪器的上次使用日期.我使用 =DLookup 语句作为报表子报表上处理各种项目的文本框的来源:
Using the query described in the problem description, I am able to retrieve the last used date for all instruments. I used a =DLookup statement as the source for a text box on the report's subreport dealing with various items as such:
=IIf((DLookUp("[qryRCCombinedUseEquipment]![datLastDateUsed]","[qryRCCombinedUseEquipment]","[qryRCCombinedUseEquipment]![strGageID]=[strGageDetailID]")) Is Null Or ([bolGageReturned]=True),"",DLookUp("[qryRCCombinedUseEquipment]![datLastDateUsed]","[qryRCCombinedUseEquipment]","[qryRCCombinedUseEquipment]![strGageID]=[strGageDetailID]"))
这允许从未使用过的项目返回 NULL 结果，该结果将显示为一个空白文本框.
This allows items that have never been used to return a NULL result, which will display as a blank text box.
但是，使用次数不会通过使用 =DCount 进行查询(我试过，检索结果需要十多分钟，如果有的话).但是，使用底层活动表，我使用了以下语句:
The number of uses, however, would not feed off a query using =DCount (I tried, it would take over ten minutes to retrieve results, if it ever did). However, using the underlying activity table, I used the following statement:
=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")
它会检索自上次校准仪器以来使用过的次数，但没有在今天之前或之后使用过(有些工作过时了，奇怪的是).当然，这很慢(对于包含三十或四十个仪器的大型文档，大约需要三十秒).
It would retrieve a number of times used since the instrument was last calibrated, but no uses that are before that or after today (some jobs are post dated, strangely). Of course, this is SLOW (about thirty seconds for a large document with thirty or forty instruments).
有没有其他人对此有更好的解决方案，还是我必须承担性能损失?如果没有人有更好的想法，我会在五天后(8/21/2011)接受这个作为答案.
Does anyone else have a better solution for this, or will I have to take the performance hit? If no one has any better ideas, I will accept this as the answer after five days (8/21/2011) .
相关文章