Alphanumeric Grouping in Microsoft Access. DMAX Function. Group By Words. Group by Names.

welcome to another tech help video brought to you by accesslearningzone.com my name is richard ross 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 two t through z goes into group three 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 okay you're in group two for example or you're with mr smith all right that's what this lesson's about today's question comes from nancy from swanton maryland a gold member nancy says i've 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 five starts with elephant and page six starts with gorilla if i search for fox i want the database to tell me it should be on page five sometimes for the tech help 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 and some of them are translation dictionaries between like hebrew and english and it's really cool she wants to be able to type in a word and then had 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 okay if your last name begins with a through m you're in group one or you have teacher mr smith right group n through s you're in group two okay so if you get a student and you type in you know richard ross 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 now once you're familiar with dmax and dlookup let's continue with this lesson okay here's my blank customer database you can get a copy of this from my website again i'll put a link down below in the description just download the blank customer database all right here's the blank database and we don't need a lot of this stuff we don't need the customer and contact tables or any of this stuff here so let me just clean this out a little bit okay continuous form in a single form actually for this we're not going to need the single form either get rid of that we'll keep the main menu and the continuous blank form okay let's create a table table design turn off this property sheet over here this table will hold all of the pages in my dictionary so we'll start off with the page id that'll be my auto number then the first word that appears on each page that'll be text then the actual page number now those should be one through whatever you don't want to rely on your auto number you want to put these numbers in yourself okay so this will be a number because if you end up deleting auto numbers in the middle you know page seven you delete it you start it over you gotta mess with stuff to get those numbers back can you do it yeah you can but there's tricks so it's best just to not rely on that auto number for anything except for relationships in the database so we're going to put in the page number ourselves can you automatically increment that to the next one yeah sure you can i got videos on that one too i'll put a link down below for auto incrementing counter variables okay let's save this this will be my page t my page table let's put some data in it all right the first word on each page all right let's just do names let's say we got aaron that's on page one bruce starts page two charles is on page three edward starts page four and you see how this works right george page five and so on okay so the goal here would be to type in a name like christine and have the database say it belongs on page three chr would come after charles but before edward so it's going to appear on page three because edward starts page four all right so 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 okay 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 i mean you can do this in a query but it's kind of weird and it's only going to return one value anyways 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 again i got a free video on it i'll put it in the links down below if you've never done any programming go watch that now so 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 and i use this for getting values so i have the message box stuff or use the immediate window i can just put stuff i want in this button so we're gonna we're gonna put this button here to use so let's go design view all right i'm going to uh slam my slide this up by the way here a little bit put this up top give me a bigger box here all right like this and i'm going to put a text box up here that'll be our target word okay so i'm going to come up to design grab a text box drop it right there and we'll slide it over here and right here i'll put the word target in there so we know what this is right target we'll make that white there we go and we'll name this guy the target okay that's my target word i'm going to stick a default value in there right now just just for class i'm going to put the word christine in there so i don't have to keep playing around with typing it in okay close that save changes open it back up again all right there's my default target word doesn't do anything yet though okay you with me so far all right first let's do it a quick d max all right we're working with the dmax let's use dmax just to see what the largest word in the table is okay design view right click build event here's my code editor and i'm going to just shrink this down a little bit so you guys can see it better there we go i don't need the watch window and for this i don't really need the project explorer either and just to make things simple i'm going to get rid of all these buttons that we don't need okay just to just to clean things up a little bit here let's get rid of these guys too okay now all we really need is this hello world button right here okay so let's d-max the largest first word in the table okay so dim let's call it uh let's call it s as a string all right it's a string variable and s is going to be equal to d max the first word from the page table all right so find me the largest first word field in the page table okay then i'm going to status s which is just going to display it in that little box there okay and in fact here i'm going to make some more room here for this stuff let me slide this over here i'll get rid of my advertising okay save it yes close that open it back up again ready hello world boom george that's the largest value in that page team all right see that works if we want to see the largest page number just change the field right page number boom 5 is the largest page number okay let's go back to that the first word all right how do i see the smallest one well that's d min a related function right the minimum boom aaron okay so now what i'm actually looking for is i want to bring back the page number so again let's go back to page number okay and i want the largest page number all right dmax the largest page number where the first word is smaller than the target all right i want the largest page id where the first word is smaller than the target so we're going to add a criteria over here comma where the first word is less than or equal to the target so the target has to be inside of quotes inside of here so quote quote and target and one two three four quotes okay this is because this has to say first word is less than or equal to target and target itself has to be inside of quotes and that's just how we formulate that if this looks confusing i know i've got a video specifically on these double double quotes and string concatenation i'll put that in the links down below too okay so now when i run this i get a three why do i get a three well it's finding the largest page number where the first word is less than the target alright so the first word being less than target would be charles is the largest one all right if i change this to zoe all right i get a five which is perfect what if i put in here aaa just to try it boom i get invalid use of null why because it returned a value that doesn't exist there's no there's no record that fits this criteria so you might get a null value if someone puts in something below that so that's what nz is for nz and then comma and then what do you want to return if there is no value well we're bringing back a page number and page number is a number so bring back a zero okay now i can say actually since we're bringing back a number we should change s here instead of s let's use p all right then p for the page as along all right initially i was going to bring back a name like george and aaron when i started off with the name right d-max the name but if we're bringing back an id now we want to change that too along it still worked because access is pretty good of taking numbers and converting them into strings to display them but be very careful when you're doing actual comparisons of those values but see now i could say if p equals 0 then status no page valid or whatever right exit sub and if and now i can status and i can do something like um 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 all right how about frank frank should be on page four perfect right let's try zoe again there you go and that is how you use dmax which is very close to dlookup right to find the largest word that is less than the target the large the largest page number less than the target word and you could do the same thing if these were just letters in here right like we talked about before a through m is one thing we could go a right let's say uh h starts the next group right n r and then uh i don't know um v okay same same concept for our grouping before if i do zoe all right zoey will be on page five or in group five whatever you wanna call it okay if i type in bill all right bill's on page one or in group one nancy okay and page three and it's not case sensitive by the way okay uh rick say because rick is alphabetically alphanumerically larger than just an r all right there we go 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'll 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'll prompt you and say hey 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 well 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 tech help videos live video and chat sessions and other perks after 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 tech help videos are going to keep coming as long as you keep watching them i'll keep making more and they'll always be free if you enjoyed this video please give me a thumbs up and feel free to share it wherever you think it might help people who are interested in access make sure you subscribe to my channel which is completely free and click on the bell icon to select all to be notified every time i post a new video youtube no longer sends out email notifications when new videos are posted so if you'd like to get an email every time i post a new video click on the link below to join my mailing list click on the show more link below the video to find additional resources and links you'll see a list of other videos additional information related to the current topic free lessons and lots more now if you have not yet tried my free access level 1 course check it out now it covers all the basics of microsoft access it's over three hours long and you can find it on my website or on my youtube channel i'll include a link below you can click on and if you like level one level two is just one dollar and that is 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 tech help page and you can send me your question there if you have a specific problem you need help with or you'd like to discuss having a database built for your needs i do offer one-on-one consulting be sure to follow my blog and find me on facebook twitter and of course youtube once again my name is richard rost thank you for watching this tech help video brought to you by accesslearningzone.com i hope you enjoyed this video and you learned something today i'll see you again soon you

Channel: Computer Learning Zone

Views: 2,982

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, alpha grouping, alphanumeric, letter groups, group by names, group by letter, dlookup, dmax, increment, counter, group by words

Id: ZwItVL5kuqI

Length: 16min 13sec (973 seconds)

Published: Wed Dec 30 2020