Best data type for calculations (1 Viewer)

Hi have a requirement to use MS Access to store (in separate tables) cost data, and in separate tables, proportional allocation (values less than 1) so that I can do a multiplication in a query to multiply Cost x Allocation = Proportional Cost.

The Cost numbers are ok - I can use Currency or Double data type as I only need to go to 2 dps, but some of the proportional allocations might be small numbers like 0.00001234

I am aware from reading this forum that the floating point data types (eg Double) can give 'wrong' answers with calculations. The alternative is Decimal data type, but this has some issues with sorting etc, and some people advise avoiding it entirely.

I need to be able to reliably and accurately do multiplication (and division - to work out the proportional allocation fractions), so my question is: "Which data type should I use?" and a related follow up question: "When doing calculations in queries, should I force the data into a data type (eg cDbl) and if so, which one?"

Thanks everyone

the values evaluated by currency are accurate to 4dps, therefore 100ths of a penny.

I would use currency for the values, and double for the proportions.

The point is not so much with the currency evaluation, it's that the proportions may carry rounding errors. Depending on the absolute accuracy you require this may be an issue.

so eg 0.00001234 may actually be stored as 0.000012399999999965

Thanks Dave

I agree that the proportions are the ones to worry about. If I want it to be precisely 0.00001234 should I append trailing zeros 0.000012340000 to prevent it being stored as another value?

With a £10m value the difference between 0.00001234 and 0.000012399999999965 is 60p This is a small error, but they will add up, and might cause some nervousness if the sum of the parts does not equal the bottom line value.

Would I be better of using Decimal to store the proportions?

you can't append trailing zeroes.

the problem is that there are certain numbers which just cannot be represented precisely in a digital format. I don't know whether decimal would add the accuracy you need. Calculations with large numbers are just likely to show rounding differences.

Try it in excel and access for academic interest. - so if one gets nearer than the other. There may be no error at all - my example was just an illustration.

The other option would be to multiply out the proportions to integer values do the sums then divide the answer back to the correct scale. This would remove any rounding errors in the proportion value.

Minty - I might have a think about doing that. It did cross my mind too, but as someone else has suggested it, perhaps it isn't such a daft idea!

yeah, I thought so too, but the trouble with big numbers is that they overflow as well

A 4-byte integer can only go to about 2billion - which is 20 million times 100.
If you have a 8-byte long you should be OK. Does 64-bit access offer one?

Maybe currency goes to a bigger integer value than a longint.

MikeWo, here is what you need to know about Access "real" numbers. First, look up "Floating Point" on the web. There is a Wikipedia.ORG article of some interest.

Understand that when using a number in DOUBLE format, there is theoretically very little difference in precision between 0.00001234, 12.34, and 12340000.0 (not saying NO difference, though.)

A SINGLE number uses 32 bits as 1 bit sign, 8 bits exponent, and 23 bits mantissa, but with a trick called "hidden bit" normalization to give you 24 bits. 2^24 = 16777216, so a SINGLE number will generally be precise to 1 part in 16 million. That is roughly equivalent to 7 decimal digits and a fraction.

A DOUBLE number uses 64 bits as 1 bit sign, 11 bits exponent, and 52 bits mantissa, but again that becomes 53 bits via that hidden-bit trick. 2^53 is 8 x 10^15, or just over 15 decimal digits. In the USA, that number is 8 quadrillion.

Here is where the problems come into play. If you don't bother to provide a format, then default output routines for SINGLE or DOUBLE will give you 8 or 16 digits - but because decimal fractions don't work so well in binary, you run into headaches when you get to the fractional portion of ANY decimal number if you take it out to enough places.

Part of that is because dividing fractions by 10 is numerically equivalent to multiplying them by one tenth - and 0.10 in decimal is, in binary, 0.0001100110011001100...11001100... ad infinitum. That infinitely long fraction means that you can never get rid of the (infinitely long) residual remainder. All you can do is truncate the translation at some point.

The solution in most cases is to stick with DOUBLE and decide how many digits you need, then write a FORMAT( dbl, "####.####" ) type of statement for output. Let the text conversion routines work for you here.

The idea of doing computations as integers and then converting to DOUBLE after all else has occurred is absolutely possible if you take the time to do some re-scaling. If you were to do the fussy analysis and derivations required, you would learn that one of those old Summation equality rules would help. I'll avoid using special characters here, but basically SUM( a*x ) where "a" is a constant is known to equal a * SUM( x ), and you can treat that as a conversion where you do your math in integers where possible and then convert it when all else has been computed. The only down sides are to keep track of the scaling and to watch out for developing a number too large for your integer working variable.

Thanks The_Doc_Man. I have read explanations about this before, but it has never made sense, but now you describe the decimal as 0.10 as being 0.0001100110011... in binary, it does suddenly make sense! This stems from the assumption that dividing by 10 is the same as multiplying by 1/10 which is true in base 10, but not in base 2 when you have to round the base 2 number to a fixed number of digits.

I'll do some testing with FORMAT( dbl, "####.####" ) to see if this gets around the problem. If so, then I can set my table data types to be Double, and force the results to be double too.

Just one further note... There IS such a thing as a floating-point divide instruction, and dividing by 10.0 would SEEM to be dividing by an exact number. Yes? But if you do a DIVIDE by 10 (rather than multiplying by 1/10 th), you are dividing a binary-based representation by 10, which is the product of two prime numbers (2*5), and since 5 is neither even nor a power of 2, that is where the division gets "sticky."