Rounding Errors
By Richard Rost
4 years ago
How to Fix Rounding Errors in Microsoft Access
In this Microsoft Access tutorial, we're going to take a look at a common problem people have when rounding numbers in Access. Whether you're calculating sales commissions, tax, or anything with a fractional value, this tip is for you.
Luis from Austin, Texas (a Platinum Member) asks: I'm trying to calculate sales rep commissions for my store. Each sales rep gets a 15% commission. His manager gets a 7% commission. That leaves the store with 78% of the order total. However, after doing the math, the numbers aren't coming out right. Help!
Members
Members will see how additional rounding problems can occur with double values. We'll learn how to deal with the dreaded .00000001 problem. We'll write a little VBA loop to create and then fix the issue.
Silver Members and up get access to view Extended Cut videos, when available. Gold Members can download the files from class plus get access to the Code Vault. If you're not a member, Join Today!
 Recommended Course
Links
Keywords
access 2016, access 2019, access 2021, access 365, microsoft access, ms access, ms access tutorial, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #learn, #lesson, #training, #database, rounding errors, math errors, errors calculating commission, .000000000000001 problem with doubles, for next step
Intro In this video, I will show you how to fix a common rounding error in Microsoft Access, especially when calculating commissions, sales tax, or similar percentages. Using a real-world example, you'll see how rounding can cause totals to be off by a penny or more, and I'll walk you through the correct method to ensure your calculated values always match your expected totals. We'll cover why simply multiplying and rounding percentages can lead to problems, and how to solve this by subtracting commissions directly from the order total for accurate results.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. In today's video, I'm going to show you how to fix a common rounding error in Microsoft's Access, whether you're calculating commissions, sales tax rates, or any of that kind of stuff.
Today's question comes from Luis in Austin, Texas, one of my Platinum members. Luis says, I'm trying to calculate sales rep commissions from my store. Each sales rep gets a 15% commission. This manager gets a 7% commission, and that leaves the store with 78% of the order total. However, after doing the math, the numbers aren't coming out right. Help.
I did have Luis send me some screenshots and an email so I could see exactly how he's got his stuff set up. He has made one of the most common mistakes that I see new users in Access do. Let me walk you through what he did, and maybe along the way you could figure out the problem before I show you the solutions. Let me show you what he did, and Luis, don't feel bad. I probably see at least one or two people a month make the same mistake.
Before we get started, if you haven't watched my video on rounding, go watch that so you understand how rounding works in Microsoft's Access. You will find the link down below the video in the links section. You've got to find a little "more" link if you're watching on YouTube. They do a good job of hiding it right below the video. You'll see the little thing that says "more." Click on that and it'll open up. You'll find all kinds of links in there.
Here I am in my TechHelp free template. This is a free download up on my website. If you want to go grab a copy, you'll find a link down below. You don't have to, though. You can use any database you want. In this database, I've got customers. They have orders, and they've got order line items here called order details, and those add up to give us an order total. That's normally how we calculate our order totals, by adding up the line items.
But just for the purposes of this demonstration, I'm going to take the order table. I'm going to delete all of this stuff and I'm just going to put an order total right in here. Just for the purposes of class, order total. We're going to call it, and I'm going to make it a currency value. Save it and let's put some data in here.
We've got some order totals in here. Let's say we've got 100 and then 1000 and maybe a $10 order.
Now, Luis wants to calculate the commissions based on this. What were your commissions? 15%, 7%, and then 78% left for the store for profit. So let's make a query to do that.
So, create query design. Now this is how Luis set his up. See if you can catch the error.
There's the order table. I'm going to bring in order total. Now we're going to put our commission rates coming down here.
So, we'll do C1. Commission rate 1 is going to be: we're going to round the order total, multiply the order total times 0.15 for 15%, and we're going to round that to two decimal places. If I run it, there's the commission. You can format it as currency if you want. That's not important right now. So, there's the salesman's commission.
For the manager, we're going to just copy and paste this. C2, this is the manager's commission. He gets 7%. So we'll just change that to 0.07. I'll zoom in so you can see better: 0.07. That's what the manager gets. Run it, and there's the manager's commission. Looks good. Everything's fine so far.
So the store is left with 78% commission. Again, I'll copy and paste that. We'll call this one "store." The store gets the remaining 78%. Run it. Everything looks good.
Now just to verify that this adds up to that, you can do it in your head, but let's just put a little calculation in at the very end here. Let's put a total, which is going to be equal to C1 plus C2 plus store. So it should equal the same thing. This column should equal that column. Everything looks fine.
Let's try some more numbers. Let's say another order comes in, and it's $100.50. Look at that. See? Commission one, commission two, store total. The total of the three of those together is $100.51, so someone's getting an extra penny. That's not good. Let's try $7.50. Same problem. Now it's too low.
Take a second and see if you can figure out what I did wrong. Pause the video if you have to and figure out why that number is not coming out correct.
The problem has to do with the rounding. If you take these three numbers and round them, because Access uses bankers rounding, sometimes you might get two of those values that are rounded up, or maybe even all three of those get rounded up, and it might mess this number up. Or the three of them might get rounded down.
The way to fix that is to subtract commission one, subtract commission two, but then instead of multiplying by 0.78 because there's 78% left, subtract what is left from these two things from the order total to get what the store has left. So instead of saying the store gets that, what you want to say is the store gets the order total minus C1 minus C2. Don't try to put a percentage on that, and then you'll get what's left and it'll come out correct every time.
Because of the rounding, these might not be perfectly exactly adding up to the total. So keep that in mind, whether you're calculating commissions like this or I've seen people do it with sales tax. Usually, I've seen people do it with a sales tax and a discount. I've got my video that I did a couple weeks ago on making an order discount where you get that little discount amount down there. You have to round that off. In addition to that, if you also calculate sales tax, then your order total might not be exactly right. That's the reason why you want to subtract the discounts, subtract the sales tax, and then what's left is your order total. Don't try to calculate the order total as a percentage.
This is a very common mistake, Luis. I see it happen a lot. Members, in the extended cut, we're going to show you how to deal with the .000001 problem I like to call it. It's a rounding error that happens when you use doubles. Sometimes it's handy to use doubles for things, especially with percentages, instead of always using currency. But this guy creeps in and I'm going to show you what it is and how to deal with it in the extended cut.
Silver members and up get access to all of my extended cut videos. It's like 270 of them now. Gold members can download these databases and have access to the code vault. So, join today.
How do you become a member? Click on the Join button below the video. After you click the Join button, you'll see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up will get access to all of my extended cut TechHelp videos, one free beginner class each month, and more.
Gold members get access to download all of the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. You'll also get higher priority if you decide to submit any TechHelp questions to me and you'll get one free expert class each month after you finish the beginner series.
Platinum members get all the previous perks, plus even higher priority for TechHelp questions, access to all of my full beginner courses for every subject, and one free developer class each month after you finish the expert classes. These are the full-length courses found on my website, not just for Access. I also teach Word, Excel, Visual Basic, and lots more.
You can now become a Diamond sponsor and have your name or company name listed on a sponsors page that will be shown in each video as long as you're a sponsor. You'll get a shout out in the video and a link to your website or product in the text below the video and on my website.
But don't worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free.Quiz Q1. What was the main issue Luis experienced when calculating sales commissions in Microsoft Access?
A. Using incorrect percentage rates
B. Incorrectly summing line items in the order total
C. Introducing rounding errors by rounding each commission before subtracting from the total
D. Applying currency formatting incorrectly
Q2. What is the recommended way to accurately calculate the store's remaining commission after calculating other commissions?
A. Multiply the order total by the remaining percentage (e.g., 78%)
B. Use a lookup table for the store commission
C. Subtract the first two commissions from the order total
D. Round all commissions then use their sum as the total
Q3. Why does rounding each individual commission before adding them sometimes produce an incorrect total?
A. Access uses a different currency format than Excel
B. Access uses bankers rounding which may cause some numbers to be rounded up or down unpredictably
C. Percentages need to be recalculated for each order
D. The commission rates are not set up as constants
Q4. Which of the following commission rates did Luis use in his calculations for sales rep, manager, and store, respectively?
A. 10%, 5%, 85%
B. 78%, 7%, 15%
C. 15%, 7%, 78%
D. 7%, 15%, 78%
Q5. What should you do instead of multiplying the order total by the store's percentage to calculate the store's portion?
A. Divide the order total by three
B. Subtract the commissions for sales rep and manager from the order total
C. Round each portion individually to three decimal places
D. Use an estimated value for the store's percentage
Q6. What is a common mistake people make when calculating sales tax and discounts, as mentioned in the video?
A. Calculating discounts before entering order details
B. Adding the discount and sales tax percentages together
C. Trying to calculate the order total as a percentage instead of subtracting fees
D. Using integer data types for currency values
Q7. What format is recommended for storing order totals and commission amounts?
A. Text
B. Integer
C. Currency
D. Date
Q8. Why does Access sometimes have minor rounding errors, even after making calculations seem correct?
A. Due to integer overflow issues
B. Access uses floating point values which can introduce .000001-type rounding errors
C. Incorrect format settings
D. Because of duplicate records
Answers: 1-C; 2-C; 3-B; 4-C; 5-B; 6-C; 7-C; 8-B
DISCLAIMER: Quiz questions are AI generated. If you find any that are wrong, don't make sense, or aren't related to the video topic at hand, then please post a comment and let me know. Thanks.Summary Today's video from Access Learning Zone focuses on fixing a common rounding error in Microsoft Access, especially when calculating things like commissions or sales tax rates.
The question for today came from one of my Platinum members, who is calculating sales rep commissions for a store. The setup is as follows: each sales rep receives a 15 percent commission, the manager gets a 7 percent commission, and the remaining 78 percent is what the store keeps. However, after performing the calculations, the sums are not adding up correctly.
After reviewing some screenshots and emails, I noticed that this is a very common mistake, particularly among new Access users. Let me take you through the process so you can understand what went wrong and how to solve it.
First, make sure you have a solid understanding of how rounding works in Access. If you have not already done so, I recommend checking out my earlier video dedicated to rounding.
For this demonstration, I used my standard TechHelp free template, which is always available for free download on my website. However, you can follow along using any Access database you like. In my setup, I have a basic order table where I am just entering order totals as currency values for simplicity.
Let us say there are three sample orders: 100, 1000, and 10 dollars. The goal is to calculate the commissions for the sales rep and manager, then determine the amount left for the store.
A query is created to generate these calculations. The sales rep's commission is calculated as 15 percent of the order total, rounded to two decimal places. The manager's commission is 7 percent, also rounded. The store supposedly gets 78 percent, which is calculated the same way.
Looking at the results for these calculations, it seems at first that everything matches up. However, the problem appears with certain order totals. For example, if you have an order total of $100.50, when the commissions are calculated and summed, the total comes to $100.51, resulting in a one penny discrepancy. With another amount, the total could be too low. The root of the problem is with the rounding approach.
This issue arises because Access uses what is known as bankers rounding. Depending on the numbers involved, you may have one or more of the calculated commissions rounded up or down, leading to small but critical differences when you sum the results.
To avoid this, you should not simply calculate all the percentages, round them, and expect the results to always add up perfectly. Instead, after calculating and rounding the commissions, determine the store's share by subtracting the other two commissions from the order total. Do not try to calculate the store's portion as a fixed percentage. By subtracting the presently calculated commission values from the order total, you ensure that the sum is always exact and avoids rounding discrepancies.
This same problem comes up when calculating sales tax and discounts. If you round a discount and a sales tax amount separately, then try to add everything up, you may end up with a total that does not match the original. The best practice is to subtract the actual discount and tax amounts (after any necessary rounding) from the subtotal to arrive at the correct total, rather than attempting to use just flat percentages.
This is one of the more common errors in Access, and I encounter it frequently. For those with a Silver membership or higher, in today's Extended Cut, I will be covering another related issue I call the .000001 problem, which can show up when using double precision numbers instead of currency. I will explain how this subtle error pops up and how to handle it.
Silver members and up have access to all my extended cut TechHelp videos, along with other benefits such as a free beginner class each month. Gold members get to download the sample databases used in these tutorials, access my code vault with reusable functions, and enjoy faster responses to their questions, as well as an additional free expert class after completing the beginner series.
Platinum members receive all those perks plus even higher priority for support, full access to every beginner course I have for each topic, and one free developer course per month after finishing the expert series.
The main point is that the free TechHelp videos will always be available. As always, if you want to learn this technique in detail with step-by-step instructions, you can watch the complete video tutorial on my website at the link below.
Live long and prosper, my friends.Topic List Setting up order totals in an Access table
Creating a query to calculate commissions
Applying the Round function to commission calculations
Calculating multiple commission rates in a query
Formatting query results for commissions
Demonstrating the error caused by rounding individual commissions
Identifying discrepancies when summing rounded values
Explaining why rounding errors occur in Access queries
Correcting the commission calculation by subtracting values
Calculating remaining store profit after other commissions
Verifying total accuracy after correcting the formula
Best practices for handling rounding in commission and tax calculations in Access |
