Lessons

Resources

Questions?

Please feel free to post your questions or comments below. If you have questions about a specific lesson, please specify the lesson number and the time index in the lesson where the issue occurs. Thank you!

Subscribe for Updates

If you would like to be notified whenever changes are made to this page, new lessons are posted, or comments are made, click on the subscribe button.

Subscribe to Access 306
Get notifications when this page is updated

Intro

In lesson 306, you will learn how to calculate values using lookups in Microsoft Access based on multiple criteria, including how to use the DMAX function and review DLOOKUP for more complex scenarios. I will show you several ways to calculate employee commission rates and pay rates, such as single and variable rates by employee, by order amount, or by combining multiple factors like employee and order amount in a lookup table. We'll also cover calculating pay rates by date, handling regular and overtime hours, the use of Me.Requery versus Me.Refresh, fixing query issues with form values, and how to work with nested IF functions.

Transcript

Welcome to Microsoft Access 306, Access Developer Level 6, brought to you by AccessLearningZone.com. I am Richard Rost, your instructor.

In today's class, we're going to learn how to calculate values based on looking them up in a table, but not just any old lookup in a table. We've used the lookup before when we looked up, let's say, a customer's address, but this is going to be based on some multiple criteria, and we're going to use a new function called DMAX, which is similar to the lookup, but not exactly the same. It looks for the largest value out of a range in a table.

We're going to calculate employee commission rates. In other words, we've got employees, we've got orders, we've got sales reps, and we need to be able to calculate a commission for them based on the order.

We'll do it a couple of different ways. We'll do it based on a single commission rate. I want to say it's 3, and we'll do it based on a different rate for each employee. Joe gets a 2 rate, and he gets a 3 rate. Then we'll do it based on the order amount. For orders over 100 bucks, you get a 2 commission, everything else you get a 3 commission.

And then finally, we'll do it by order amount and employee in a lookup table. We'll have a list that says Joe gets, for orders under 100 bucks, this rate, for orders over 500 bucks, this rate, and so on. Whereas Jane gets this and this. So we'll show how to do it in a very complex way.

Then we'll do the same thing with pay rates. We'll calculate pay rates based on dates and hours worked. We know Joe worked nine hours this day. Well, based on the date that he worked on, this is how much he gets paid. So you can track pay rates as of January 1st; you get paid this much, but on June 1st, you get a raise.

I'll show you how to calculate regular hours versus overtime hours, and a whole bunch of cool stuff. New function, DMAX, we'll cover that today. We'll go over DLOOKUP again and do some extra stuff with it.

We'll go over Me.Requery. We need that requery and how it's different from Me.Refresh. The reason we need to learn that is because sometimes you might have a form that pulls in data, but the data needs a value on the form to calculate itself. So we'll see how that works.

Sometimes you might have had an underlying query based on a certain value on the form, like a checkbox. But if there's no value in the checkbox, then it can't run. So we'll see how to fix that problem.

And also, one about nested IF functions; the IF function we covered before, I think, in Access 207. Now I'm going to show you how to nest them so you can do multiple ones together.

This class follows Access 305. I recommend you take that one first. We'll be using Access XP again in this lesson. Watch all the lessons through completely the first time before doing them. Don't just try to do this stuff with me.

This one, more so than other ones previously, because, for example, with the commission rate, I'm going to show you how to do it four different ways. But really, each one of those leads up to the last one, which is the way we're going to really do it. So if you want to run through all those examples, go ahead, but the last one is the one we're going to keep. We're going to leave the previous three just to lead up to it and show you how it works.

So, watch the lesson through first. If you decide you want to do all the stuff I did, then go ahead and do it.

The sample database files are at AccessLearningZone.com/access306, also in the help folder. Do the examples, at least the final one. You'll learn better if you actually do this stuff with your database.

Quiz

Q1. What is the main function introduced in this lesson for looking up values based on multiple criteria?
A. DMIN
B. VLOOKUP
C. DMAX
D. SUMIF

Q2. What business scenario is used as a primary example in this class to learn about table lookups?
A. Customer address verification
B. Calculating employee commission rates
C. Summing sales by region
D. Managing product inventory

Q3. Which of the following represents a complex commission calculation discussed in the lesson?
A. A single flat rate for all employees
B. Different commission rates for orders over a certain amount per employee
C. The same rate for every order regardless of amount or employee
D. Randomly assigned commission rates

Q4. Besides commission rates, what other type of calculation is demonstrated in this lesson?
A. Vacation time tracking
B. Calculating pay rates based on dates and hours worked
C. Employee benefits administration
D. Sales tax calculations

Q5. When describing Me.Requery and Me.Refresh, what is their main difference according to the lesson?
A. Me.Refresh reloads the entire table, Me.Requery only updates visible fields
B. Me.Requery updates underlying query data, Me.Refresh updates current record display
C. Me.Requery is used to save data, Me.Refresh is used to delete data
D. There is no difference between the two

Q6. Why does the instructor recommend watching the lessons through before doing the examples?
A. To memorize all the steps before practice
B. Because each step builds toward the final solution
C. To avoid making mistakes with the demo database
D. To save time on assignments

Q7. What is suggested about the sequence of Access courses?
A. You should start at any course level
B. Access 306 is best taken after Access 305
C. Access 206 is a prerequisite for Access 306
D. The order of courses is not important

Q8. What is the purpose of using nested IF functions, as referenced in the lesson?
A. To simplify calculations
B. To combine multiple conditional checks
C. To automatically back up the database
D. To run macros more efficiently

Q9. Where can sample database files for this class be found?
A. Only on Microsoft's official site
B. At AccessLearningZone.com and the help folder
C. They are not provided
D. In the Access user manual

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

Today's video from Access Learning Zone is Microsoft Access 306, Access Developer Level 6. I'm Richard Rost, your instructor for this course.

In this lesson, we are going to explore how to calculate values by looking them up in a table using multiple criteria. You may already be familiar with using lookup functions to retrieve data, such as pulling a customer's address. However, today we'll go a step further by using criteria-driven lookups and introducing the DMAX function. DMAX is similar to a lookup, but instead of simply finding a matching value, it searches for the largest value within a specified range in a table.

Our primary focus will be calculating employee commission rates. We will look at multiple ways to determine commissions for sales reps based on the order details. First, we'll work with a simple example where one flat commission rate applies to everyone. Next, we'll see how to use unique commission rates for each employee so that, for example, Joe gets one rate, Jane gets another, and so forth. After that, we will add another layer by varying the commission rate based on the order amount. Orders over a defined threshold (like $100) can have one rate, while those below get another.

We'll build on this by bringing together both the employee and the order amount in a lookup table. This table might specify, for example, that Joe gets a certain rate for orders under $100, a different rate for those over $500, and so on, while Jane has her own set of rates. This approach leads to a complex but very flexible way to determine commissions.

After covering commissions, we'll apply similar techniques to calculating pay rates. Here, the calculations will factor in dates and hours worked. Think of situations where an employee's pay changes on specific dates, such as receiving a raise on June 1st. You'll learn how to determine the correct pay based on when the work was completed, as well as how to track and calculate regular versus overtime hours.

A key part of this lesson is introducing and using the DMAX function, along with reviewing and expanding on DLookup. I will also explain the differences between Me.Requery and Me.Refresh, and why and when you should use each. Many forms need to be updated based on values the user enters, and understanding these actions will help ensure your forms return valid results as users interact with them.

We'll examine how to handle queries where a certain field, like a checkbox, can prevent the query from running unless a value is present. I'll show you methods to work around this issue.

Another important technique you will learn is how to use nested IF functions. We have previously covered the IF function in lessons like Access 207, but today we'll go further and nest them to handle more complex logic.

This course builds directly on what we covered in Access 305. If you haven't yet taken that class, I recommend starting there first. Once again, we will use Access XP. Make sure to watch all the lessons in this course from start to finish before trying out the techniques yourself. This is especially important today, as the commission calculation portion involves several methods, each leading up to the best and final approach. While trying out each example can be helpful, understand that the last example is the one I suggest you use in practice.

You can download the sample database files from my website at AccessLearningZone.com, under access306, or in the help folder. Be sure to complete at least the final example by following along with your own database. Doing so will help solidify what you learn in this lesson.

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

Calculating commission rates for employees
Commission calculation by single fixed rate
Commission calculation by individual employee rate
Commission calculation based on order amount
Commission calculation using order amount and employee from a lookup table
Creating and using a commission rate lookup table
Calculating pay rates based on date and hours worked
Tracking employee pay raises by date
Calculating regular hours and overtime hours
Using DMAX function to find maximum values in a table
Advanced usage of DLOOKUP function
Difference between Me.Requery and Me.Refresh
Ensuring form values are available for calculation
Handling queries that require form values
Using nested IF functions for multiple conditions