Solved Query Dividing by 1 Giving Incorrect Results (1 Viewer)

robsmith

New member
Local time
Today, 19:41
Joined
Feb 17, 2020
Messages
26
Hi,

I am trying to run a simple query where I divide values by 1. Many of the results are correct but often they are wrong.

This is the query:

Untitled.png


And here is an extract of the results:

EVENT_IDSELECTION_NAMEBSPBSPRPPWAPPPWAPRMORNINGWAPMornWAPR
116821865​
Investissement
2​
0.5​
2.04211327886025​
0.489688799515629​
2.36734735680065​
0.422413718513809​
116821865​
Bennelong
14.796570571897​
6.75832278257296E-02​
12.6466123626234​
0.079072558826541​
9.60161536643841​
0.104149141767896​
116821865​
Prime Exhibit
9​
0.111111111111111​
8.32956245936419​
0.120054325167559​
6.43245421804421​
0.155461658350372​
116821865​
Mister Green
32​
0.03125​
31.3575591097099​
3.18902372630894E-02​
11.2505352142602​
8.88846602366511E-02​
116821865​
Elusive Ellen
10​
0.1​
9.34990225756201​
0.106952989716146​
9.03321846703053​
0.110702514685082​
116821865​
Sutton Sid
44​
2.27272727272727E-02​
44.1101434795782​
2.26705224947403E-02​
30.7259364397246​
3.25457940708076E-02​
116821865​
Its Only Business
5.785896489​
0.172834063295321​
6.68945998601978​
0.149488897772001​
10.4175027472858​
9.59922952994225E-02​


As an example, look at the second row of BSPR. 1/BSP should give 0.0675... but it doesn't. It's the same with the penultimate row in BSPR and elsewhere.

Can anyone suggest what the issue might be?

Thanks
 

Minty

AWF VIP
Local time
Today, 19:41
Joined
Jul 26, 2013
Messages
10,355
I think you'll find that is 0.00675 , It's just displayed in scientific notation.
Access will do that when there are more than a couple of leading zero's, or a lot of digits after the decimal point with a 0 as the leading digit.
 

isladogs

MVP / VIP
Local time
Today, 19:41
Joined
Jan 14, 2017
Messages
18,186
Agree with Minty

By the way you aren't dividing by 1 (which would be pointless). You are calculating the reciprocal values.

If you don't want to see scientific notation, format your calculated fields as Fixed and specify the number of d.p. required
 

arnelgp

..forever waiting... waiting for jellybean!
Local time
Tomorrow, 03:41
Joined
May 7, 2009
Messages
19,169
you can Round it to specified digits:

BSPR: Round(1/[BSP], 4)
 

Pat Hartman

Super Moderator
Staff member
Local time
Today, 15:41
Joined
Feb 19, 2002
Messages
42,973
www.FMSINC.com has lots of excellent Access articles. One in particular might shed some light on floating point errors. It is called "When Access Math Doesn't Add Up". Sorry, I don't have a direct link.

If you don't need more than 4 decimal digits of precision, you can avoid using single and double and switch to Currency. It is is limited to 4 decimal digits and is base 10 rather than base 2 so you don't get the anomolies you get with single and double when you do arithmetic. You can format the data type however you want. It doesn't have to be formatted as money with a dollar sign.
 

Users who are viewing this thread

Top Bottom