Order Discount
By Richard Rost
4 years ago
Add a Discount Percentage Rate to Orders
In this Microsoft Access tutorial, I'm going to show you how to add a discount percentage rate to your order entry database and invoice.
Jerry from Tacoma, Washington (a Gold Member) asks: I've been using your Invoicing template for a while now. Thank you for making that available. How can I add a discount percent to the order, so if I'm having a 25% off sale it's easy to calculate?
Members
Members will learn how to set up automatic tiered discount rates based on the customer's total past sales. So if they've bought $10,000 worth of material, they get a 5% discount on future orders. If $20,000 then 7% and so on. We'll learn how to use the BeforeInsert event.
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!
 Suggested Courses
Links
Member Link
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, tiered discount rates, Discount calculation in ms access, How do you calculate a discount, Creating a discount, Calculate Discount Amount, Calculating percent discount, how to do discount on access, Access calculating discount rate, calculating sales tax
Subscribe to Order Discount
Get notifications when this page is updated
Intro In this video, I will show you how to add a discount percentage rate to your order form and invoice in Microsoft Access. You will learn how to modify your order table to store the discount percent, update your forms to display and calculate the discount, round values properly, and use a bit of VBA code to ensure your totals refresh automatically. You'll also see how to apply these changes to both your order entry form and your invoice, so the discount is reflected in your final totals.Transcript Welcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.
In today's video, I am going to show you how to add a discount percentage rate to your order form and invoice in Microsoft Access.
Today's question comes from Jerry in Tacoma, Washington, one of my gold members. Jerry says, I've been using your invoicing template for a while now. Thank you for making that available. You are welcome. How can I add a discount percent to the order? So, if I am having a 25 percent off sale, it is easy to calculate.
Well, Jerry, it is easy to add a dollar amount discount. If you give someone like a $10 credit, you can just type in negative 10 and make that a line item. But if you are going to do a discount percentage, it is a little more work, but it is not that hard. It is a little bit of math. Let me show you how to do it.
For the rest of you who have not watched the invoicing video, go watch this now. It is absolutely free. It is on my website. You can even download a free copy of this database if you want to. There is the link. I will also put a link down below in the description below the video. You can click on that. Go watch it. Go get a copy of the database. And here we go.
So, here is the template. You have the customer form. Each customer can have multiple orders. What I want to do is specify a discount percentage for the order as a whole. Then, down here, we will calculate what the discount should be, and then subtract that from the subtotal to get an order total.
The first thing we need is a place to store that information. We are going to put that in the order table. So, let us go to design view. The order table will come right down here. I am going to type in discount percent. We will make that a number, and then we will make that a double.
Remember, there are really only two types of numbers you have to worry about. There is long integer, which is the default type. It is just counting numbers: one, two, three, four, five, zero, and their negatives. If you want a fractional component like a percentage, you are going to use double. Do not use decimal. Do not use single. Just use double or long integer.
Format if you want. You can put percent in there. That is what places it up to you. If you only do whole discounts like 25 or 50, then you can set that to zero. Or if you might have a 1.5, it all depends on what you are doing. I just leave it at auto. It covers pretty much every situation. All right, save that.
Now let us go over to the datasheet view real quick. We have to put some values in here for the existing records. I am just going to put zeros in for everybody right now. If you have a bunch of orders in your table already and you do not feel like doing that manually, you can use an update query to update everybody to a zero percent. Again, if you have never done an update query before, I have a video for it. I will put a link to it down below in the description.
Now we are going to add that field to the order form. So, right click, design view. I am going to make some extra room in here. Let us stick it below the customer. I am going to select all this stuff here and just slide this down a little bit.
Let us go to the form design tab, go to add existing field. There is discount percent. We are going to drop it right there. I am going to use a format painter and just paint over that guy so it looks good. We could change the label to say discount with a percent sign because it is a little long. We will just squeeze that right there, slide you over a bit more, and there we go.
Make sure that is left aligned. Good.
Let us see what it looks like. Save it. Let us close it and open it back up again. Good, there is my discount percent. Now it does not do anything yet. If I type in a five, that is another thing I do not like. Since I just brought that field in, it is the last field in the tab order. When you tab past it, it is going to move to the next record. I personally do not like that. So, I am going to go into the form design one more time. Go right here where the form properties are. Go to other and I am going to change cycle to current record.
Current record just means it will loop back to the first field on the same record instead of going to the next one. So now if I come back in, your save changes, yes. Now if I come in here and I type in like a seven and hit tab, it just loops back up to the top. That is how I like this one.
Now, down below here in the footer of the subform, right here, remember we have to click there twice. If I click here once, now I have just selected the subform. I want to click in there a second time. Now I am working with the actual subform fields.
Let us make that smaller. I am going to put a label next to this now so we know what it is. I am going to copy one of these labels. Click on that guy and hit paste and that will attach that label right to that text box. This will now be the subtotal. I can rename that subtotal too. Let us call it subtotal and let us not bold it.
I am going to copy and paste this, copy, paste, copy it twice. All right, we have got two copies here.
The first one is going to be the actual discount in dollars. We will name this guy discount. What is this going to be? Let us go over to data. It is not going to be the sum of the extended price. What is it going to be?
The discount is going to be what times what? See if you can figure it out.
It is going to be this subtotal times this percentage rate. Then we have got to make it negative. So it is going to be equals subtotal times. Now we have to get the value off a different form. There are a couple ways you can reference it, but the easiest way is forms exclamation point order f, exclamation point discount percent. That is right there.
Save it. Close it. Open it up. Looks good, but look at that. If you click on it, it is deceiving because it is only showing it to you as currency, but it is rounding. You get that little fraction of a penny there. The view rounds it off. You cannot really see it. But if you click on it, I was hoping I would get that number.
So we have to round that value off now. So come back in here, design view. Come back into discount. I am going to shift F2 to zoom in so you can see this better. We are going to round this whole thing off. So, round, and then open parenthesis, and at the very end, comma two, and then off to two decimal places. We want to make it negative. If it is a 10 percent discount, it is going to be negative some value. So just times negative one.
If you do not know what this is, that form field notation, I have a whole video that explains that better. It is called form name. Look down in the link section below for that too. I also have a video on the round function. Go watch that. Lots of videos to get you to this point. So if you do not understand any of this stuff, go watch those videos. I will put a whole set of links down below for you.
All right, let us hit OK. This avoids the Superman 3 problem with the rounding of a penny, or Office Space, depending on which one you like better.
There we go. If you click here, notice it is exactly negative 3.88.
Now, we are going to figure out the total total. We have to take this and add that to it. We are adding a negative number, which is the same as subtracting.
So, design view. This will now be the order total. This guy will now be equals the subtotal plus the discount.
Yes, we will bold these ones. Let us bold those. Format, bold.
Let us see. I have good names for this stuff here. Discount, and, no, not text 17. We will call this order total.
Close it. Open it up. There you go. That is a beautiful thing. There is your discount.
Now, here is a little quirk with the way Access works. If you change some value in here, let us say you make this three cupcakes now. If you move off that record, watch the order total. See, it updates.
But if you come up here and change this to say 10 percent and tab, it does not change. It does not refresh these fields on here until you hit the refresh button up top. Now, it updated the discount. See? I come in here and put this back to five and then hit refresh; it updated the discount.
Do not be scared. I am going to teach a tiny little bit of VB. Remember, I always say learn a little teeny tiny bit of VB and it makes your database a whole lot more powerful. We can use one line of code in the right place to make that automatically refresh itself.
Watch this: design view, go to that field, go to events and go to after update, hit the dot dot dot button. You get the code builder. If you get a window up that says what builder would you like, pick the code builder.
Then in here, we are just going to say me dot refresh. That is it. That is all you have to put in there. That says after I update this field, refresh all the calculations on the form.
Save it. Close that. Close that. Open it back up again.
Now, if I come in here and I put in like a 10, let us watch the totals tab. There they go. See? It automatically refreshed all the calculations.
If you want to learn more VBA, go watch my free intro to VBA video. It is about 20 minutes long. It teaches all the basics. You will need to know a couple of different commands to make your database a whole lot more powerful.
Now, we have got to put the same thing in our invoice because right now our invoice does not have the discount.
Good news is we can use pretty much the same fields we had over here.
So, right click, design view. I am going to try and fit these both on the screen at the same time here.
Let us put the discount right over here. I am just going to copy this, copy and paste. It comes in over on the left. I hate that. Click and drag. Click and drag and sit here right there. Line these labels up a little bit.
Down below, you have to make a little room in here. We will do the same thing. I will grab a label, copy, click, paste, and that attaches it there. Let us match the font size. What is it? About 16 MSA, yes, 16. So we will come over here and make this guy 16 point. Make it a little bit bigger. Let us unbold this stuff.
I am just going to copy and paste this guy, copy, paste, paste. This will be the subtotal. This will be the discount. This will be the order total.
Let us set these guys up here too. Sum extended price, we are going to rename that to subtotal. This we are going to name discount. This we are going to name order total. Those are the names of the fields.
Now let us put the updated calculations in here. Discount, we are going to get from this guy. Let us open up his properties, double click, and we are going to copy that function right there. So shift F2, zoom in, select it all with the mouse, copy it. Let us close that, come back over to here, bring up your properties, and then paste that in the control source there. There is my discount.
This one was easy. This one was just the equals the subtotal plus the discount.
Save it. Let us close it. Sure.
I like to go back in from the top. So orders, invoice, and there we go. That should match that. Looks good. Oh, we have got the stupid box around that. I hate that box. I click design view. We will just go up here. It is easy to do. We will go to format, and we will go to shape outline, and we will set it to transparent. Save it. Close it. And one more try. There we go. Beautiful. Looks fantastic.
Now, if you want to learn more in the extended cut for the members, I cover tiered discount rates. What does that mean? Some of my students emailed me a little while ago and said, I like to give the customer a discount based on how much money they have spent with me in the past. So let us say for example, the customer has spent $1,000, then they get a 10 percent discount. If they spent more than $5,000, they get a 15 percent discount, and so on.
I will show you how when you create the order, it will automatically calculate how many total sales they have had in the past and make that the discount percentage for the new order.
If you really want to learn more about building an order entry system, in my Access Expert Level 8 and 9 classes I cover a lot more, including calculating sales tax, the proper rounding of values, final product and tax totals. We do taxable items, so you might have certain items on an order that are taxable and others that are not taxable, like if you sell groceries and clothes, for example.
Then in Expert Level 9, we do multiple sales tax calculations. For example, each customer can have their own taxable rate or whether they are taxable or not, because you might have certain customers that are tax exempt. You can have different rates for each order. That is all covered in Access Expert Levels 8 and 9. I will put links to these down below.
Check out the extended cut for the members. Silver members and up get access to all of my extended cut videos, over 200 of them now. Gold members can download these databases and have access to my code vault.
How do you become a member? Click on the join button below the video. After you click the join button, you will see a list of all the different membership levels that are available, each with its own special perks.
Silver members and up 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 will also get a higher priority if you decide to submit any TechHelp questions to me, and you will 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, too. 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 are a sponsor. You will 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 do not worry, these free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more, and they will always be free.Quiz Q1. Why does the order table need a new "discount percent" field?
A. To store the percentage discount applied to each order
B. To record customer phone numbers
C. To list individual product discounts
D. To store tax rates applied to orders
Q2. Which data type should you use for storing a percentage discount in the order table?
A. Short Text
B. Long Integer
C. Double
D. Currency
Q3. What is the reason for choosing "Double" over "Single" or "Decimal" for this field type?
A. Single is only for small numbers and decimal can cause errors
B. Double handles fractional values well, which is needed for percentages
C. Single allows alphanumeric values
D. Decimal fields are faster
Q4. How can you quickly set all existing orders to have a 0 percent discount?
A. By entering zeros manually for each order
B. By deleting all orders and starting over
C. By using an update query to set the value for all records
D. By exporting the table to Excel and back
Q5. What is the advantage of setting the form's "Cycle" property to "Current Record"?
A. It deletes the record after editing
B. It loops back to the first field on the same record when tabbing
C. It disables form navigation
D. It allows edits to multiple records at once
Q6. How is the discount amount calculated in the subform footer?
A. Subtotal minus tax
B. Subtotal times discount percent (from the main order form)
C. Extended price divided by discount percent
D. Order total times discount percent
Q7. Why should you use the ROUND function when displaying the discount value?
A. It converts the number to an integer
B. It makes sure the value is always positive
C. It rounds to two decimal places to avoid rounding errors with currency
D. It sorts discounts by amount
Q8. What is the purpose of making the discount calculation a negative value?
A. To show it as a deduction from the total
B. To increase the total
C. To mark the record as invalid
D. To reverse the sign for accounting
Q9. What VBA code is added to the "After Update" event of the discount percent field to refresh calculations?
A. Me.Requery
B. Me.Refresh
C. Form.Refresh
D. CalculateFields
Q10. What must you do to ensure the invoice shows the same discount information as the order form?
A. Manually enter discounts on the invoice
B. Copy and paste the discount fields and calculations from the order form to the invoice
C. Use only the subtotal on the invoice
D. Ignore discounts on invoices
Q11. How are tiered discount rates described in the extended cut for members?
A. Flat rate for every customer
B. Discounts based on total amount spent by each customer over time
C. Random discounts on each order
D. Weekly rotating discounts
Q12. Which Microsoft Access concepts are also mentioned as covered in higher-level courses (Expert Level 8 and 9)?
A. Printing reports only
B. Calculating sales tax, rounding values, taxable items, and multi-rate sales tax per customer
C. Video embedding
D. Creating user logins
Q13. What allows Gold and Platinum members to benefit more compared to Silver members?
A. They get free website hosting
B. They access more downloadable databases, code vault, and higher priority for questions
C. They get access to YouTube ads
D. They receive Microsoft Office for free
Q14. What is the "diamond sponsor" perk mentioned?
A. Access to free logos
B. Having your name or company listed on the sponsors page and in video credits with a website link
C. Free merchandise
D. One-year free membership
Answers: 1-A; 2-C; 3-B; 4-C; 5-B; 6-B; 7-C; 8-A; 9-B; 10-B; 11-B; 12-B; 13-B; 14-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 covers how you can add a discount percentage rate to your order form and invoice in Microsoft Access. This is a common request from users who want to easily calculate percentage-based discounts on their orders, for example if they're running a 25 percent off sale.
Adding a flat dollar discount is simple. You can just add a negative dollar amount as a line item. Percentage discounts, however, need a bit of extra work, but the process is straightforward. Let me walk you through the steps.
If you haven't yet seen my invoicing template video, I'd encourage you to visit my website and check it out. It's free to watch and there's a downloadable database you can use as a starting point.
The template uses a simple customer form where each customer can have several orders. What we want to do is add a field to the order so you can specify a discount percentage for the whole order. The system then needs to calculate the discount amount based on this percentage, subtract it from the subtotal, and show the final order total.
You need to start by storing the discount percentage in your order table. In design view, add a new field called 'discount percent' to the table. Set its data type to number, and then select double as the field size. For percentage values and decimal values, always use the double type instead of single or decimal. If you only ever plan to use whole numbers for discounts, you can set the decimal places accordingly, but leaving it at Auto will handle most cases.
Once you've added your new field, switch to datasheet view and enter a value for existing records. If you already have many records, you can use an update query to set all existing discount percent values to zero at once.
Now, add this new field to your order form. Make some space on the form and bring in the discount percent field from the field list. You can adjust labels and alignments to maintain a clean appearance. It can be helpful to change the form's tab order so that after you enter the discount percent, pressing tab keeps you on the same record, instead of taking you to the next record. You can do this by changing the form's cycle property to 'current record.'
In the order details section, typically within a subform footer, you want to display calculations including the subtotal, discount amount in dollars, and the final order total. To display the discount as a calculated field, multiply the subtotal by the discount percent, referencing the value from the parent form. It's important to make the discount value negative so it subtracts from the subtotal. Since rounding errors can occur with floating point numbers, wrap your calculation in the round function and round to two decimal places. This avoids issues with fractions of a cent.
If you're not familiar with how to reference controls from another form or how to use the round function, I have other tutorials on those topics linked on my website.
After setting up the calculations, make sure your discount field updates the totals automatically when you modify it. By default, Access does not automatically recalculate totals when you change the percent field. This can be solved by adding a tiny bit of VBA code. In the after update event of the discount percent field, add a single line to refresh the form. This ensures your totals recalculate immediately when you change the discount percentage. If you're interested in learning more VBA, I have a free introductory video that covers the basics.
Next, replicate these changes to your invoice form so the discount percent and calculations are also shown on printed or saved invoices. You can copy the controls and formulas you used on the order form over to your invoice form, making sure that the labels, calculations, and formatting match the look of your invoice.
In the extended cut for members, I go further and show how to create tiered discount rates. This is useful if you wish to reward customers based on their total spending history. For example, you can set thresholds where customers who have spent over $1,000 receive a 10 percent discount, and those over $5,000 might qualify for a higher percentage. I'll show you how to automatically calculate the customer's prior purchases and set the discount rate accordingly for new orders.
If you want a more advanced system, my Access Expert Level 8 and 9 courses cover other topics such as sales tax calculation, handling taxable and non-taxable items, and even giving each customer their own tax rate or tax status.
If you're interested in more content like the extended cut or want access to sample databases and code, consider joining as a member. Silver members and above get access to all extended cut TechHelp videos and a free beginner class each month. Gold members can download all the sample databases and get access to my code vault for different functions. Platinum members enjoy all those perks, plus additional priority support, access to all beginner courses for every subject I teach, and a free developer class monthly after you finish the expert courses.
But don't worry if you're not a member. All my free TechHelp videos will continue to be available. As long as people keep watching, I'll keep making more.
You can find a complete video tutorial with step-by-step instructions on everything discussed here on my website at the link below. Live long and prosper, my friends.Topic List Adding a discount percent field to the order table
Choosing the correct data type for the discount percent
Adding the discount percent field to the order form
Configuring tab order and form cycling
Calculating the discount amount in the order form
Using expressions to reference form fields
Rounding the discount calculation to two decimals
Calculating the final order total with discount applied
Automatically refreshing calculated fields after updating discount
Using VBA After Update event to trigger form refresh
Adding the discount percent and calculations to the invoice
Copying calculation formulas between forms
Formatting and aligning discount fields and labels in forms |
