双倍的平均值不是双倍？ [英] Average of a double not a double?
问题描述
我无法让Access识别两个值彼此相等。这是情况（看起来很长，但我只是想要彻底彻底）：
我有一个名为chem2046的大表，其中包括三个字段，
PWSID（文本），CHEMID（文本），VALUE（双倍）
我在PWSID和CHEMID上执行分组来获得平均值对于PWSID和CHEMID的每个组合的VALUE，导致查询（让'来电是AverageValueBySystem）有三个字段，
PWSID，CHEMID，AverageVALUE
我有另一张名为ChemLevels的桌子，里面有字段，
CHEMID（文字），THRESHOLD（双）
我创建另一个查询（让我们称之为BuggyResults），它将AverageValueBySystem内部连接到CHEMID上的ChemLevels。 BuggyResults有三个字段，
PWSID，CHEMID，AverageVALUE，THRESHOLD，IsEqual
当我显示BuggyResults时，我将字段AverageValue和THRESHOLD并排，所以我可以看到它们何时相等。在这些情况下，我希望IsEqual始终为1;但是，我发现它偶尔会评估为0.这对我来说没有意义，特别是它似乎是命中注定。 {我不认为问题与加入有关。 CHEMID从来都不是空的，我只是在chem2046表中只包含一个独特的值，让事情变得非常简单。}
我的唯一猜测就是这与此有关数据类型，但我不明白为什么双倍的平均值会出现双重兼容性问题。
我找不到任何押韵或理由，任何人都可以帮帮我??
谢谢，
迈克
Hi,
I am having trouble getting Access to recognize two values as being equal to one another. Here''s the situation (looks long but I am just trying to be uber-thorough):
I have a large table called chem2046, which includes three fields,
PWSID (text), CHEMID (text), VALUE (double)
and I perform a group-by on PWSID and CHEMID to get an the average of VALUE for every combination of PWSID and CHEMID, resulting in a query (let''s call is "AverageValueBySystem") with three fields,
PWSID, CHEMID, AverageVALUE
I have another table called ChemLevels, which has the fields,
CHEMID (text), THRESHOLD (double)
I create another query (let''s call it BuggyResults) which inner-joins AverageValueBySystem to ChemLevels on CHEMID. BuggyResults has three fields,
PWSID, CHEMID, AverageVALUE, THRESHOLD, IsEqual
where IsEqual = IIf(AverageVALUE=THRESHOLD,1,0)
When I display BuggyResults, I have the fields AverageValue and THRESHOLD side-by-side, so I can see when they are equal. In these cases, I expect IsEqual always to be 1; however, I see that it occasionally evaluates to 0. This does not make sense to me, especially that it seems to be hit-or-miss. {I don''t think the problem has to do with the join. CHEMID is never Null, and I have made things super simple by only including a single unique value in that field in the chem2046 table.}
My only guess is that this has to do with datatypes, but I can''t figure out why the average a double would have compatibility problems with a double.
I can''t find any rhyme or reason to this, can anyone help me??
Thanks,
Mike
推荐答案
结果如果我使用CDbl（）强制AverageVALUE和THRESHOLD加倍，仍会返回不正确的结果。但是，如果我将AverageVALUE和THRESHOLD强制为字符串，然后再将CDbl（CStr（））加倍，则结果是正确的。任何人都可以解释这个吗？
Turns out that if I force both AverageVALUE and THRESHOLD to double using CDbl(), incorrect results are still returned. However, if I force AverageVALUE and THRESHOLD to strings and then back to doubles CDbl(CStr()), then the results are correct. Can anyone explain this?
- 我之前的猜测尝试是谦卑地撤回，因为下面会有更好的答案。 ^ _ ^
- My previous attempt at a guess is humbly withdrawn, for lo, a much better answer appears below. ^_^
我无法获取Access以识别两个值彼此相等。这是情况（看起来很长，但我只是想要彻底彻底）：
我有一个名为chem2046的大表，其中包括三个字段，
PWSID（文本），CHEMID（文本），VALUE（双倍）
我在PWSID和CHEMID上执行分组来获得平均值对于PWSID和CHEMID的每个组合的VALUE，导致查询（让'来电是AverageValueBySystem）有三个字段，
PWSID，CHEMID，AverageVALUE
我有另一张名为ChemLevels的桌子，里面有字段，
CHEMID（文字），THRESHOLD（双）
我创建另一个查询（让我们称之为BuggyResults），它将AverageValueBySystem内部连接到CHEMID上的ChemLevels。 BuggyResults有三个字段，
PWSID，CHEMID，AverageVALUE，THRESHOLD，IsEqual
当我显示BuggyResults时，我将字段AverageValue和THRESHOLD并排，所以我可以看到它们何时相等。在这些情况下，我希望IsEqual始终为1;但是，我发现它偶尔会评估为0.这对我来说没有意义，特别是它似乎是命中注定。 {我不认为问题与加入有关。 CHEMID从来都不是空的，我只是在chem2046表中只包含一个独特的值，让事情变得非常简单。}
我的唯一猜测就是这与此有关数据类型，但我不明白为什么双倍的平均值会出现双重兼容性问题。
我找不到任何押韵或理由，任何人都可以帮助我??
谢谢，
Mike
Hi,
I am having trouble getting Access to recognize two values as being equal to one another. Here''s the situation (looks long but I am just trying to be uber-thorough):
I have a large table called chem2046, which includes three fields,
PWSID (text), CHEMID (text), VALUE (double)
and I perform a group-by on PWSID and CHEMID to get an the average of VALUE for every combination of PWSID and CHEMID, resulting in a query (let''s call is "AverageValueBySystem") with three fields,
PWSID, CHEMID, AverageVALUE
I have another table called ChemLevels, which has the fields,
CHEMID (text), THRESHOLD (double)
I create another query (let''s call it BuggyResults) which inner-joins AverageValueBySystem to ChemLevels on CHEMID. BuggyResults has three fields,
PWSID, CHEMID, AverageVALUE, THRESHOLD, IsEqual
where IsEqual = IIf(AverageVALUE=THRESHOLD,1,0)
When I display BuggyResults, I have the fields AverageValue and THRESHOLD side-by-side, so I can see when they are equal. In these cases, I expect IsEqual always to be 1; however, I see that it occasionally evaluates to 0. This does not make sense to me, especially that it seems to be hit-or-miss. {I don''t think the problem has to do with the join. CHEMID is never Null, and I have made things super simple by only including a single unique value in that field in the chem2046 table.}
My only guess is that this has to do with datatypes, but I can''t figure out why the average a double would have compatibility problems with a double.
I can''t find any rhyme or reason to this, can anyone help me??
Thanks,
Mike
我认为它这与Avg函数在字段表达式中的值被平均之前对数据进行分组这一事实有关。这意味着结果取决于分组。由于您可以在总计查询中的计算字段表达式中使用DAvg或Avg函数，因此您可能需要尝试DAvg函数，因为在对数据进行分组之前对值进行平均。请参阅以下链接以获取支持参考：
http://msdn2.microsoft.com/en-us/lib...ffice.10).aspx
这篇关于双倍的平均值不是双倍？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
