Max Value
By Richard Rost
3 years ago
Max Value From Multiple Fields in Microsoft Access
In this Microsoft Access tutorial, I'm going to teach you how to find the maximum value from multiple fields in the same record. We're going to use multiple nested IIF functions.
Tabitha from Knoxville, Tennessee (a Platinum Member) asks: I have a table where I'm tracking quiz and test scores for my students. Every quarter they take 3 quizzes and 3 tests. Their grade is calculated by taking the average of the three tests and the largest grade from the quizzes. So let's say they got quiz grades of 80, 75, and 70, and then got 100s on all of their tests. Their final grade would be (100+100+100+80) / 4 = 95. My question to you is how can I determine the largest of the three quiz grades?
Members
Members will learn how to create a function so that you can send any number of values to it using something called a parameter array.
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!
Pre-Requisites
Links
Recommended Courses
Additional Resources
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, finding the highest value in different fields, find max value of different fields in same record, parameter array, paramarray, lbound, ubound, for each
Subscribe to Max Value
Get notifications when this page is updated
Intro In this video, I will show you how to find the maximum value from multiple fields in a single record in your Microsoft Access database. Using a real-world example of tracking quiz and test scores, we will discuss the limitations of Excel-style functions in Access, walk through setting up a table for student grades, and demonstrate step-by-step how to calculate the sum and maximum values using calculated fields and nested IIf statements in a query. If you need to select the largest value from several fields in one record, this video will show you how to do it in Microsoft Access.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 take the maximum value from multiple fields in the same record in your Microsoft Access database.
Today's question comes from Tabitha in Knoxville, Tennessee, one of my platinum members.
Tabitha says, I have a table where I'm tracking quiz and test scores for my students. In the quarter, they take three quizzes and three tests. Their grade is calculated by taking the average of the three tests and the largest grade from the quizzes. So let's say they got quiz grades of 80, 75, and 70, and then got hundreds on all of their tests. Their final grade would be three 100s for the tests plus 80, which is the largest of the quizzes divided by 4, and that's going to equal 95.
My question to you is how can I determine the largest of the three quiz grades?
Well Tabitha, that's not that hard at all. Let me show you how to do it.
Now before we get started, I've got a couple prerequisites for you first. You should know how to make calculated fields in queries. If not, go watch this video. It's free. It's on my website. It's on my YouTube channel. Go watch it right now. Also go watch this video on the if function, the immediate if function, basically, if then statement that you can put in a query. Go watch this too if you haven't watched this yet.
Before we get into Access, I want to show you how most people do this in Excel. A lot of people, including Tabitha, are coming into Access from Excel, and in Excel, you do things a little bit differently. I want to get you to stop thinking in terms of Excel and start thinking like Access.
But first, I want to show you the way to do this in Excel so you can see the difference.
The easiest way to do this, and I'm a big fan of multiple steps. I don't like to make one function that's complicated and does everything in one step. I like to break things up.
So what we're going to do first is we're going to find the total of the three tests first. Let's put over here, we're going to say equals the sum of the three tests. There they are, right there. Press Enter. And yeah, it's saying right here, it ignores the adjacent cells because it wants you to fill in all the way for the quizzes too. We're just going to ignore that error. I'm going to auto fill down, double click there. Same thing. I hate this. This is irritating to me. This will be the sum of the tests, a sum of T.
Next, we can find the max of the quizzes using the max function. So max Q is going to be equals the max of the three quizzes.
Now this isn't something that you can do easily in Access. There it is. Again, auto fill it down. Turn that off. Okay.
Now that we have the sum of the three tests and the max of the quizzes, now we're going to find their average, which is going to be equals this plus that divided by four. That'll give you what you're looking for. We can do one of these jobs if you want to open here. We'll go like that. I'm going to go like that. Okay. Perfect.
In Excel, it's easy to do ranges this way. Give me the max value of that. Give me the sum of all of those. In Access, you really can't do that.
So let's create the same thing in Access.
Let's create a table. Now, like I said before, this isn't necessarily the best way to store this data. I wouldn't store test one, test two, test three, quiz one, quiz two, quiz three, as fields in the table. I wouldn't do these as fields. Not the best way to do it.
Now, I'm going to show you how to do it this way because Tabitha and I actually had this conversation in email. This is the way her database is set up. She doesn't want to change it. She's got years worth of data in it. So she wants to work with this. It's always three tests and three quizzes per quarter, always. So she has no desire to change it. And that's fine.
But if you want a variable number of these things, then I would recommend making this a separate table. If you really want to see how to do this, I can show you. We'll do it in a future video, though. So if you want to see me do that in a future video, post something in the comments down below and if enough people are interested, I will do it.
Continuing on. Let's just make a simple table to store the same basic data.
We've got an ID. We've got our student name. Then we've got test one. I'm going to make these numbers. You should make them doubles if you want to do fractions. I'm just going to keep them as long integers because it's easier. Test two as a number. Test three as a number. Then we've got quiz one, quiz two, and quiz three.
If you do your test and quiz grades and you want to do fractional components, again, make these doubles down here. You can still make the final average a double even if these are all stored as long integers.
Now, do we need a field down here to store the average? No. Why? Because those are going to be calculated fields. No need to store something that you can calculate. There are some exceptions, but generally that's the rule.
So I'll save this as my grade table. We'll call it. Primary key. Yes. In fact, while we're at it, let's write this thing grade ID. I try not to name things just ID. When I'm starting a table off, I'll just put ID there until I determine exactly what I want to call this table. Then I'll change the name of the ID.
Let's put some data in it now. Save the table. Yeah, okay.
Now, can we copy and paste this stuff over to our table here? Yeah, you can, but your fields all have to match up, and that ID kind of messes things up. So here's what you're going to do. Save that. We're going to make a query. Create query design. Then we're going to bring in the fields just to match up with that other Excel sheet. So we're going to bring in grade T and then bring in exactly the fields the way they are on the other table. This one to this one.
Now, these all line up. Save this as grade Q.
Run the query. Now these all line up. So we're going to come over here. You want to copy whole rows. I'm just going to copy that and paste it here and then watch this, select the rows. Copy. Come over here. Select the row and paste and look at that. They all line up nice. Once you get to the end of the row here, the copy paste knows that you hit the end of the row and everything copied in. These all have to line up exactly. You don't want this stuff in the row. That's just a little trick.
Now to do the sum of the three tests, that's the easy part. That we can come in here very easily. Let me make these a little smaller. That's easy. We can say sum T, sum of the tests, is equal to test one plus test two plus test three. That's the easy one.
Now, how do we figure out the max of the quizzes? That's a little tougher. For that we have to use the if function.
Now Access does have a max function, but it doesn't work the way you think it works. It doesn't work the same as in Excel. See, in Excel, a range can be this way, it can be that way, it can be a whole block. A range can be vertical, it can be horizontal, it can be a block like that. It's just a starting upper left and a bottom right.
But in Access, it doesn't work that way. In Access, you can only use those functions like max in the same field with multiple records. So for example, in an aggregate query, if you make an aggregate query, you can add up all of the test one grades. You can add up all of the quiz two grades. You can't use an aggregate query across multiple fields like that.
It would be nice if I could come over here and say, I want x to be the max of quiz one, quiz two, quiz three. It would be nice, but it's wrong number of arguments. It doesn't work that way.
There's also a DMax function, which you can again use to get the maximum value of a single field across multiple records. But it doesn't work across multiple fields in the same record. That's why I want you to think like Access, less like Excel.
So we actually have to compare these three values. To do that, we're going to use the if function.
Now, let's just pretend we only have two quizzes. Let's pretend we've got two of them. I'm going to zoom in. Here, we're going to call it max Q. Now we're going to say if quiz one is greater than quiz two. You could use greater than or equal to if you wanted to. Then the answer here would be quiz one. Otherwise, the answer would be quiz two.
Honestly, it doesn't matter if you use greater than or equal to because if they're equal, it will be the same thing. That's how you'd compare two of them. This should give you the larger of one or two. Forget three for a minute. There's 89. There's 87. There's 76. Let's make this one bigger. There you go. There's 99.
If you want to do three values, then this gets a little more complicated because now you have to figure quiz three into all of this.
So let's chop this off for a minute. We're going to say if quiz one is greater than quiz two and quiz one is greater than quiz three, then the greatest value is quiz one. Otherwise, now we have to check quiz two against the other values. So we need another if statement.
If, okay. This if statement is going to have those parentheses and then the outer if statement is going to have that parenthesis. I like to put my parentheses in when I'm making nested functions like that; I'll put the parentheses in there.
Now, we have to compare quiz two against the other two. So if quiz two is greater than quiz one and quiz two is greater than quiz three, then the answer is quiz two. Otherwise, quiz three is the biggest one.
So check to see if quiz one is bigger than quiz two and quiz one is bigger than quiz three. In other words, quiz one is bigger than the other two; then it's quiz one. Otherwise, check quiz two against one, two against three. If two is the biggest, then it's quiz two. Otherwise, it's quiz three.
If you had a fourth option, you'd replace this with another if statement. You can see how doing multiple values, four, five, six, seven values, can get quite tedious and make very long functions. I'm going to show you a solution for that in the extended cut for the members. But for the rest of us, this is how you do three of them.
Hit OK. Now we're on it. There you go. There's your max Q. 89, 99. 99. There we go. There we go. So there's one in each position. Quiz one, quiz three, quiz two.
That's how you have to do it. There's no other easier way I can think of for doing that like this. If you have a better solution, I want to hear about it. Tell me about it down in the comments below.
If you're just dealing with a single field, there's all kinds of things you can do. There's a max function. There's a DMax function. There's a max in the form footer you can put down there. But it doesn't work across multiple fields in the same record. This is the only way I can think of to do this.
With the exception of making your own function and creating something called a parameter array. That is what we're going to do for the members in the extended cut. It's going to require a little bit of VBA. We're going to write our own function called max value. We're going to use something called a parameter array. That's where you can pass an array of values. An unlimited number of values. However many you want. If you have five fields, pass all five of them. If you have ten, pass ten. If you have two, pass two. That's what a parameter array is good for. Then it will analyze all of them and return the biggest value. That will be in the extended cut for the members.
Oh, I almost forgot. We have to finish our calculation in here. We'll finish that like this. Then over here, we're going to say student average is going to be the sum T plus max Q divided by four. There you go. If you want to round it, you can throw that whole thing in the round function, comma one. I'll zoom in so you can see that a little better. There you go. There it is. Beautiful.
If you want to learn more, the extended cut for the members - silver members and up get access to all of my extended cut videos. It's 5.99 a month, folks. It's worth the cost in gold. I would think, well, that's gold membership - that's 9.99. But we're going to do a lot more in the extended cut. I do cover a lot of this stuff in my developer classes as well.
If you want to learn more, check it out.
This has been your TechHelp video for today. Tabitha, I hope this helps you out. The rest of you, I hope you learned something.
I'll see you next time.Quiz Q1. What was Tabitha's requirement for calculating her students' grades?
A. Use the smallest quiz score and the sum of the tests
B. Use the average of all quizzes and all tests
C. Use the average of the three tests and the largest grade from the quizzes
D. Use the median of the quiz and test scores
Q2. In Excel, how would you typically find the largest value among three quiz grades?
A. By using a custom VBA function
B. By manually comparing each value
C. By using the MAX function on the range of quiz scores
D. By creating a pivot table
Q3. Why can the MAX function not be used in Access to determine the largest value across multiple fields in the same record?
A. MAX in Access only works on strings
B. MAX in Access only works across multiple records in a single field, not multiple fields in a single record
C. The MAX function is not available in Access
D. MAX can only be used in Excel formulas
Q4. If the quiz grades are stored in separate fields per record, what is a recommended way to find the largest among them in Access?
A. Write a CROSSTAB query
B. Use a nested IIf (Immediate If) function to compare each value
C. Use DLookup on each field
D. Create a report and sort the fields
Q5. What issue arises if you want to scale up the IIf comparison formula for more than three quiz fields?
A. Access crashes if more than three IIf statements are used
B. The function becomes excessively long and complex
C. MAX automatically works for more than three values
D. Access returns incorrect results for decimals
Q6. What is a parameter array, as mentioned for the members' extended cut?
A. A field type in Access
B. A method for comparing two values only
C. A way to pass any number of values into a custom VBA function
D. A new Excel feature for range analysis
Q7. When creating calculated fields in an Access table as shown in the video, which of the following is TRUE?
A. You should always store the calculated average in the table
B. Calculated fields are unnecessary and should be avoided
C. You should not store data that can be calculated from existing data
D. It is mandatory to use macros for calculations
Q8. When dealing with test and quiz grades that may contain fractions, what data type is recommended for the fields?
A. Text
B. Autonumber
C. Double
D. Boolean
Q9. What is the main difference between using Excel and Access for this type of data analysis?
A. Excel cannot store test data
B. Excel allows ranges in functions; Access operates differently, working best on fields and records
C. Access is unable to do calculations
D. Access supports drag-and-drop for all formulas
Q10. If you want to improve the data structure for handling a variable number of quizzes and tests in Access, what does the instructor recommend?
A. Using more columns in the same table
B. Creating a separate related table for quizzes and tests
C. Using only reports
D. Storing all scores as a comma-separated string in one field
Answers: 1-C; 2-C; 3-B; 4-B; 5-B; 6-C; 7-C; 8-C; 9-B; 10-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 how to find the maximum value from multiple fields within the same record in Microsoft Access. This is a common scenario for people who are moving from Excel to Access, and it presents a bit of a challenge since Access handles these kinds of calculations differently.
The question for this lesson came from a student who tracks student quiz and test scores each quarter. Each student has three quizzes and three tests. The process for calculating a student's final score involves averaging their three test grades and then using the highest of the three quiz grades. For instance, if a student scored 80, 75, and 70 on their quizzes, and 100 on all three tests, their final grade would be the sum of the three 100s and the highest quiz grade (80), divided by four. That comes to a final score of 95.
To tackle the challenge of finding the highest quiz score among three separate fields, it's helpful to first think about how this would be done in Excel. In Excel, it's straightforward because you can use the MAX function across a range. You could simply sum up the test scores, find the max of the quizzes, and calculate the final average with their built-in functions.
However, in Access, things are handled differently. Instead of working with cell ranges, you are working with fields in a table. If your table is structured with fields for Test1, Test2, Test3, Quiz1, Quiz2, and Quiz3 for each student, and if you are committed to keeping it this way (as in Tabitha's case), there are techniques you can apply even though this is not the preferred database design.
Ideally, it's better to normalize your data by putting test and quiz results in a related table, letting each row represent a single score. That approach makes calculations like finding the maximum much more flexible. However, if your structure is fixed and you can't or don't want to redesign your tables due to years of accumulated data, you have to work with what you have.
After ensuring your data is structured in a table with each quiz and test score as a separate field, you do not need to store the final average as a field since it can be calculated as needed. Exceptions do exist, but as a general rule, look to store only base data, not calculated results.
If you are importing data from Excel, it's important that the fields match exactly. Once everything is lined up, you can use a query to perform calculations.
Adding the three test scores to get their sum is simple: just create a calculated field in a query that adds those three fields together. But Access does not provide a built-in way to find the maximum value across different fields in the same record like Excel's MAX function does. The Max and DMax functions in Access work across multiple records within a single field, not across multiple fields within one record.
To determine which quiz grade is highest, you need to use nested IIF statements (the Immediate If function in Access). For two values, the logic is simple: if Quiz1 is greater than Quiz2, use Quiz1; otherwise, use Quiz2. When there are three values, the logic becomes slightly more involved. You first check if Quiz1 is greater than both Quiz2 and Quiz3. If so, Quiz1 is the largest. If not, you then check if Quiz2 is greater than both Quiz1 and Quiz3. If this is true, then Quiz2 is the largest. If neither of these are true, then Quiz3 is the maximum.
If you have more than three fields to check, this logic quickly gets complex and cumbersome, requiring increasingly nested IIF statements. There is a better way to handle this for a large or variable number of fields, which is covered for members in the extended cut of this lesson. In that section, I demonstrate how to use a custom VBA function that accepts a parameter array, so you can pass any number of values and return the maximum. That approach is more flexible and manageable, especially as the number of fields grows.
Once you have found the highest quiz grade, you can complete the calculation for the student's final average by adding the sum of the three tests and the maximum quiz score, dividing by four, and optionally rounding the result.
For those interested in working with more advanced techniques, including handling a variable number of scores and writing your own VBA functions, be sure to check out the extended cut on my website.
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 Taking the maximum value from multiple fields in a record
Comparing Excel and Access methods for finding max values
Creating a table for student test and quiz scores
Designing fields for test and quiz grades in Access
Entering sample data into the Access table
Creating a query to match Excel data structure
Calculating the sum of multiple fields in a query
Understanding limitations of the Max function in Access
Using nested IIf functions to find the max of three fields
Constructing expressions with nested IIf statements
Calculating the student average using calculated fields
Rounding calculated average values in Access queries |
