[AccessD] Access rounding scenario

Thanks Gustav:

We're going to end up adding the difference back ... even increasing the
precision doesn't help when we're reporting against a wide date range
containing a lot of detail records. 

Larry Mrazek
lmrazek at lcm-res.com
ph. 314-496-1645


-----Original Message-----
From: accessd-bounces at databaseadvisors.com
[mailto:accessd-bounces at databaseadvisors.com] On Behalf Of Gustav Brock
Sent: Tuesday, August 23, 2011 2:30 AM
To: accessd at databaseadvisors.com
Subject: Re: [AccessD] Access rounding scenario

Hi Larry

This is a classic. You have two options:

1. Use a higher precision (more decimals) when you calculate from the
percentages.

This may be acceptable for example if you perform a final rounding of the
total to, say, 25 cent.

2. Calculate to final error and spread this proportionally across the items
to correct the error to zero:

Calculated:
30.003
20.002
49.999
Error: 0.004

Corrected:
30.002
20.001
49.997
Error: 0.0

Further, it may be required to handle the special case of an uneven count of
equal shares, the classic being three equal shares:

Calculated:
33.333
33.333
33.333
Error: 0.001

You could by random correct one value by the error:

Corrected:
33.333
33.334
33.333
Error: 0.0

However, as accountants prefer predictable results, it's important to stick
to rules that will produce identical outputs for identical inputs. 

Thus, you should avoid any random method and find some other like adding the
0.001 error correction to the first or last line, to the line with the
highest total costs, the most "important" material (measured by activity,
customer classification or turnover, project duration, or whatever that
makes sense for your client), or something else you can explain to your
client in common language in few words.

These methods typically require a second run by code.

/gustav


>>> lmrazek at lcm-res.com 22-08-2011 18:16 >>>
Hi folks:

Here is the scenario. 

We have a shipping record (with details on where the shipment is going,
etc.).  Linked to this is the details table, containing line item details on
exactly what is in the shipment, plus, on occasion, an extra transport
charge.

For a report, the client would like to have the transport charge applied to
each item in the shipment.

Thus, if the shipment contains the following charges:
Material 1: $200
Material 2: $200
Material 3: $400
Transport:  $100
They'd want the report to show;

Material	Charge	Transport
Material 1: $200		$25
Material 2: $200		$25
Material 3: $400		$50

Currently, I'm trying to do this by calculating a ratio of the materials to
the total (material 1 = .25, material 2=.25, material 3= .50), and
multiplying this by the total transport.

While this works for the above numbers, I'm getting some rounding errors (I
think) when dealing with other numbers and I need the transport charges to
add up correctly.

Any hints about how to do this? 

Larry Mrazek
lmrazek at lcm-res.com 
ph. 314-496-1645


-- 
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com