Solved Query issue with currency and extra pennies (1 Viewer)

[mistyinca1970]

I have converted a beast of a spreadsheet into an access database. I am working on a query that is being used for an access report. Here's the gist of what it does:
The amounts in here are currency, and there is a specific amount, so coming up a few pennies more or a few pennies less is not acceptable.
There are 8 sums of money, each of these sums is distributed based on a set of percentages (all add up to 100, of course). When I apply distribution percentages to each of these sums, I get a distribution amount. Now when the distribution amounts are added back together (in the report), they often add up to a penny or two more or less than the total, finite sum. I realize this is an issue of multiple hidden places behind the decimal (fractions of a penny) adding back together. But I need to find out how to overcome this.
I have tried using or not using the Round() function. This is not solving the problem.

Is there another function I can try?

This is the query:

SELECT tblDistribution.DistYear, tblDistribution.Order, tblDistribution.ZoneOrder, qrySysEnhancDistribution.Zone, qrySysEnhancDistribution.AvgOfDistribution, tblDistribution.City, tblDistribution.Distribution, Round(([AvgOfDistribution]*[Distribution]),2) AS DistributionAmt, [AvgOfDistribution]*[Distribution] AS NotRounded, "April " & ([DistYear]-1) & " - March " & [DistYear] AS AnnualReport, [DistributionAmt]-[NotRounded] AS Difference
FROM qrySysEnhancDistribution INNER JOIN tblDistribution ON qrySysEnhancDistribution.EffectiveZone = tblDistribution.EffectiveZone
WHERE (((tblDistribution.DistYear)=[Forms]![frmSysEncReport]![cboReportYear]))
ORDER BY tblDistribution.Order, tblDistribution.ZoneOrder;

(note that in the AvgOfDistribution field, there aren't any hidden decimal places)

Then in the Report, the DistributionAmt field is summed per Zone and a grand total. I'm finding the Zone totals and grand totals are off a penny and sometimes add up to a few pennies more than the actual total.

Here's an example of the math: The query that produced the following 7 figures started with a sum of $227,380.85. As you can see from the snip from the report below, the amounts now add up to $227,380.86. This is with using the Round() function in multiplying $227,380.85 by each of these percentages.

What can I do? Thank you!

 

[theDBguy]

Hi. Sorry to ask the obvious question but just wanted to confirm it first before offering any suggestions, are you using Currency fields in the table? I know you said the query data are currency, but I am asking about the data type in the table. Thanks.

 

[Pat Hartman]

note that in the AvgOfDistribution field, there aren't any hidden decimal places

There are ALWAYS hidden decimal positions. Double and single are floating point and the currency data type is fixed at four decimal places REGARDLESS of what you are viewing. This is not a case of what you see is what you get. In order to limit to two decimal places, you must round the results at each step.

Back in my mainframe days my employer managed investment portfolios for multi-million dollar clients. We had to store 4 decimal places but display 2. Our solution was to round to 2 for display and to calculate the total, we summed the rounded amount rather than the stored amount so that no client who added up the list would see a discrepancy of a penny or two and question our arithmetic

 

[The_Doc_Man]

As you can see from the snip from the report below, the amounts now add up to $227,380.86.

And yet if you compare line-by-line for the amounts from the first and second tabular displays, the individual (line-item) amounts are the same. Further, the even/odd method shows that you have all EVEN numbers as the last digits of the line items. Why do you think the total should be an ODD number? That violates basic math.

My other question relates to the percentage column. What number type is that? If it is SINGLE or DOUBLE then one of your intermediate numbers is catching a round-off issue. I would do ALL SEVEN of those percentage computations (well, ... six actually, since two of the rows use the same percentage) by hand to see what you think they SHOULD be. Then work forward from there.

 

[mistyinca1970]

Hi. Sorry to ask the obvious question but just wanted to confirm it first before offering any suggestions, are you using Currency fields in the table? I know you said the query data are currency, but I am asking about the data type in the table. Thanks.

I just double-checked. All money values in the table are in fact designated as currency. I also double-checked the queries and found a few places where the property sheet for a field in a query didn't designate as currency, but it was pulled from the table field in currency data type. I went ahead and added it to the query as well, but it made no difference in the total on the report.

 

[mistyinca1970]

There are ALWAYS hidden decimal positions. Double and single are floating point and the currency data type is fixed at four decimal places REGARDLESS of what you are viewing. This is not a case of what you see is what you get. In order to limit to two decimal places, you must round the results at each step.

Back in my mainframe days my employer managed investment portfolios for multi-million dollar clients. We had to store 4 decimal places but display 2. Our solution was to round to 2 for display and to calculate the total, we summed the rounded amount rather than the stored amount so that no client who added up the list would see a discrepancy of a penny or two and question our arithmetic

What I meant by that is that this particular field there is no value past the 5. When you put your cursor in the record, it will expose the digits after the decimal (see second pic). The value in the first pic does not. Not that the first value has anything to do with the issue.

I will try a test by going back through any queries and seeing if any need to add the Round() function to and see if that has an effect on the bottom line.

 

[theDBguy]

I just double-checked. All money values in the table are in fact designated as currency. I also double-checked the queries and found a few places where the property sheet for a field in a query didn't designate as currency, but it was pulled from the table field in currency data type. I went ahead and added it to the query as well, but it made no difference in the total on the report.

Hi Misty. Thanks for verifying that.

Just for fun, I created the following table and query.

As you can see, there are some "hidden" figures at play here.

Here's the result of using the Immediate Window.

 

[mistyinca1970]

And yet if you compare line-by-line for the amounts from the first and second tabular displays, the individual (line-item) amounts are the same. Further, the even/odd method shows that you have all EVEN numbers as the last digits of the line items. Why do you think the total should be an ODD number? That violates basic math.

My other question relates to the percentage column. What number type is that? If it is SINGLE or DOUBLE then one of your intermediate numbers is catching a round-off issue. I would do ALL SEVEN of those percentage computations (well, ... six actually, since two of the rows use the same percentage) by hand to see what you think they SHOULD be. Then work forward from there.

The total should be the same as the original amount I started with. I am starting with an ACTUAL $227,380.85, applying percentages that add up to 100% and then adding those calculated amounts back together and getting $227,380.86-- one additional penny. So this won't work.

Here is the percentage column.

 

[CJ_London]

simply set your number of decimals to 4 for your not rounded column and I'm pretty sure you will see something like $52,297.6031 or $52,297.5973

 

[mistyinca1970]

Hi Misty. Thanks for verifying that.

Just for fun, I created the following table and query.
View attachment 92276
View attachment 92277
As you can see, there are some "hidden" figures in play.

Here's the result of using the Immediate Window.
View attachment 92278

Yes. However, when this report goes public, all the values in that column have to accurately add up to the $227,380.85.
FWIW, I could never find a rounding function in excel that would fix it in the past. I solved the issue by figuring the first 6 percentages and then subtracting the sum of those from the whole, leaving the remaining amount for the final one. But I'm trying to find a cleaner way to run this in the DB that will produce a reliable result year after year without having to resort to such "tricks" (not to mention an access report would be significantly complicated trying to set it up that way).

 

[mistyinca1970]

simply set your number of decimals to 4 for your not rounded column and I'm pretty sure you will see something like $52,297.6031 or $52,297.5973

Unfortunately, this is REAL money I'm working with, and we won't be cutting drafts with the 3rd and 4th decimal places. I am bound to working with a $0.00 format for the end result. The numbers on the report have to add up properly. Everyone is going to get a piece of the pie, and the numbers have to match and not exceed the pot.

 

[theDBguy]

Yes. However, when this report goes public, all the values in that column have to accurately add up to the $227,380.85.
FWIW, I could never find a rounding function in excel that would fix it in the past. I solved the issue by figuring the first 6 percentages and then subtracting the sum of those from the whole, leaving the remaining amount for the final one. But I'm trying to find a cleaner way to run this in the DB that will produce a reliable result year after year without having to resort to such "tricks" (not to mention an access report would be significantly complicated trying to set it up that way).

Does the first section in this article apply to your situation?

How to Round in Accounting (chron.com)

 

[The_Doc_Man]

OK, here is where the rubber meets the road.

How (IN EXCRUCIATING DETAIL) are you computing those percentages? What data type is the percentage? What data type is used to hold any of the intermediate values? Note that due to potential rounding at the intermediate step, the following formulas are not necessarily the same, where PC is the percent expressed as nn.n (i.e. 18.50 ) and BaseAmount is a large number:

A = ( PC * BaseAmount ) / 100.0 ... = PC * BaseAmount / 100.0
B = BaseAmount * ( PC / 100.0 ) ... = ( PC / 100.0 ) * BaseAmount
C = ( BaseAmount / 100.0 ) * PC ... = PC * ( BaseAmount / 100. )

For small numbers, these should all give the same results. But as the BaseAmount gets larger, they might diverge in the last decimal place.

 

[CJ_London]

we won't be cutting drafts with the 3rd and 4th decimal places

wasn't expecting you to - just helps to identify where the problem is so you can take the appropriate remedial steps. It's the same problem of distributing $1000 equally to 7 people - each gets (on your basis) $142.86 - but that * 7 = $1000.02 - so two people will need to be short changed by a cent, or one person by 2 cents because $1000/7=$142.8571429 and not $142.86.

You need to ensure all calculations are rounding to 2 dp (not format, use the round function)

then you need two queries. The first to calculate the difference between the sum of the calculations and the initial value which is pretty straight forward. The second query is used to uniquely identify which of the calculated values is going to be modified for this difference. You have to have some rules for this - you could use the first or last record for example or perhaps a record designated in some way. I wouldn't recommend the one with the largest or smallest amount since there is a risk that you have two calculations with the same amount so would not be unique.

Finally use another query to combine these to give your final result.

Alternatively do this the other way round

calculate the values to 2dp (using the round function)

then a query to sum these to give a potentially adjusted base figure

All depends on what this is about. If this is an accounting figure that must balance on both sides for a journal for example, use the first method. If it is for reporting purposes, you could use the second.

 

[mistyinca1970]

OK, here is where the rubber meets the road.

How (IN EXCRUCIATING DETAIL) are you computing those percentages? What data type is the percentage? What data type is used to hold any of the intermediate values? Note that due to potential rounding at the intermediate step, the following formulas are not necessarily the same, where PC is the percent expressed as nn.n (i.e. 18.50 ) and BaseAmount is a large number:

A = ( PC * BaseAmount ) / 100.0 ... = PC * BaseAmount / 100.0
B = BaseAmount * ( PC / 100.0 ) ... = ( PC / 100.0 ) * BaseAmount
C = ( BaseAmount / 100.0 ) * PC ... = PC * ( BaseAmount / 100. )

For small numbers, these should all give the same results. But as the BaseAmount gets larger, they might diverge in the last decimal place.

The percentages are provided to me by one of our providers. They are exactly as they appear to be because I have hand-typed all of them into my original table. By that I mean, when I read her list that says: 23.0%, 18.5%, 16.0%, 2%, 25.5%, 7.5% and 7.5% (which adds up to 100%), they aren't really 22.97 or 18.48, etc. because I have hand-typed them into my table. So however she arrives at them has no bearing on my calculations. All that matters is that each respective zone adds up to 100, and they all do.

I have a big pie of fees we have collected, and they provide to me the percentages that dictates how much of the pie I distribute to whom, and since the entire pie is over $1m, minute amounts add up to a few pennies.

 

[Pat Hartman]

@mistyinca1970,
Did you read my explanation and suggestion? It doesn't lose the fractions, Unless you are going to calculate the percentage, actually round to decimal places and SAVE the result, you will find minor errors using your method. They are not going away. You can explain them to the client or you can fudge them to smooth over the rough edges. If you want accuracy, show the four decimal digits, everything will add up. Then round the total.

For now, remove ALL formatting so that you see all the decimal places. You will see that the result may or may not add up. Some days it will, some days it won't. Show it to management. Explain my company's solution. It doesn't affect ANY stored number. It is simply a way to make the rounding error disappear on the report.

Trust me, this is not a new problem. My solution was from 1978.

 

[mistyinca1970]

wasn't expecting you to - just helps to identify where the problem is so you can take the appropriate remedial steps. It's the same problem of distributing $1000 equally to 7 people - each gets (on your basis) $142.86 - but that * 7 = $1000.02 - so two people will need to be short changed by a cent, or one person by 2 cents because $1000/7=$142.8571429 and not $142.86.

It is ok if one or two get a penny less; that is to be expected. I just need a consistent methodology and function for achieving that and not going over my exact amount.

You need to ensure all calculations are rounding to 2 dp (not format, use the round function)

All calculations (sums as well as multiplications)? Because I wouldn't think the round function would have an effect on sums.

then you need two queries. The first to calculate the difference between the sum of the calculations and the initial value which is pretty straight forward. The second query is used to uniquely identify which of the calculated values is going to be modified for this difference. You have to have some rules for this - you could use the first or last record for example or perhaps a record designated in some way. I wouldn't recommend the one with the largest or smallest amount since there is a risk that you have two calculations with the same amount so would not be unique.

Finally use another query to combine these to give your final result.

So I actually currently have 6 queries that arrive to this point. The numbers are aggregated and then sliced and diced in different ways a couple of times (welcome to the world of overlapping contracts!). The 5th query is what provides the "pies" for distribution and those numbers are sound as I have validated them against my pivottable in my old spreadsheet. There are not multiple (hidden) decimal places on those numbers; just two and they all end in 5 or 0. So the issue is here at the percentage, (and as you said, how dividing 1000 by 7 is going to leave a couple of odd pennies). And that's where I need a consistent formula that addresses what to do with the last pennies so we don't go over the actual amount.

After the 6th query, the figures are then summed in the report.

So applying your method, I think I would be taking the 6th query (the one I've posted above in this thread) and launching from there. This is not all different from the method I described above how I used to handle this issue when I did the report in Excel.

 

[mistyinca1970]

Did you read my explanation and suggestion? It doesn't lose the fractions, Unless you are going to calculate the percentage, actually round to decimal places and SAVE the result, you will find minor errors using your method. They are not going away. You can explain them to the client or you can fudge them to smooth over the rough edges. If you want accuracy, show the four decimal digits, everything will add up. Then round the total.

For now, remove ALL formatting so that you see all the decimal places. You will see that the result may or may not add up. Some days it will, some days it won't. Show it to management. Explain my company's solution. It doesn't affect ANY stored number. It is simply a way to make the rounding error disappear on the report.

Trust me, this is not a new problem. My solution was from 1978.

Are we talking about my AvgOfDistribution field? I assure you, there are no hidden ones. By that I mean no minute amounts. If there are 4, then $227,380.85 is $227,380.8500. The DistributionAmt field and the NotRounded field (which I only added to compare when I round or don't round each), I acknowledge has the hidden additional decimal places. If that's the one we're talking about, yes...that is the field that is the problem.

Just trying to clarify what we're talking about here. (And yes I did read your explanation and suggestions. I had some subsequent questions before I go digging into 6 queries to find where to make the changes.)

And I would expect this is not a new problem. That is why I figure there must be a good solution for it. I was around in 1978 as well (though I was on the playground and not in an office )

 

[arnelgp]

I made a function that you can call
in a Query.

the only catch, is that you need to put
your query in a form (maybe datasheet)
and call resetfixDistribute() when the form/report
opens, so that the Collection object is
reset on the function.

on the demo, replace "Query1" on the function
(Const YOUR_QUERY = "QUERY1") with the
name of your Query you posted on Post#1.

create New query from your query (post#1)
and adding a Calculated column.
see my sample Query2.

 

[The_Doc_Man]

The percentages are provided to me by one of our providers. They are exactly as they appear to be because I have hand-typed all of them into my original table. By that I mean, when I read her list that says: 23.0%, 18.5%, 16.0%, 2%, 25.5%, 7.5% and 7.5% (which adds up to 100%), they aren't really 22.97 or 18.48, etc. because I have hand-typed them into my table. So however she arrives at them has no bearing on my calculations. All that matters is that each respective zone adds up to 100, and they all do.

I have a big pie of fees we have collected, and they provide to me the percentages that dictates how much of the pie I distribute to whom, and since the entire pie is over $1m, minute amounts add up to a few pennies.

You misinterpreted my question, I believe, or perhaps I wasn't clear. The explicit percentages in that table you showed us ALSO have a data type that includes a decimal point. You confirmed that all money values were in fact defined as CURRENCY, and that is good. My question is not about money values. I'm asking "What is the data type of the column containing the explicit percentages?" I don't care that you typed them, imported them, somehow computed the percentages, or that they sprang forth from the brow of Zeus. What is that field's data type?

I ask that question because of something called "implicitly coerced type" that occurs in expression evaluation. There is a possibility that despite having an endpoint as CURRENCY, there might be a data path that does not STAY as CURRENCY. I was trying to decide if that is possible.