Average of a double not a double?

[mrooney]

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 "AverageValueBy System") 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 AverageValueByS ystem to ChemLevels on CHEMID. BuggyResults has three fields,

PWSID, CHEMID, AverageVALUE, THRESHOLD, IsEqual

where IsEqual = IIf(AverageVALU E=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

[mrooney]

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?

[Khriskin]

- My previous attempt at a guess is humbly withdrawn, for lo, a much better answer appears below. ^_^

[puppydogbuddy]

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 "AverageValueBy System") 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 AverageValueByS ystem to ChemLevels on CHEMID. BuggyResults has three fields,

PWSID, CHEMID, AverageVALUE, THRESHOLD, IsEqual

where IsEqual = IIf(AverageVALU E=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

I think it has something to do with the fact that the Avg function groups the data before values in the field expression are averaged. This means that the result is dependent on the grouping. Since you can use either the DAvg or Avg function in a calculated field expression in a totals query, you may want to try the DAvg function because values are averaged before the data is grouped. See the following link for a supporting reference:

http://msdn2.microsoft.com/en-us/library/aa190033(office.10).aspx

[mrooney]

Thanks for the tip! Unfortunately, I really do need to group before averaging because I need an average for each PWSID-CHEMID combination. So I am not sure that DAvg would help... Maybe I am missing the point?

I have found that performing CDbl(CStr(Avg(V ALUE))) definitely solves the problem, but I am not sure why. Is it possible for the Avg() function to return dirty values?

In one example, Avg() is returning a value of 0.6. When I write an expression asking whether that value is equal to a record in another field, also equal to 0.6, Access says they are not equal. But according to my naked eye they are equal, and if I dump them into Excel, Excel says they are equal. So the only thing I can think of is a data type mismatch, but I cannot think of what the mismatch would be. As I said, CDbl(Avg(VALUE) ) is not enough the get the values to compare correctly.

Thanks,
Mike

[puppydogbuddy]

Thanks for the tip! Unfortunately, I really do need to group before averaging because I need an average for each PWSID-CHEMID combination. So I am not sure that DAvg would help... Maybe I am missing the point?

I have found that performing CDbl(CStr(Avg(V ALUE))) definitely solves the problem, but I am not sure why. Is it possible for the Avg() function to return dirty values?

In one example, Avg() is returning a value of 0.6. When I write an expression asking whether that value is equal to a record in another field, also equal to 0.6, Access says they are not equal. But according to my naked eye they are equal, and if I dump them into Excel, Excel says they are equal. So the only thing I can think of is a data type mismatch, but I cannot think of what the mismatch would be. As I said, CDbl(Avg(VALUE) ) is not enough the get the values to compare correctly.

Thanks,
Mike

Mike,
If you need to group before averaging, then using DAvg won't help.

In regards to CDbl(CStr(Avg(V ALUE))):
If you use CStr, you are converting an expression to a text string, creating a data mismatch.....bu t then you used CDbl to convert the string back to a double precision number again. I don't see how that could correct the problem.

Is there some way to create uniform or standard groups, so the avgs will be computed on the exact same base? or maybe you could consider as equal, those results within a certain precision + or -

[mrooney]

Well, CDbl(CStr(Avg(V ALUE))) definitely does correct the problem. That's why the problem is so insidious.

So I guess I do have a solution to my problem now. That makes me less interested in setting up precision bounds like you mentioned. But I really wish I understood what was forcing me to go through such rigamarole. Why would two doubles, both ostensibly equal to 0.6 not equate? I did some further snooping and found that both values will equate independently to a hardcoded 0.6. They just won't equate to each other.

Other people in my research group are noting similar problems and there is growing concern over whether we can perform our calculations in Access anymore.

[mrooney]

I discovered that Round(Avg([VALUE]),10) also fixes the problem.

My theory is that when Access does its division (as part of calculating the average) there is occasionally a discrepancy in the smallest significant bit. So even though the value should mathematically be exactly 0.6, Access sees it as something roughly equivalent to 0.5999999999999 9999999999999, resulting in unusual behavior. I am no expert, but that's my best guess at this.

[puppydogbuddy]

I discovered that Round(Avg([VALUE]),10) also fixes the problem.

My theory is that when Access does its division (as part of calculating the average) there is occasionally a discrepancy in the smallest significant bit. So even though the value should mathematically be exactly 0.6, Access sees it as something roughly equivalent to 0.5999999999999 9999999999999, resulting in unusual behavior. I am no expert, but that's my best guess at this.

Rounding to 10 decimal places seems like a good solution. You need to be aware that Access uses the round to even method of rounding, which could cause some differences. See the following link.

http://www.techonthenet.com/access/functions/numeric/round.php

[Stewart Ross]

Re: Average of a double not a double?
I discovered that Round(Avg([VALUE]),10) also fixes the problem.

My theory is that when Access does its division (as part of calculating the average) there is occasionally a discrepancy in the smallest significant bit. So even though the value should mathematically be exactly 0.6, Access sees it as something roughly equivalent to 0.5999999999999 9999999999999, resulting in unusual behavior. I am no expert, but that's my best guess at this.

Rounding errors in floating-point calculations render tests for strict equality very hit and miss. Explicit rounding (as above) can resolve this, as does testing the difference between the two values and checking whether the difference is lower than a certain threshold. So, instead of IF A = B (which would work perfectly for integers, longs, and other fixed-point numbers) you can use

IF Abs(A-B) < Threshold then ...

where Threshold is a value that would be taken to be 'close enough' that they are indeed equal. This is an arbitrary value, and could be whatever suits you for the purpose. For example, if you define Threshold to be 10 ^ -6 (10 to the minus 6, or a millionth), then rounding errors beyond the sixth decimal place of the result are taken into account.

Since round(x, 10) works it suggests that the rounding errors involved are actually very small - 10 ^ -11 or less.

Stewart

[mrooney]

Thanks a lot! That was my suspicion.