Round( ) not correct

[67philb]

I am trying to create an invoice using an access report that shows subtotals of grouped products (with a rounded subtotal) which works perfectly, and then I want to sum these grouped rounded figures as a grand total in the report footer.
The problem is when I manually add the subtotal figures it doesn’t match to the grand total, I know it’s due to the rounding’s the grand total is calculating but I can’t figure out how to get around this.
Any help would be much appreciated.

 

[Jon]

Welcome to Access World! We're so happy to have you join us as a member of our community. As the most active Microsoft Access discussion forum on the internet, with posts dating back more than 20 years, we have a wealth of knowledge and experience to share with you.

We're a friendly and helpful community, so don't hesitate to ask any questions you have or share your own experiences with Access. We're here to support you and help you get the most out of this powerful database program.

To get started, we recommend reading the post linked below. It contains important information for all new users of the forum:

https://www.access-programmers.co.uk/forums/threads/new-member-read-me-first.223250/

We hope you have a great time participating in the discussion and learning from other Access enthusiasts. We look forward to having you around!

 

[67philb]

sorry, how do I post on the correct page?

 

[Pat Hartman]

Welcome. I moved the thread for you to an appropriate forum. Choose the forum first. Then push the create new thread button.

Rounding is always a problem when you don't show the actual value of each detail item. Most users don't bother to get out a calculator and check your math so most people don't ever even notice the tiny errors. You are more likely to notice the error if there are subtotals. If you total the subtotals, the result might not match the grand total if it is calculated differently. The fix is to have the grand total total the subtotals rather than the details. Another thing you should do is to round the calculations within the calculation. For example, if you show tax on each item, you can be accumulating a rounding error. So, round the tax calculation to two decimal places and sum the rounded amount rather than summing the unrounded value.

 

[DHookom]

I would consider rounding in the report’s record source. Then try total the rounded values in the footers.

 

[plog]

I can’t figure out how to get around this.

1. Don't round.
2. Don't round until the end.
3. Round at the record level in the query the report is based.

You've got 3 levels--1. Record level, 2. Subtotal level, 3. Total level. You can't round the middle independent of the others. Either that total level needs to add up the rounded values of level 2, or you need to round at level 1 and base level 2 and level 3 on those rounded values.

 

[67philb]

Hi Pat, thanks very much for the reply, and moving my thread (sorry).

You are right, normally I wouldn’t have checked the figures on a calculator but knowing it was for a main customer I wanted to make sure all the boxes were ticked.

“Have the grand total total the subtotals” how do I go about this? Because what I thought hasn’t worked, I know it will be something simple I’ve overlooked.

 

[Gasman]

normally I wouldn’t have checked the figures on a calculator

I would have always done that.

 

[DHookom]

You could try use a running sum on the subgroup totals. I’m not entirely sure this would work and not sure you even want to do it this way since it isn’t going to have the maximum accuracy.

 

[67philb]

You could try use a running sum on the subgroup totals. I’m not entirely sure this would work and not sure you even want to do it this way since it isn’t going to have the maximum accuracy.

never thought of a running sum, that might actually work for what I need. Thank you!!

 

[Pat Hartman]

I used the tax on each line as an example because multiplication and division can generate more decimal places than what you are showing which is typically just 2. Since we don't have any currency smaller than a penny, our currency only goes to two decimal places but if your tax is 3 decimal places 2.5% = .025, then you can end up with a result that is more than two decimal places but the report is going to print two and so that is what the customer is going to enter into his calculator. So, understanding where the "end" is becomes important. The "end" is anything that gets printed which is why I told you to do the rounding inside the calculation. we don't typically store the calculated tax. We store the tax rate. Procedurally we either calculate the tax on the order total or we calculate the tax on each line item. If you calculate the tax on each line item and you PRINT IT, then THAT is the amount that you need to sum in order for the total tax to equal what you see on each line. Prove this to yourself by changing the report temporarily to show 5 decimal places next to the printed amount. The longer the list of items, the more likely you are to end up with the sum of the printed tax amount not equaling the sum of the actual tax amount. The difference will be pennies but could be more depending on how many items get rounded up vs rounded down.

I had a slightly different problem when working with an application that managed investment accounts. In this case we actually stored four decimal digits but we printed only two. In this case our managment didn't want us to round and accumulate the rounded amounts. We needed to print the rounded amounts but the total needed to actually total all four decimal digits and then round to 2 places. this would always make the client upset if he used a calculator to sum up his positions and find they didn't = the total. To solve this problem, we did a pre-process to find the two largest holding amounts. We calculated the rounding error over the whole report and added half to one of the large amounts and the remainder to the second. So, the report always balanced and the positions changed enough in value from month to month that the client never saw that a couple of items were slightly different from what they were last month. His total holdings amount was always accurate but a couple of individual items absorbed the rounding amounts.

 

[The_Doc_Man]

Let's start by understanding that ROUND() has TWO purposes.

First, you round off things because you want to provide an approximation, or perhaps an order of magnitude number to represent the results of a larger data grouping. When used this way, ROUND is a FORMATTING function and you would do better to use the FORMAT( x, template ) method to limit what you show.

Second, you round off because you have fractions that you don't want to keep at ANY level of computation. For example, an interest or tax computation that works on dollars and cents even though the math would produce another couple of digits (fractional dollars x fraction tax rate = even smaller amounts of useless fractions). Here, you run into a couple of issues. First, is there a regulatory limit on what you can track? Second, is the data type you are using a reasonable method for tracking what you track?

If you have regulatory limits, you might have to apply ROUND() after each computation where such action is appropriate. But then there is the matter that DOUBLE data type retains as many fractional digits as will fit, even if those digits lose meaning (or the more proper term, "lose significance.") Sometimes you do better using a CURRENCY data type, which involves a scaled integer that keeps a fixed decimal point (at 4 places) and provides 15 digits to the left of the decimal. This data type takes up the same amount of space as DOUBLE (8 bytes) but is less likely to have a worrisome rounding.