Rounding Problem With Percentages

[Lady_Jane]

Hi everyone,
I am trying to merge together two databases.
The good database looks like this:
----------------------------------
tbl_CatchmentsCSL:
UID


CatchmentArea
...

tbl_Physiography:
UID
Region
Area (the area that particular region takes up within that particular catchment)
----------------------------------
I am trying to add data to these tables, tbl_catchmentsCSL is fine.
The new tbl_Physiography data differ in that "Area" is represented by a percentage of total catchment area, rather than a raw number.
I decided to convert this percentage to a raw number based on the total catchment area. So tbl_Physiography.Area was populated with tbl_catchmentsCSL.CatchmentArea/100 * percent.

If I run a query ((tbl_Physiography.RegionArea/tbl_CatchmentsCSL.CatchmentArea)*100) on these data to calculate the percentage of each region within a catchment based on the raw area, then the totals will add up to 100% for each catchment.

If, however, I calculate the sum of all the region areas (sum of tbl_Physiography.RegionArea grouped by UID) that number will NOT be equal to the total catchment area.

Can anyone suggest what may be going on here, and how to correctly convert from percentage to raw number?

Thanks,

[rpeare]

Can you give an example of the data itself and some sort of example of what your results are currently telling you?

I imagine it's a rounding error particularly if the catchmentArea is a very large number and you are rounding to an integer rather than a decimal place.

[Lady_Jane]

Here is the example I have been looking at:

tbl_CatchmentsCSL.CatchmentArea = 183,946,787.975

The original physiography table that I am trying to convert to raw area looks like this:
Region: 31, percent: 14
Region: 38, percent: 82
Region: 39, percent: 4
Which adds up to 100% Good

I ran a query to update tbl_Physiography like this:
Area = (CatchmentArea/100) * percent
e.g. for Region 31, Area = (183,946,787.975/100)* 14
If I take out my calculator and do the above calculation I get this:
25,752,550.3165
Access, however, got the following numbers:

tbl_Physiography:
Region: 31, Area: 16,555,210.9178
Region: 38, Area: 160,033,705.538
Region: 39, Area: 5,518,403.63925

These numbers added together do not equal the total catchment area. But if I run a query that converts these back to percentages...
e.g. for Region 31, (16,555,210.9178/183,946,787.975)*100
the results will equal to 100% once again, even though in a manual calculation it only equals 99%

All the number fields are double.

I hope this makes more sense.

[rpeare]

I'm assuming you're using integers to represent percentages based on your formula but if we use your numbers

CatchmentArea = 183,946,787.975
Percent = 14

So your formula for this line is

(183,946,787.975/100) * 14

which does not equal what you're saying, nor what I get when I perform the same calculation.

You are saying you're getting

16,555,210.9178

when I perform the same calculation I get:

25,752,550.3165

the other two areas are also off by a significant amount which leads me to ask, are there other portions or calculations that are going into this 'final' formula? It's the only thing I can think of unless you are simplifying your example and forgot to change numbers.

[Lady_Jane]

So your formula for this line is

(183,946,787.975/100) * 14

which does not equal what you're saying, nor what I get when I perform the same calculation.

You are saying you're getting

16,555,210.9178

when I perform the same calculation I get:

25,752,550.3165

Are you "performing" this calculation in Access or with a calculator?
I get 25,752,550.3165 with a calculator
and 16,555,210.9178 in Access.

[rpeare]

using both the calculator and a formula within access:

here's my example db.