Hands on: A fraction more on fractions

I received an email from John Palmer who had a problem with fractions.

In his database, fractions are also represented as decimals – you can show the user the fraction but do the calculation on the decimal component.

Unfortunately, his data contains a large number of fractions which cannot be represented with complete accuracy in decimal format (thirds, for example). He asks whether these can be totalled accurately.

To illustrate the problem, imagine that we represent 1/3 as 0.33. If we then add the number to itself three times (0.33 + 0.33 + 0.33) we get 0.99. The correct answer is, of course, 1.0. We can try to represent 1/3 more accurately as 0.333, whereupon we get closer to the correct answer (0.999), but we never achieve total accuracy.

So, what can we do about fractions such as these? Well, take our two different answers of 0.99 and 0.999, respectively. We can try applying a rounding function. In Access (and most database engines) this function takes two arguments: one is the number to be rounded; the other is the number of decimal places to which the rounding is applied.

Round (0.99,2) returns 0.99 and round (0.999,3) returns 0.999. Crucially, however, round (0.999,2) returns 1.0. So we know it is possible to return the right answer, depending on the circumstances. The inevitable next question is, ‘What circumstances?’

It turns out that getting the correct answer depends on several factors:

• The number of decimal places you start with.
• The number of addition/subtraction operations you want to perform.
• The accuracy you ultimately require.

To test this I built a table containing 10 different decimal representations of 1/3. I then ran a query against it that multiplies every row by 3,000 (ie, performs 3,000 additions) then rounds the answer to three decimal places. If I start with seven decimal places or more, perform 3,000 additions and round to three decimal places, it yields the correct answer.

You can play with the sample database yourself (If you’ve bought the DVDedition of PCW you’ll find the database DBCAUG07.MDB on the disc. It’s also on our website.) but if, for example, you chose to round to four decimal places, then you need to start with at least eight decimal places. If you then also perform, say, 9,000 additions, then eight still appears to be fine, but 30,000 additions now require starting with nine decimal places.

This kind of test can give you a feel for the limits within which you can work. For example, if we reduce the number of operations to 1,000, we get answers such as 3333.333. Is this really the same as 3,333 and 1/3? No, but it’s close and may be close enough for your needs. In other words, using a rounding function can help considerably with whole numbers.

Like this story? Spread the news by clicking below:

 del.icio.us     Digg this     reddit!