|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
VLOOKUP in Access By Richard Rost 
5 years ago
Access Equivalent of Excel VLOOKUP Function In this video, I'll show you how to perform the equivalent of the VLOOKUP function from Excel in Microsoft Access using DMax and DLookup. We will look up letter grades for students based on their test scores. Alyssa from Baltimore, Maryland (a Gold Member) asks: I know how to use the VLOOKUP function in Excel to figure out letter grades from test scores. How can I do the same thing in Access? MembersMembers will learn how to create a GetLetterGrade global function so this lookup can be performed anywhere in the database without extra steps.
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!
Links
Keywordsmicrosoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #lesson, #training, #database, dlookup, vlookup, dmax, Access Equivalent of Excel VLookup, Excel-Like VLookup for Microsoft Access, Can I do VLookup in access, How to do a VLookup in access
IntroIn this video, I will show you how to perform the equivalent of Excel's VLOOKUP function in Microsoft Access using a combination of DMAX and DLOOKUP to achieve an approximate match. We'll work through a practical example by looking up student letter grades based on test scores, and I'll explain how this approach can be used for other scenarios like employee commissions or customer discounts. If you've wondered how to replicate Excel's VLOOKUP in Access, this tutorial will walk you through building the required tables, setting up your query, and applying these domain functions effectively.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In this video, I am going to show you how to perform the equivalent of the VLOOKUP function from Excel in Microsoft Access using a combination of DMAX and DLOOKUP to get that approximate match. We will look up letter grades for students based on their test scores. The same technique works great whether you are calculating employee commissions, customer discounts, or whatever you would use VLOOKUP for. Today's question comes from Alyssa from Baltimore, Maryland, one of my Gold members. Alyssa says, I know how to use the VLOOKUP function in Excel to figure out letter grades from test scores. How can I do the same thing in Access? Access has a function called DLOOKUP, but DLOOKUP does not have the approximate match that VLOOKUP does where it will find values inside of a range. So in order to do this in Access, we have to use a combination of two functions, DMAX or DMAIN and DLOOKUP together. Let me show you how. If you are not familiar with VLOOKUP, it is a function in Excel. It is one of my more favorite functions. It was recently replaced with XLOOKUP, which is more powerful, but VLOOKUP goes back many, many years. I love this function. I cover VLOOKUP in detail in my Excel Expert Level 4 class. Essentially, you have a spreadsheet here that has a list of students, their final grade, and you want to look up a letter grade. Where do you look up the letter grade from? You make a little chart here or on a different sheet, wherever you want to put it. That has the grade and the letter that it is associated with. Now, direct match or exact match is like 80 is a B. That is fine. But VLOOKUP can look inside of a range, which is what is called an approximate match. So 72 is a C, for example. Excel can do this. Access cannot do this with one function. You have to use two. You have to use DMAX and then you have to use DLOOKUP. We want to basically DMAX the grade over here that is smaller than this value. The largest value over here that is smaller than 72 is 70. Then we can DLOOKUP the letter grade from 70. Let's see how we do this in Access. First, we need a list of our students and their grades. So I will create a table, create table design. Student ID, autonumber. I always put an autonumber in my tables, pretty much almost always. We have a student name and their grade, which will be a number of type long integer. If you want to go double, if you want to have 89.3, that is fine too. I am just going to go with whole integers here. Let us save this as my student table, StudentT. Say yes for the autonumber and let us put some values in here. We have Joe with a 65, Sally with a 90, Sue with a 70, Will with a 67. Let us go a little bit off so it is not exact. We will go Richard with a 98, of course. Deanna with 82. There is the list of students and their grades. Now we need to make our lookup table. We will do that with another table. Create table design. We will call this our grade table. Grade ID, autonumber again. Always put an autonumber in there. Grade letter or letter grade, whatever you want to call it. This will be short text. Then the grade minimum value will be a number, again a long integer. Save this, GradeT. Save this. Let us put some data in here now. Grade letter A minimum value 90. B is an 80. C will be a 70. D 65 and an F for zero. Make sure this range encompasses your entire range of values. Save it, close it. Now, let's make a query where we can look up their letter grades. Create query design, bring in the student table. Close this. We are not bringing in the grade table. We are not making a relationship between these two tables because the values will not be exact. We are going to use a function to look stuff up. Bring in the student name and the grade. Save this while I am at it, StudentGradeQ. Here is what we have so far. This is actually easier to do if you can see the grade list too. Bring over the grade table just to display it. Here it is. Set it down in the corner just so you can see what it looks like. Put it right down here so it will not be in the way. What I want to do is look up this 65 in this list. If we just do a straight DLOOKUP, we will only get the values that have matches over here. Let me show you. If I come in here and say LG (letter grade) is DLOOKUP, the grade letter from the GradeT, where the grade minimum value equals grade. That is a valid DLOOKUP statement. If you have never done DLOOKUP before, go watch my DLOOKUP video and while you are at it, go watch my DMAX video too. Those are two free videos on my website. I will put links below in the links section. You have to know DLOOKUP and DMAX before you go any further or you will be confused. Go watch those videos now. Pause this, come back to it. Now you are a DLOOKUP expert. You watched the other videos, you know DLOOKUP, you know DMAX. This is going to look up the grade letter from the grade table, where the grade minimum value equals the grade. If I run this now, I get the values where there are exact matches, but DLOOKUP does not do approximate matches. It has to have an exact match. So we need to use DMAX to first figure out which one of these grades to use. I want the largest grade that is smaller than the one I am on. What is the largest grade that is smaller than 67? That should be 65. We will get that first. Then we will DLOOKUP 65's letter grade. See what I am doing? Design view. We will come back to this in a second. Over here, let us just look up a value, we will call it G. G is going to be DMAX, the grade minimum value from the GradeT, where the grade minimum value is less than or equal to my score, or my grade, the student's grade. Run that now. Here is our list of values. 65 is a match, 90 is a match, 70 is a match, 67 brings back 65. It is the largest value from this table that is less than or equal to this one. 98 brings back a 90, 82 brings back an 80. Now, we will use this value to look up the letter grade. Let me zoom in so you can get a better look at this one here real quick. Shift F2 to zoom in, there you go. Look up the largest grade minimum value from the grade table where the grade minimum value is less than or equal to the student's grade. Now, let us go back to the class. Go back to this guy, move him over to the right. Instead of using DLOOKUP on the student's grade, we are going to DLOOKUP based on G, that value we just pulled up. Get rid of that stuff and just make it G, because G is right here. Save it, go back in here, and there you go. There is your DLOOKUP equivalent. 98 is an A, 67 is a D, 82 is a B. Easy enough. It just takes two steps, that is all. There is no VLOOKUP function because there is no vertical, really; it is not a spreadsheet, you are looking up in a domain, which is what the D stands for. A domain is another table or query. The hard part is just looking up the other value with DMAX because there is no approximate match, so you have to see what value you have to do your lookup on based on the value you have. Hope this answers the question for you, Alyssa. To learn more, in the extended cut, I show you how to take what we just did and make a global module out of it, so you can make a function that does exactly what we just did but a whole lot easier. You are going to probably want to use this everywhere in your database. You are going to want to display the letter grade in your forms, on your reports, maybe multiple queries. You do not want to have to do all that calculation everywhere you want to display this value. You just want to say get letter grade. This will make a public function called get letter grade that we can then use in our queries. All you have to say is get letter grade, send it the grade, and it returns that letter grade. That is all you have to do. You can display it in your forms, in your reports, other queries, wherever. That is covered in the extended cut. Silver members and up get access to all of my extended cut videos, over 100 of them now. It is a great value. I also cover lots of stuff just like this in my Access Expert Level 29 class. All the domain functions, DLOOKUP, DSum, DAvg, DMax, DMin, DFirst, DLast, there are a lot of them. We cover running sums, creating your own custom counter, and the Excel VLOOKUP replacement stuff. That is all in Access Expert Level 29. You can find it on my website. I will put a link down below. How do you become a member? Click the Join button below the video. After you click the Join button, you will see a list of all the different types of membership levels that are available. Silver members and up will get access to all of the extended cut TechHelp videos, live video and chat sessions, and more. Gold members get access to a download folder containing all the sample databases that I build in my TechHelp videos, plus my code vault where I keep tons of different functions that I use. Platinum members get all the previous perks plus access to my full beginner courses and some of my expert courses. These are the full-length courses found on my website and not just for Access. I also teach Word, Excel, Visual Basic, ASP, and lots more. These free TechHelp videos are going to keep coming. As long as you keep watching them, I will keep making more. If you liked this video, please give me a thumbs up and feel free to post any comments that you have. I do read them all. Make sure you subscribe to my channel, which is completely free, and click the bell icon and select All to receive notifications when new videos are posted. Click on the Show More link below the video to find additional resources and links. You will see a list of other videos, additional information related to the current topic, free lessons, and lots more. YouTube no longer sends out email notifications when new videos are posted, so if you would like to get an email every time I post a new video, click on the link to join my mailing list. If you have not yet tried my free Access Level 1 course, check it out now. It covers all the basics of building databases with Access. It is over three hours long. You can find it on my website or on my YouTube channel. If you like Level 1, Level 2 is just one dollar, and it is also free for all members of my YouTube channel at any level. Want to have your question answered in a video just like this one? Visit my TechHelp page and you can send me your question there. Click here to watch my free Access Beginner Level 1 course, more of my TechHelp videos, or to subscribe to my channel. Thanks for watching this video from AccessLearningZone.com. QuizQ1. What is the main goal demonstrated in this video?A. Performing the Excel VLOOKUP function for approximate matches in Microsoft Access B. Creating a mail merge in Access C. Importing Excel data into Access D. Designing forms for data entry in Access Q2. Why does the standard DLOOKUP function in Access not fully replicate Excel's VLOOKUP approximate match behavior? A. It only searches for text values B. DLOOKUP requires an exact match and does not handle ranges C. DLOOKUP can only be used on reports D. DLOOKUP updates records directly Q3. What combination of functions is suggested in Access for simulating VLOOKUP with approximate match? A. DFirst and DLast B. DSum and DAvg C. DMax and DLookup D. DMin and DCount Q4. In the video's example, what real-world scenario is being used to demonstrate the approximate lookup? A. Calculating quarterly sales from monthly data B. Looking up letter grades based on test scores C. Assigning employee IDs based on departments D. Tracking inventory quantities Q5. What does the DMax function achieve in the lookup process? A. Finds the smallest grade higher than the student's grade B. Calculates the sum of all grades C. Finds the largest grade minimum value less than or equal to the student's grade D. Looks for a matching letter grade for the score directly Q6. Why isn't a relationship created between the student and grade tables for this example? A. A relationship would cause duplication B. The lookup requires a dynamic match, not a fixed link C. Relationships are not supported in Access queries D. It would be too complicated to manage Q7. After finding the correct minimum grade using DMax, what is the next step? A. Assign a grade randomly B. DLookup the corresponding letter grade from the grade table for that minimum value C. Calculate the class average D. Sum all student scores Q8. What important tip does the video offer when building tables for the lookup? A. Always include an AutoNumber field as a primary key B. Use only short text data types C. Avoid saving the table to prevent errors D. Only use one table for all lookups Q9. What is suggested for repeated use of this lookup across multiple forms and reports? A. Make a global module with a custom function B. Redo the query manually each time C. Use only macros D. Print each result on paper and refer to it Q10. What does the 'D' in DLookup and DMax stand for? A. Default B. Domain C. Design D. Data Answers: 1-A; 2-B; 3-C; 4-B; 5-C; 6-B; 7-B; 8-A; 9-A; 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. SummaryToday's video from Access Learning Zone focuses on how you can replicate Excel's VLOOKUP functionality in Microsoft Access. Many people use VLOOKUP in Excel to look up values within a range, like determining a student's letter grade based on their test score. Unlike Excel, Access does not have a direct VLOOKUP function that supports approximate matches. However, by using a combination of DMax and DLookup functions, you can achieve the same result.The example in this lesson stems from a question about how to assign letter grades to students based on their numeric scores, just as you might with VLOOKUP in Excel. DLookup on its own only works for exact matches, but we need to account for scores that fall within ranges, just like Excel's approximate match. To accomplish this in Access, you first set up two tables. The first table holds your student data with fields for an ID, student name, and their numeric grade. The second table acts as your lookup list, defining the minimum score required for each letter grade. For instance, an A might have a minimum of 90, a B starts at 80, and so on down to an F for anything below a certain value. Once these tables are set up and populated, you create a query using the student table. It is not necessary to join the two tables directly since student grades will not always match the exact values listed in your lookup table. Instead, you use DMax to find, for each student, the highest grade minimum in the lookup table that is less than or equal to their score. This step determines the cutoff within the grading scale that applies to each student. Once you have that value, you then use DLookup to find the corresponding letter grade for that cutoff. This combination of DMax and DLookup mirrors the approximate functionality found in Excel's VLOOKUP. For example, if a student earns a 67 and the grade cutoffs are 90 for A, 80 for B, 70 for C, and 65 for D, DMax finds the nearest lower cutoff (65) and DLookup retrieves the letter D. Doing this in two steps gives you the flexibility to handle cases that are not direct matches, just like VLOOKUP in approximate mode. If you wish to streamline this even further for repeated use in forms, reports, or other queries, I cover in detail in the Extended Cut how to encapsulate this logic into a reusable public function. This makes it much simpler to look up a letter grade anywhere in your database with a single function call instead of repeating the DMax and DLookup steps each time. This approach is not limited to letter grades. You can use this technique for calculating commissions, discounts, and any scenario where you need to look up a result based on a range of values in a lookup table. For those wanting to learn more about these domain aggregate functions, I cover DLookup, DSum, DAvg, DMax, DMin, and others in my Access Expert Level 29 class, including building your own custom counters and the replacement technique for VLOOKUP in Access. You can find more details and a link to that class on my website. Remember, joining my channel as a member gives you access to the extended cut of this and over a hundred other videos, downloadable sample databases, and my code vault of useful functions. Higher-level members gain even more benefits, including full access to my beginner and some expert-level courses for Access, Word, Excel, Visual Basic, ASP, and more. I also encourage you to try my free Access Level 1 course, which covers the basics of building databases with Access. If you need your own question answered in a future TechHelp video, you can submit it through the TechHelp page 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 ListCreating a student grade lookup table in AccessCreating a grade scale lookup table Entering sample student and grade data Understanding DLOOKUP limitations for approximate matches Using DMAX to find the nearest lower grade Combining DMAX and DLOOKUP to simulate VLOOKUP Building a query to assign letter grades based on numeric scores Constructing calculated fields in Access queries for lookups Demonstrating the workflow step by step in Access |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access dlookup, vlookup, dmax, Access Equivalent of Excel VLookup, Excel-Like VLookup for Microsoft Access, Can I do VLookup in access, How to do a VLookup in access PermaLink VLOOKUP in Access in Microsoft Access |
