|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Alphanumeric Grouping By Richard Rost 
6 years ago
Alphanumeric Grouping with the DMAX Function. Have you ever wanted to group records alphanumerically? For example, you've got three groups set up, and you want to put people into them based on their last name. A-M goes in Group 1. N-R goes in Group 2. S-Z goes in Group 3. In this lesson I will teach you how to do just that using the DMAX function. Nancy from Swanton, Maryland (a Gold Member) asks: I have created a database containing words from a printed dictionary. I have entered in the first word that appears on each page, and the page number. I would like to be able to search for any word, and have the database tell me what page it should appear on. For example, if page 5 starts with Elephant, and page 6 starts with Gorilla, if I search for Fox I want the database to tell me it should be on page 5. MembersI'll show you how to create a table to store all of the words on each page. If the word exists, it will open a form and jump to the right page, and to the word on that page. If it doesn't exist, you'll be prompted to add it. Plus lots more tips!
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! LinksDLookup: https://599cd.com/dlookup
IntroIn this video, we will talk about how to set up Alphanumeric grouping in Microsoft Access, such as dividing last names or words into groups like A-M, N-S, and T-Z. I will show you how to use the DMax function to look up which group or page a record should fall into based on your entries, with practical examples including a dictionary search scenario and student group assignments by last name. You will learn the basics of grouping text values in your Access database and how to write simple VBA code to make this work.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. My name is Richard Rost, your instructor.In today's video, I'm going to teach you about Alphanumeric grouping - that's putting records into groups like A through M goes into group 1, N through S goes into group 2, T through Z goes into group 3, and so on. This way, you can search for a name; for example, schools do this all the time. You type in the name Rick, and you are in group 2, for example, or you are with Mr. Smith. That's what this lesson is about. Today's question comes from Nancy from Swanton, Maryland, a gold member. Nancy says, I have created a database containing words from a printed dictionary. I have entered in the first word that appears on each page and the page number. I would like to be able to search for any word and have the database tell me what page it should appear on. For example, if page 5 starts with elephant and page 6 starts with gorilla, if I search for fox, I want the database to tell me it should be on page 5. Sometimes for the TechHelp videos, I simplify the question a little bit. Nancy actually sent me an email with some screenshots of the database she's been building. It's actually pretty impressive. She's got some different dictionaries, she's scanned the pages in, and she's saved image files for those. Some of them are translation dictionaries between Hebrew and English, and it's really cool. She wants to be able to type in a word and then have the database tell her what page of the dictionary it should appear on. While I was preparing this for her, I thought this is actually something I've seen groupings like this in a lot of different places. Businesses use it; schools use it. They say, if your last name begins with A through M, you're in group 1, or you have teacher Mr. Smith. Group N through S, you're in group 2. So if you get a student and you type in Richard Rost, last name R, the database should be able to tell me I'm in group 2. How can I set this up in Access? Now, in order to do this properly, we have to use a function called DMax. I suggest you pause the video right now and go watch the DMax tutorial, although I will show you how to do it in just a second. DMax is a very close cousin to DLookup, where you can look up a value in a table or query. I've got tutorials on that too. Go watch the DLookup stuff first. Once you're familiar with DMax and DLookup, let's continue with this lesson. Here's my blank customer database. You can get a copy of this from my website. I'll put a link down below in the description. Just download the blank customer database. Here's the blank database. We don't need a lot of this stuff. We don't need the customer contact tables or any of this stuff here, so let me just clean this out a little bit. Continuous form and a single form. Actually, for this we're not going to need a single form either. Get rid of that. We'll keep the main menu and the continuous blank form. Let's create a table. We'll design, turn off this property sheet over here. This table will hold all of the pages in my dictionary. We'll start off with the page ID - that'll be my autonumber. Then the first word that appears on each page; that'll be text. Then the actual page number - those should be one through whatever. You don't want to rely on your autonumber; you want to put these numbers in yourself. This will be a number. If you end up deleting autonumbers in the middle - page 7, you delete it, you start it over - you have to mess with stuff to get those numbers back. Can you do it? Yes, you can. There are tricks. So it's best to not rely on that autonumber for anything except for relationships in the database. We're going to put in the page number ourselves. Can you automatically increment that to the next one? Yes, sure. You can. I have videos on that one too. I'll put a link down below for auto-incrementing counter variables. Let's save this. This will be my page T, my page table. Let's put some data in it. The first word on each page. Let's just do names. Let's say we have Aaron. That's on page 1. Bruce starts page 2. Charles is on page 3. Edward starts page 4. And you see how this works. George, page 5, and so on. The goal here would be to type in a name like Christine and have the database say it belongs on page 3. CHR would come after Charles but before Edward. So it's going to appear on page 3 because Edward starts page 4. I'm going to slide you down over here for just a minute so we can see over there. Actually, let's put you over here. Now, I'm going to use this little hello world box here because this unfortunately is going to require some VB code to type in something and then have it return a value. You can do this in a query, but it's kind of weird and it's only going to return one value anyway. So you might as well do a little bit of programming. Don't be scared. If you've never done any VB programming before, go watch my intro to Access VBA. I have a free video on it. I'll put it in the link down below. If you've never done any programming, go watch that now. What I put in my template here is a simple button and a text box. I can click on the button and it puts hello world in that text box. I use this for getting values. Instead of having to message box stuff or use the immediate window, I can just put stuff I want in this button. We're going to put this button here to use. Let's go to design view. I'm going to slide this up by the way here, put this up top, and give me a bigger box here. Like this. I'm going to put a text box up here that will be our target word. I'm going to come up to design, grab a text box, drop it right there, and we'll slide it over here. Right here I'll put the word target in there so we know what this is. Target. We'll make that white. There we go. We'll name this guy the target. That's my target word. I'm going to stick a default value in there right now. Just for class, I'm going to put the word Christine in there so I don't have to keep typing it in. Close that. Save changes. Open it back up again. We'll call it target word. It doesn't do anything yet though. First, let's do a quick DMax. We're working with the DMax. Let's use DMax just to see what the largest word in the table is. Design view. Right-click, build event. Here's my code editor, and I'm going to shrink this down a little bit so you can see it better. There we go. I don't need the watch window. For this, I don't really need the project explorer either. Just to make things simple, I'm going to get rid of all these buttons that we don't need to clean things up a little bit here. Let's get rid of these guys too. All we really need is this hello world button right here. So, let's DMax the largest first word in the table. Dim S as a string. It's a string variable. S is going to be equal to DMax the first word from the page table. Find me the largest first word field in the page table. Then I'm going to status S, which is just going to display it in a little box there. In fact, here I'm going to make some more room for this stuff. Let me slide this over here. I'll get rid of my advertising. Save it. Yes. Close that. Open it back up again. Ready? Hello world. Boom. George. That's the largest value in that page table. See, that works. If you want to see the largest page number, just change the field to page number. Boom. Five is the largest page number. Let's go back to that first word. How do I see the smallest one? That's a DMin, a related function. The minimum. Boom. Aaron. Now, what I'm actually looking for is I want to bring back the page number. Let's go back to the page number. I want the largest page number, DMax, the largest page number where the first word is smaller than the target. I want the largest page ID where the first word is smaller than the target. So we're going to have to add a criteria over here where the first word is less than or equal to the target. The target has to be inside quotes inside of here. So quote, quote, and target. One, two, three, four quotes. This is because this has to say first word is less than or equal to target. Target itself has to be inside quotes, and that's just how we formulate that. If this looks confusing, I have a video specifically on these double double quotes and string concatenation. I'll put that in the links down below too. Now, when I run this, I get a 3. Why do I get a 3? It's finding the largest page number where the first word is less than the target. First word being less than target would be Charles is the largest one. If I change this to Zoe, I get a 5, which is perfect. What if I put in here aaaa, just to try it? Boom. I get invalid use of null. Why? Because it returned a value that doesn't exist. There's no record that fits this criteria. So you might get a null value if someone puts in something below that. That's what NZ is for. NZ, then comma. What do you want to return if there is no value? We're bringing back a page number, and page number is a number. So bring back a zero. Now, since we're bringing back a number, we should change S here. Instead of S, let's use P. Dim P for the page as a long. Initially, I was going to bring back a name like George and Aaron. I started off with a name. DMax the name. If we're bringing back an ID now, we want to change that to a long. It still worked because Access is pretty good at taking numbers and converting them into strings to display them, but be very careful when you're doing actual comparisons of those values. Now I could say if P equals zero, then status "no page valid" or whatever. Exit sub. End If. Now I can status. Now I can do something like target "should be on page" P, like that. Ready? And go. No page valid. See? If I put in here Bryce, Bryce should be on page two. How about Frank? Frank should be on page four. Perfect. Let's try this over again. There you go. That is how you use DMax, which is very close to DLookup, to find the largest word that is less than the target, the largest page number less than the target word. You can do the same thing if these were just letters in here. We talked about before, A through M is one thing. We could go A. Let's say H starts the next group. N, R, and then V. Same concept for our grouping before. If I do Zoey, Zoey will be on page five or in group five, whatever you want to call it. If I type in Bill, Bill is on page one or in group one. Nancy, page three. It's not case sensitive. Rick, because Rick is alphabetically larger than just an R. That's how it works. Want to learn more about this Alphanumeric grouping? In the members-only extended cut, I cover a lot more stuff. First, we'll add a word table as well, so we'll track what words are actually on each page. When you search for a word, it will first look to see if the word is in there already. If it is, it will open up that form you see, go to the right page, and then go to that particular word and show you exactly where it is. If the word doesn't exist, it will prompt you and say, this word should be on page 12, but it's not in there. Would you like to add it? If you say yes, it will add it, and then again, open up the page right to that word. I will also show you how to automatically set the next page number to the next number in order and a couple of other tricks. How do you get this? It's the extended cut for my members only. Silver members and up get access to all of my extended cut videos. How do you become a member? Click on the join button below the video. Silver members and up will get access to all of my extended cut TechHelp videos, live video and chat sessions, and other perks. If you click the join button, you'll see a list of all the different membership levels that are available, each with its own special perks. But don't worry, these TechHelp videos are going to keep coming. As long as you keep watching them, I'll keep making more and they'll always be free. QuizQ1. What is the main purpose of alphanumeric grouping as described in the video?A. To sort records numerically only B. To delete duplicate records from a table C. To group records by alphabetical ranges for searching or assigning groups D. To encrypt data in a database Q2. In Nancy's project, what does the database need to do when a user searches for a word? A. Delete the word if it doesn't exist B. Tell the user what page the word should appear on C. Re-sort the entire dictionary D. Export the word list to Excel Q3. Why should you avoid relying on an autonumber field for page numbers in the page table? A. Autonumber values are always unique for page numbers B. Deleting records can cause gaps, making numbering unreliable C. Autonumber fields sort text values D. Autonumber fields are required for every table Q4. What is the primary function demonstrated in the video for finding which group or page a word belongs to? A. DCount B. DSum C. DMax D. DAvg Q5. What does the DMax function do in this lesson? A. Finds the smallest value in a set of records B. Finds the average value in a set of records C. Finds the largest value that meets a specified condition D. Finds all records matching a condition Q6. If no page meets the search criteria in the DMax function, what should you use to handle the result? A. Use the Max function B. Use NZ to provide a default value C. Ignore the result and show an error D. Use a For loop to search manually Q7. What variable type does the video recommend using if the DMax function returns a number (like a page number)? A. String B. Date C. Long (or Integer) D. Boolean Q8. What is recommended before attempting the VBA code in this lesson? A. Watch the DMin tutorial B. Learn SQL Server Advanced Features C. Watch the introductory Access VBA video D. Study Excel Macros in detail Q9. Which of these grouping examples is directly mentioned in the lesson? A. Grouping by country codes B. Grouping by last name initials (A-M, N-S, T-Z, etc.) C. Grouping by customer IDs D. Grouping by purchase totals Q10. What additional feature is introduced in the members-only extended cut of the lesson? A. Exporting data to PDF B. Adding a word table and searching for exact words with options to add missing words C. Encrypting the database for security D. Linking Access to SQL Server Answers: 1-C; 2-B; 3-B; 4-C; 5-C; 6-B; 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. SummaryToday's video from Access Learning Zone focuses on alphanumeric grouping in Microsoft Access. I'm going to explain how to organize records into specific groups based on where their names fall in the alphabet. For example, you might want to group names from A through M together in group 1, N through S in group 2, and T through Z in group 3. This sort of grouping is commonly used in schools and businesses, making it easy to categorize and search for individuals. If you want to look up a name, such as Rick, you can instantly tell which group or page it belongs to, like the group that contains Mr. Smith.The question for today's video came from someone who was working on a database project involving a printed dictionary. She entered the first word that appears on each page alongside the page number, with the goal of searching for any given word and finding out which page it should be on. As an example, if page 5 starts with elephant and page 6 starts with gorilla, entering 'fox' ought to point you to page 5. While preparing this solution, it occurred to me just how useful this technique is in various situations. Places like schools or businesses often use groupings like this to decide which staff member or group a person should belong to, based on their last name or another identifier. For example, if a student's last name is Rost, starting with R, you would want the database to return the proper group or page number according to your group boundaries. To set this up in Access, you will need to use a function called DMax. If you are not already familiar with DMax, I recommend reviewing my DMax tutorial first, as well as my DLookup tutorials, since the two are closely related. DMax helps to retrieve the largest value that meets specific criteria from a table or query. In my example, I start with a blank customer database, which you can download from my website. Since we do not need unnecessary tables or forms for this demonstration, I clean up the database, keeping just the core objects needed for the example. The next step is to create a table that will hold all of the pages in the dictionary, including an autonumber ID, the first word appearing on each page, and the actual page number. It's important to enter the page numbers manually rather than relying on autonumbering, since manual entry is more reliable if records are deleted and added over time. If you want to automatically increment these numbers, I have tutorials on that topic as well. After saving the table and entering some sample data (such as Aaron on page 1, Bruce on page 2, Charles on page 3, and so on), the goal is to be able to enter any name (for example, Christine) and have the database return the proper page number. For Christine, which comes after Charles but before Edward, the expected page is 3, since Edward starts page 4. To achieve this, I use a text box on a form to input the target word and a button to trigger the search logic. The core of the solution lies in using VBA code linked to this button. The logic retrieves the highest page number for which the 'first word' is less than or equal to the target word. This tells you the appropriate group or page for that word. When setting this up, be sure your code checks for cases where no matching page exists, which can result in a Null value. You can handle this situation by using the NZ function to substitute a default value, such as zero. I also point out that you should declare your variable types appropriately; if working with page numbers, use a data type like Long, rather than String, to ensure correct handling and comparisons. Once set up, you can test by entering various names. The code can return the appropriate group or page each name belongs to, whether you're entering names like Bryce, Frank, or Zoey. This method can also work for general alphanumeric ranges, not just words, so if your groups are simply defined by letters (for example, A, H, N, R, V), the same logic applies. This approach gives you a flexible way to assign any searched word to its appropriate group or page, based on how you have set your group boundaries in the table. If you want to explore more advanced features related to this topic, in the members-only extended cut of the lesson, I cover topics like adding a word table for more efficient page tracking, searching for existing words, opening forms directly to the right page and word, prompting for new entries when a word isn't found, and even automatically incrementing page numbers. 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 page table for first words and page numbersManually entering page numbers vs using autonumber Populating sample data for dictionary grouping Setting up a form with a text box for target word input Using DMax to find maximum values in a table Using DMin to find minimum values in a table Applying DMax with criteria for alphanumeric searching Building criteria strings for DMax using concatenation Handling null results from DMax with NZ function Switching variable types for returned values (string vs long) Displaying lookup results on a form Testing grouping logic with different sample inputs |
||||
|
| |||
| Keywords: TechHelp Access alpha grouping, alphanumeric, letter groups, group by names, group by letter, dlookup, dmax, increment, counter, group by words PermaLink Alphanumeric Grouping in Microsoft Access |
