Welcome

Welcome! Order Notes & Tiered Commissions

S M L XL FS | Slo Reg Fast 2x | Bookmark

Welcome to Microsoft Access Developer Level 4. In this course we will build on concepts from previous Developer levels. We will start by adding and editing notes on order details in the order form. We will then update the commission system to allow for commission rates based on the amount of each order, setting different rates for different sales amounts. Finally, we will finish the commissions by order form, showing how to select the sales rep and order ID, calculate the order amount and commission, set the paid date, add notes, and log everything to the commission table.

Navigation

Prev: D04
Next: Lesson 01

Keywords

Access Developer, VBA programming, order form notes, order details notes, editable product notes, commission system, employee commission rates, commission based on order amount, dynamic commission table, commission calculation, logging commissions, commis

Subscribe to Welcome
Get notifications when this page is updated

Intro

Transcript

Welcome to Microsoft Access Developer Level 4 brought to you by AccessLearningZone.com. I am your instructor, Richard Rost. This class follows Microsoft Access Developer Level 3. It is recommended that you have taken the Beginner, Expert, and Advanced series before beginning the Developer series, and then of course have taken Levels 1 through 3 before this one.

In this class, I will be using Microsoft Access Version 2016. Everything covered should work in Access 2010 and 2013 as well, and probably 2007 also.

My courses are broken down into various groups. There is Beginners, Experts, Advanced, and Developer. The Beginner level classes are for people who have never used a database before. Expert gets you into things like relationships and expert form building. Advanced mostly covers macros, a very short series, and then Developer is where we are at now, where we are covering VBA programming. Each series is broken down into different levels that are each at least an hour long. This is Developer Level 4.

I recommend that you build the database with me in class while following along with the videos. But if you want to grab my database off the website, there is the address. You can log on. You will need the password that you got when you registered for this class in order to download the database.

Now let's take a look at what is covered in today's class.

In lesson one, we are going to begin by adding notes to our order details in our order form. We have it where we have the product, the unit price, and the quantity. We have notes on our products, and I want to be able to bring those notes into each order so we can print them out on our invoices and such, or have the user be able to edit those notes. But right now we are not bringing them in or we are not giving our people a way to edit that. So in this lesson, we are going to learn how to do that.

In lesson two, we are going to update our commissions system. Currently, an employee gets one flat commission rate, so 3 percent is all they get. In this lesson, I am going to show you how to set up a table so that an employee can get commissions based on the order amount of each sale. For example, for something $400, you get a 3.5 percent commission. If he sells a $500 order, he gets a 4 percent commission. You can use this on a per order basis, or using the same techniques you can easily change just to adjust as monthly commissions or as annual commissions based on a list of values, not one static amount.

In lesson three, we are finishing up our commissions by order form. We are going to pick the sales rep, pick the order ID. The system will calculate the order amount, bring it up from the order, calculate the commission rate based on the table we created in the last lesson, pull up the order date. The paid date is going to be defaulted to today. It will calculate the commission. We can add some notes and then log it to our commission table and then update the entire form.

Quiz

Q1. What is the main topic covered in Microsoft Access Developer Level 4?
A. VBA programming and advanced database development
B. Macros and basic form building
C. Table design and simple queries
D. Basic relational databases

Q2. What should students have completed before starting Developer Level 4?
A. Only the Beginner series
B. Beginner, Expert, Advanced, and Developer Levels 1-3
C. Only Developer Levels 1 and 2
D. Only Access 2007 training

Q3. Which Microsoft Access versions are compatible with this course?
A. Only Access 2016
B. Only Access 2013 and 2016
C. Access 2010, 2013, 2016, and probably 2007
D. Only Access 2007 and 2010

Q4. What new feature is added to the order details form in lesson one?
A. Ability to change product prices
B. Bringing and editing product notes on order details
C. Adding customer photos
D. Assigning multiple employees to each order

Q5. What is the improvement made to the commissions system in lesson two?
A. Employees get paid hourly
B. Commissions can vary based on the order amount
C. Employees get promotions automatically
D. Commissions are removed entirely

Q6. In lesson three, what key information does the system bring up when a sales rep and order are chosen?
A. Customer address and shipping status
B. Product reviews and ratings
C. Order amount, commission rate, and order date
D. Payment method and supplier contact info

Q7. What is the default value for the paid date on the commissions by order form?
A. The order date
B. The first of the month
C. Today's date
D. The date the employee was hired

Q8. What is the recommended approach for students during the course?
A. Watch the videos without building a database
B. Skip the hands-on exercises
C. Build the database along with the instructor
D. Only review printed materials

Q9. What is required to download the sample database from the website?
A. An email confirmation
B. A password obtained at course registration
C. A Microsoft account login
D. No requirements, the database is public

Answers: 1-A; 2-B; 3-C; 4-B; 5-B; 6-C; 7-C; 8-C; 9-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

Welcome to Microsoft Access Developer Level 4 from AccessLearningZone.com. I'm Richard Rost, your instructor for this course. This class continues from Microsoft Access Developer Level 3, and I recommend you complete the Beginner, Expert, and Advanced series first, as well as Developer Levels 1 through 3 before starting this lesson.

For these lessons, I'm using Microsoft Access 2016; however, everything I cover should work just fine in Access 2010 and 2013, and probably in Access 2007 as well.

The courses are organized into several groups: Beginner, Expert, Advanced, and Developer. Beginners are designed for those completely new to databases. The Expert series introduces concepts like relationships and more advanced form design. Advanced classes mainly focus on macros, and then we reach the Developer series, where we look at VBA programming. Each of these series is divided into levels that are at least one hour long. This class is Developer Level 4.

I encourage you to build your database alongside me as you follow along with these lessons. However, if you'd prefer, you can download my sample database from my website - just log in with the password you received when you registered for the course.

Now, let's review what we'll be working on in this class.

In the first lesson, we will add notes to our order details on the order form. Currently, our order form displays the product, unit price, and quantity. While we have notes for our products, we do not have a way to bring those notes into each specific order or give users the option to edit them. This lesson will cover how to properly integrate and edit these notes so they can be included on invoices and made available for end-user modifications.

The second lesson will involve a major update to our commissions system. Right now, employees receive only one flat commission rate for each sale. With the improvements we'll be making, you'll learn how to structure a table so that an employee can receive commissions based on the size of each order. For instance, if an order is for 400 dollars the commission might be 3.5 percent, while a 500 dollar sale could net 4 percent. With these techniques, you will be able to apply commission rates for each order amount or adapt the system to handle monthly or annual commissions using a set of values rather than just a single rate.

In lesson three, we'll finish building our commissions by order form. This form will allow you to select the sales representative and the order ID. The system will then pull the order amount, calculate the correct commission rate from the table you set up in the previous lesson, and display the order date automatically. The paid date will default to today. The form will then calculate the commission, allow you to add notes if needed, record the information in our commissions table, and then refresh the whole form interface.

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 editable notes to order details in order form
Displaying product notes in order details
Printing notes on invoices
Creating a commissions table with multiple rates
Setting commission rates based on order amount
Calculating commission based on order amount
Creating a commissions by order form
Selecting sales rep and order ID in commissions form
Calculating commission rate from commission table
Logging commission records to commission table
Defaulting paid date to today in commissions form
Updating commissions form with new data