問題描述

背景:
我一直在嘗試簡化在我的程序中運行報告所涉及的工作.最近，我不得不提供一份工作編號列表，其中包含用于成本/收益分析的項目列表.主要是查看儀器自上次維修/校準以來的使用頻率以及任何人上次使用它的頻率.我希望將其集成到有助于生成報告的查詢中 - 但我一直在使用次數遇到各種各樣的磚墻 - 因為我希望該聚合基于儀器上次校準的日期(基于字段的在同一個查詢中).我可以讓它給我系統總使用次數 - 但它不會接受我希望它只計算自上次校準以來使用的次數的限制

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

這篇關于訪問&SQL Server:自日期聚合問題以來的使用次數 - 新報告問題(已解決聚合問題)的文章就介紹到這了，希望我們推薦的答案對大家有所幫助，也希望大家多多支持html5模板網！