访问和SQL Server:自日期以来的使用次数聚合问题-新的报告问题(已解决的聚合问题) [英] Access & SQL Server: Number of uses since date aggregate problem - new reporting problem (solved aggregate issue)

问题描述

背景:
我一直在尝试简化程序中运行报告所涉及的工作.最近，我不得不提供一份使用过的工具的工作编号清单，以及用于成本/收益分析的项目清单.主要是查看自上次维修/校准以来仪器使用的频率以及任何人上次使用仪器的时间.我一直希望将其集成到有助于生成报告的查询中-但我不断在使用次数方面碰到一堵砖墙-因为我希望该汇总基于仪器上次校准的日期(基于字段在同一查询中).我可以得到它来提供系统中的总使用次数-但它不会接受我希望它仅计算自上次校准以来使用的时间的限制

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 --

对于从未被此查询排除的工具，发现了使用最大聚合的另一个问题.

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.

qryRCE设备从未使用:

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.

APPENDIX

与上述脚本相同，但表别名更短(以防有人发现更清晰的别名):

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).

还有其他人对此有更好的解决方案吗，还是我不得不承受性能方面的冲击?如果没有人有更好的主意，我将在五天后(2011年8月21日)接受此答案.

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) .

这篇关于访问和SQL Server:自日期以来的使用次数聚合问题-新的报告问题(已解决的聚合问题)的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！