Rounding of Calculations

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hoping you can help

I have a table where I'm trying to get a %. The total % should always equal
one, but it doesn't. Often is .9999 or 1.0001.

hours to proj A / total hours = %

There could be hours to proj A coming from several different folks, so I
have it set up in 2 separate queries
qry 1 - gets total hours to project A
qry 2 - gets total hours over
qry 3 - does % plus a few other simple stuff
 
db2nd,

Assuming that you are aware of the limitations inherent in all (to the best
of my knowledge) software packages and hardware device floating point
arithmetic systems.

To minimise rounding error try using the:

1) Currency or decimal datatypes (which give better accuracy at the cost of
reduced precision)

2) T-SQL Round function

And if you really concerned and a gun mathmatician:

3) Calculating the rounding error and comparing to a ranged threshold value.

Hope this helps
Guy
 
I tried using currency type, and it did minimize the rounding problems.
But, still need to get this worked out.

Could you (or anyone else) point me to some documentation or something for
the 3rd option?

Other suggestions? Either in preventing the rounding problem, or correcting
it after it's rounding incorrectly?

thank you.
 
Gotta try one more time.....are there any suggestions? I'm still having the
same problem, and don't know where to go from here.

Other suggestions? Either in preventing the rounding problem, or correcting
it after it's rounding incorrectly?

thank you for your help.
 
Gotta try one more time.....are there any suggestions? I'm still having the
same problem, and don't know where to go from here.
Click to expand...

We don't have much to go. Perhaps you could post some steps to
reproduce the problem?

An important consideration is the nature of the data itself e.g. is it
fixed point or floating point? Another is whether you are rounding
stored values (if so, how?). Jet's native decimal type, DECIMAL, tends
to be best for fixed point when you need to do your own rounding;
unsurprisingly, FLOAT (Double) handles 'unrounded' float data best.

Consider the following example using various numeric types to store
then sum float values -- remember INTEGER division results in a FLOAT
(ANSI-92 Query Mode syntax):

CREATE TABLE Test
(
dec_col DECIMAL(5,4) NOT NULL,
cur_col CURRENCY NOT NULL,
sng_col REAL NOT NULL,
dbl_col FLOAT NOT NULL,
int_col SMALLINT NOT NULL
)
;
INSERT INTO Test VALUES (1/3, 1/3, 1/3, 1/3, 1/3)
;
INSERT INTO Test VALUES (2/3, 2/3, 2/3, 2/3, 2/3)
;
INSERT INTO Test VALUES (3/5, 3/5, 3/5, 3/5, 3/5)
;
INSERT INTO Test VALUES (3/5, 3/5, 3/5, 3/5, 3/5)
;
SELECT SUM(dec_col), SUM(cur_col),
SUM(sng_col), SUM(dbl_col), SUM(int_col)
FROM Test
;

Jamie.

--
 
Back
Top