Rounding issue (1 Viewer)

R

rincewind_wizzard

Registered User.
Local time
Today, 09:38
Joined
Feb 7, 2018
Messages
23
Hi all, doing some work for the Finance team for their general ledger accounting. I have the following scenario:Salary of £1315.33 needs to be split across 3 account codes, each of which has a different percentage:
60% = 789.20
29% = 381.45
11% = 144.69

So, I've built a query to do this split - the problem is I end up with a penny too much - a total of £1315.34. I am using format to 'Fixed" in the query i.e. Format([Salary]*([position percentage]/100),"Fixed")

I'm really struggling to get the values to add back to the original amount. Any ideas people?

Cheers
Paul
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2013
Messages
16,610
calculate the first two based on your percentage, calculate the last one based on total minus the first two
 
R

rincewind_wizzard

Registered User.
Local time
Today, 09:38
Joined
Feb 7, 2018
Messages
23
CJ_London said:
calculate the first two based on your percentage, calculate the last one based on total minus the first two
Click to expand...

I can see your thinking, but that won't work in a query (will it?)
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:38
Joined
May 21, 2018
Messages
8,525
won't work in a query (will it?)
Click to expand...
I think it could but not easily.
I will assume in the table you have lots of accounts and it can be split in many different ways (3 was just an example).

I would create qryA like you did except replace the Format with CCUR so it is still a value not a string.

qryB returns the Top N-1 records per account. In your case 60% and 29% from qryA for that account.

qryC returns Top 1 of qry A ordered descending (thus the bottom one) and then the Salary - sum of qryB 789.20, and 381.45. The sum could be done in a subquery or dsum

qry D is the union of B and C
 
arnelgp

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 16:38
Joined
May 7, 2009
Messages
19,230
can you show your query sql.
 
C

CJ_London

Super Moderator
Staff member
Local time
Today, 09:38
Joined
Feb 19, 2013
Messages
16,610
Something like

Code: 
SELECT....round((Salary*AC1,2)) as AC1Amt, round(Salary*AC2,2) as AC2Amt, salary-AC1Amt-AC2Amt as AC3Amt
FROM....
or

Code: 
SELECT....round((Salary*AC1,2)) as AC1Amt, round(Salary*AC2,2) as AC2Amt, salary-round((Salary*AC1,2)) -round(Salary*AC2,2) as AC3Amt
FROM....
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:38
Joined
May 21, 2018
Messages
8,525
@CJ_London,
I might be wrong, but I believe they are records not fields for the position percentage.
 
MajP

MajP

You've got your good things, and you've got mine.
Local time
Today, 04:38
Joined
May 21, 2018
Messages
8,525
CJ London The round function seems to have done the trick - thank you
Click to expand...
NO it most certainly does not. By using Bankers round instead of a floor function you simply reduced how often this will occur. Makes me nervous if you are doing this for a financial team and you assume that three rounded numbers will equal the sum of those numbers. This is math not a database issue.

Simple example
Code: 
	Value	Round 2
1	0.253	0.25
2	0.254	0.25
3	0.254	0.25
Sum	0.761	0.75
Sum Round 2	0.76	0.75
I do not care what rounding scheme you employ, you cannot guarantee the sum will match the unrounded sum.
[/code]
So lets say the got 3% over time. And you round each time
Code: 
salary	percent		percent salary	Round Percent
99.9	0.03		2.997	3
99.9	0.03	0.06	2.997	3
99.9	0.03	0.09	2.997	3
99.9	0.03	0.12	2.997	3
99.9	0.03	0.15	2.997	3
99.9	0.03	0.18	2.997	3
99.9	0.03	0.21	2.997	3
99.9	0.03	0.24	2.997	3
99.9	0.03	0.27	2.997	3
99.9	0.03	0.3	2.997	3
99.9	0.03	0.33	2.997	3
99.9	0.03	0.36	2.997	3
99.9	0.03	0.39	2.997	3
99.9	0.03	0.42	2.997	3
99.9	0.03	0.45	2.997	3
99.9	0.03	0.48	2.997	3
99.9	0.03	0.51	2.997	3
99.9	0.03	0.54	2.997	3
99.9	0.03	0.57	2.997	3
99.9	0.03	0.6	2.997	3
99.9	0.03	0.63	2.997	3
99.9	0.03	0.66	2.997	3
99.9	0.03	0.69	2.997	3
99.9	0.03	0.72	2.997	3
99.9	0.03	0.75	2.997	3
99.9	0.03	0.78	2.997	3
99.9	0.03	0.81	2.997	3
99.9	0.03	0.84	2.997	3
99.9	0.03	0.87	2.997	3
99.9	0.03	0.9	2.997	3
99.9	0.03	0.93	2.997	3
99.9	0.03	0.96	2.997	3
99.9	0.03	0.99	2.997	3
99.9	0.01	1	0.999	1
			99.9	100

The difference is ten cents.

You still have to do what CJ proposed and account for the rounding in the last record.
 
Last edited:
R

rincewind_wizzard

Registered User.
Local time
Today, 09:38
Joined
Feb 7, 2018
Messages
23
I realised it was only a fix when I totalled up the split amounts and compared to the original total. I've now mapped out a way of doing it using queries. Sum the total of the split values, compare that to the original total, find the difference and use the difference to update one of the split values. It's a few queries but it will work.
 
You must log in or register to reply here.

Similar threads

R
Stop reports from rounding down
Replies
3
Views
441
Rushman21
R
G
Solved Minimum Values From A Set of Records For Each Occurrence Then Create Those Results in Another Table
Replies
14
Views
741
Gimlett
G
mistyinca1970
Solved Query issue with currency and extra pennies
2
Replies
29
Views
2,408
mistyinca1970
mistyinca1970
H
New to Microsoft Access world, should I invest the time learning it from scratch or go for the C#, SQL Server, and ASP path? Please advice
Replies
17
Views
1,338
gemma-the-husky
gemma-the-husky
C
Solved Error in sql code
Replies
8
Views
1,045
chizzy42
C

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Top Bottom