問題描述

背景:
我一直在嘗試簡(jiǎn)化在我的程序中運(yùn)行報(bào)告所涉及的工作.最近，我不得不提供一份工作編號(hào)列表，其中包含用于成本/收益分析的項(xiàng)目列表.主要是查看儀器自上次維修/校準(zhǔn)以來的使用頻率以及任何人上次使用它的頻率.我希望將其集成到有助于生成報(bào)告的查詢中 - 但我一直在使用次數(shù)遇到各種各樣的磚墻 - 因?yàn)槲蚁Ｍ摼酆匣趦x器上次校準(zhǔn)的日期(基于字段的在同一個(gè)查詢中).我可以讓它給我系統(tǒng)總使用次數(shù) - 但它不會(huì)接受我希望它只計(jì)算自上次校準(zhǔn)以來使用的次數(shù)的限制

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

問題:
嘗試在我的報(bào)告中為使用次數(shù)添加一個(gè)聚合函數(shù)，因?yàn)轫?xiàng)目的校準(zhǔn)遇到了不想要的結(jié)果，或者可怕的聚合丟失"錯(cuò)誤(不記得確切的警告).

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

發(fā)現(xiàn)使用 Max 聚合的另一個(gè)問題是從未使用過的工具被此查詢排除.

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 中匯總使用計(jì)數(shù)(生成 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

我嘗試向先前列出的查詢添加一個(gè)字段以執(zhí)行 Count(strCustomerJobNum)，其中 datDateEntered 與調(diào)用查詢中的 Last_Calibration_Date 匹配 - 但我收到了缺少聚合"錯(cuò)誤.如果我不考慮這個(gè)條件 - 它會(huì)運(yùn)行 - 但只有當(dāng)它的使用次數(shù)至少為一個(gè)(遺憾的是，這根本不是我想要的)時(shí)，才會(huì)列出所有發(fā)出過的樂器.

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

我還想確保如果我應(yīng)該得到零使用計(jì)數(shù) - 我會(huì)得到零，而不是我的預(yù)期記錄減去空結(jié)果.

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.

我希望有人能告訴我我哪里出錯(cuò)了 - 我想節(jié)省我目前在另一個(gè)程序中運(yùn)行活動(dòng)報(bào)告所花費(fèi)的時(shí)間，每當(dāng)我想生成此報(bào)告時(shí).提前致謝，如果您需要我發(fā)布更多信息，請(qǐng)告訴我.

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

我設(shè)法解決了 Max() 聚合問題，方法是創(chuàng)建一個(gè)純"的第一步查詢，以獲取最新日期為 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;

然后我創(chuàng)建了一個(gè)純"列表，列出了所有根本沒有使用的工具作為名為 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 查詢不會(huì)因?yàn)閺谋碇袡z索的字段數(shù)不匹配而失敗.

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.

最后，我創(chuàng)建了一個(gè)名為 qryCombinedUseEquipment 的 UNION 查詢，將兩者組合成一個(gè)列表:

At last, I created a UNION query named qryCombinedUseEquipment to combine the two into a list:

qryCombinedUseEquipment:

SELECT *
FROM qryRCEquipmentLastUse
UNION SELECT *
FROM qryRCEquipmentNeverUsed;

使用最后一個(gè)聯(lián)合查詢將上次使用日期提供給父查詢?cè)跀?shù)據(jù)表視圖中工作，但是當(dāng)在報(bào)告中調(diào)用父查詢時(shí) - 我得到一個(gè)空白報(bào)告；因此，在正確方向上的推動(dòng)仍然會(huì)受到極大的贊賞.

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.

附錄

與上面相同的腳本，但表別名更短(以防有人發(fā)現(xiàn)更清楚):

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;

推薦答案

總結(jié)問題:

嘗試在我的報(bào)告中添加一個(gè)聚合函數(shù)以了解使用次數(shù)，因?yàn)轫?xiàng)目的校準(zhǔn)遇到了不希望的結(jié)果或可怕的聚合缺失"錯(cuò)誤.

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.

解決方案:

我決定讓查詢單獨(dú)驅(qū)動(dòng)報(bào)告 - 而不是選擇使用適當(dāng)?shù)?DLookup 和 DCount 從提供所有工具的最后使用日期的查詢中檢索最后使用的日期，以及使用自上次校準(zhǔn)以來的儀器，分別使用上述域聚合.

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 語句作為報(bào)表子報(bào)表上處理各種項(xiàng)目的文本框的來源:

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]"))

這允許從未使用過的項(xiàng)目返回 NULL 結(jié)果，該結(jié)果將顯示為一個(gè)空白文本框.

This allows items that have never been used to return a NULL result, which will display as a blank text box.

但是，使用次數(shù)不會(huì)通過使用 =DCount 進(jìn)行查詢(我試過，檢索結(jié)果需要十多分鐘，如果有的話).但是，使用底層活動(dòng)表，我使用了以下語句:

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

它會(huì)檢索自上次校準(zhǔn)儀器以來使用過的次數(shù)，但沒有在今天之前或之后使用過(有些工作過時(shí)了，奇怪的是).當(dāng)然，這很慢(對(duì)于包含三十或四十個(gè)儀器的大型文檔，大約需要三十秒).

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

有沒有其他人對(duì)此有更好的解決方案，還是我必須承擔(dān)性能損失?如果沒有人有更好的想法，我會(huì)在五天后(8/21/2011)接受這個(gè)作為答案.

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

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