Microsoft Access Update Query: Values from Another Table to Normalize a Field. Plus add World Flags!

welcome to another tech help video brought to you by accesslearningzone.com i'm your instructor richard ross in today's video we're going to talk about multi-table update queries we're going to use an update query with values from another table to retroactively normalize a field what does that mean i'll explain it in just a second today's question comes from louis from arlington virginia one of my gold members louis says i've got my state and country fields as text like you show in your videos however my users are entering values any which way i've got us usa you period s period you period s period a period united states and others how can i limit them to just one value well louis this brings up a topic called database normalization now i spent a lot of time covering this in my access expert level 2 class along with a bunch of other stuff like referential integrity but normalization basically says you don't want to have data in your database in any table that repeats itself there's a lot of other aspects to normalization but that's the big one take a look at this customer table for example we've got the country field here right but we've got us usus that's repeating data but then someone down here types in usa and then u.s okay and that's not good if i want to generate a query or a report showing all my customers from a particular country now i gotta deal with misspellings the same problem can happen with state too the way that you fix the problem is to properly normalize the table you use a country id then you create a second table a country table here i've got a country query but it's the same thing right and you link it by country id and you can see in your tables now with the country id the user picks from a combo box right so this gets saved as a one for the country and that translates to united states see how that works that's properly normalizing that table for the country field and now to pick the country the user can just pick from a combo box right like canada there you go now i'm going to show you how to put that little world flag in there in a few minutes too but the problem is right now we've got all of this data in our table so how do we take this and fix it and normalize it so we don't have to re-enter all this information and more importantly why do i show you how to do it this way first well this is easier for new users to comprehend when you're starting off building a database it's difficult to teach people an advanced concept like relationships and normalizing and all that stuff when they're still learning how to build tables okay so that's why in my intro and my beginner classes i tell them just to type in the country and then we'll fix it later it's not that hard to fix you're going to see in just a second here remember you got to learn how to walk before you can learn how to fly all right we're going to be flying pretty soon with our database but right now we're still in the jogging stage now before we continue three prerequisites relationships you got to know relationships go watch my relationship video if you don't know how to relate two tables together update queries i have an earlier update query video that shows you how to just update the values inside of one table in fact we're going to use part of that today to fix all these multiple different country names okay but then when it comes to updating it from the proper list of countries and setting the id we're going to do a multi-table update so that'll be new and if you want to follow along with the bonus material today where i show you how to put that world flag in there then go watch my images video first okay so watch these three videos right now pause and go watch those and then come back i'll put links down below in the links section you can click on them okay so here i am in my tech help free template this is a free download from my website you can go grab a copy if you want again links are down below watch the video on how i built this if you don't know how i did it you're not familiar with it already so the first thing we have to do is come into our customer table and scrub this and get it correct okay get it so that all the countries are the same now that's literally what i showed you how to do in the previous update query video okay we take new york new york new york all these misspellings of new york and fix it so they're all the same thing so do that first with your countries now i tell people leave your home country blank because that's probably where most of your customers are coming from so mine in the united states is all blank okay and that's fine but i want to change these all to be united states and make sure all your other ones like france germany whatever they're all spelled the same so you got to scrub that field first before you can do what we're going to do you just got to do it once there's no way around it okay you got to use manual update queries and fix the current list of all of your your countries in your table there's there's no magic pill for that okay unless you want to put all of the misspellings in your table that i'm going to show you how to do in a minute which is kind of silly so just scrub it once and then it'll be good from that point on so i'm going to make a quick update query to change all of the null values to united states okay so create query design bring in my customer table all right we're going to change this to an update query this is what we did in the last video bring in country all right criteria is null so where it's null i want to set it to united states like that okay run the query you might get the warning message but now if i look in my customer table i got united states okay so this is good close that close changes no no i'm gonna say that one okay so we're good so our our customer data is all good united states united states united states and i got one from france okay now for the purposes of class let's change a couple of these just let's make this one here canada which is fitting because uh shatner who just turned 90 today by the way today's bill shatner's 90th birthday happy birthday bill uh he's from canada and let's make this one greece okay all right so now we've got a couple of different countries in here okay now we have to make a country table so we can convert these over to ids alright so let's make the country table first okay so create table design country id that's my auto number the country name or just country that's fine and then let's put in here flag now flag is going to be a file name to wherever your flag image is and we'll talk about this at the end of the video all right so this is bonus material so save this as my country table country t make sure you get the r in there i always type in county by accident all right it's country now we're going to have to have a place to store that country id in the customer table so design the customer table come down here like right near the countries fine we can insert a row right click insert row and put in here country id that'll be a number of type long integer it's a foreign key pointing to the primary key in the country table which is country id there okay save it close it now if you only had six records like me you could just come in here and type in one three six two one whatever these values happen to be okay oh one more thing you might want to set the default value to be one which will make united states or whatever your home country is so let's do that real quick all right so country id i'm going to make this default value one which i'm going to make united states in the table as soon as we put some data in it all right now like i was saying before i so rudely interrupted myself if you only got six records no biggie just type them in right but let's pretend we got 60 000. i got 50 000 in my customer database i wouldn't want to sit there and type those all in so an update query will work just fine but we're going to update from the other table okay i want to pull in the id for the appropriate country make sense okay how do we do that well first let's throw some countries in our country table all right it's hard to do an update if you don't have any data in here all right so one i'm gonna have the united states we'll put the flags in in just a minute all right we'll do canada what else we got greece we'll do uh united kingdom and we'll do france okay we'll just start with those five okay close that save changes sure okay create query design bring in the customer table first then bring in the country table now access sees that you've got an auto number over here called country id and an auto number over here called country id as well and it relates them and that's normally what you want but in this case i don't want that so delete that relationship click delete what i want to do in this case is i want to relate the country text to the country text over here so click and drag that that forms a relationship based on the country text temporarily right we don't normally want to do that but this is so i can relate those together and pull the id over from this table to this one all right you follow me so right now if i brought in let's say first name last name country over here country over there you should see that they're matched up all right the relationship has been formed based on the country field all right we only want this for our update query though so back in design view what are we doing let's get rid of these fields here okay let's change this over to an update query now now what do i want to update i want to update the country id field and i want to set it to whatever this country id field is right there so down below here we're going to set update to equals country t dot country id okay see that i'm going to update the country id in the customer table to the related country id in the country table all right make sense now go ahead and run it you only have to do this once you'll get a warning message if you don't have warnings turned off that's fine now i'm going to save this query you won't need it again okay but i'm going to save it so when the gold members download the database they've got it let's call this the country update query all right that way it's in your database for you you can check it out if you need to close this all right let's check out our customer table now take a peek in here let's go make sure the country ids have been updated yes they have right united states is one francis five greece's three and so on okay looks good now that we've got the country id set we no longer need this country text so now we can go ahead and delete this from the customer table so go back to design view find the country text field and go ahead and delete it it'll say you permanently delete it are you sure make sure you got your backups first hold on hold on gotta pull this slide out once in a while all right make sure you back up your data before you run any kind of update queries or delete any fields all right go watch my backup video i just recently made a backup tech tech help video go watch that i'll put a link down below very important stuff always always always back up your data okay so i'm going to delete this country table yep okay it's gone so all i have is the id but that's fine that's all we need all right save it all right now let's go update our customer form notice we got pound name in there now all right because uh this form is based on a field called country and access is like i have no idea what this country field is all right so design view okay we're going to replace this with a combo box now if you don't know how to do this i've got a video called relational combo boxes how to make a combo box that's based on data from a different table go watch that video too i guess i should have made that a prerequisite as well let me change the thing hang on okay there it is relational combos i'll put a link down below in the link section too you can just click on that unless of course you like typing then go ahead and type it up okay this one's real easy find the combo box right there drop it right there all right i want to find values from another table or query all right from the country table bring in the country id and the country field don't need the flag field leave that there next sort it by country next there's our list of countries add more if you'd like next store that value in the country id field remember when you pick a value from a combo box that first column is the id that's the one that actually gets stored in the country id field in the customer table all right that's what's bound to this form next what label do you want country and we're done and i'll slide it up and slide it over and i'm going to format paint to get that thing so i'm going to go to the format tab format painter and paint there you go and make that a little bit bigger okay and we're going to adjust the tab order on the design tab right tab order i'll put oh it's combo 30. let's fix that i don't want alex yelling at me oh someone just beamed in all right let's fix this there's so much to do so many little details right let's change combo 30 i don't like combo 30. all right let's make this the country combo you can make a country id if you want to i like to call my combo boxes combo okay let's put that in the right spot in the tab order tab order and let's take country combo move it right after zip alright so slide it up i cover this in my access beginner classes by the way so go watch those if you missed anything that i just did there and you didn't know what i just did okay all right here we go save it close it open it up there we go united states drop it down you can pick go to a different one there's canada okay all right grease now that's fixed and if you change it in here if i change this one here to canada and close it go back to my customer table notice the id is now two all right this table is now properly normalized as far as the country is concerned all right you could do the same thing with state if you want to usually i don't have a problem if it's if state is just a u.s two-digit state i don't usually have a problem with that but you can get the same problems with country that you can with state and that's a whole different video okay yes you can make it so you can pick the country and then have the state based on the country using something called a cascading combo box yeah i got a video on that too on cascading combo boxes i'll put a link to that down below that's actually one of my older videos i'll probably update this sometime soon but here i let you pick the state and then you get a list of cities from the state same thing you could just add a third one if you want to put country in there too okay now are you ready for the bonus material ready to have some fun with flags right put a little flag down there i uh i mentioned to alex earlier today in chat that i was going to uh to show this flag video and uh he posted the big bang theory fun with flags one of my favorite shows by the way okay so i want to put a little picture of the flag down underneath here okay we made a field in our country table to store the flag so we're going to put the file names for our flags in here how do we know what the file names are well we need we need to go grab some flags somewhere now i've got them on my website you can use the ones i've got if you want to i grabbed them from somewhere else i don't know where but so close out of your database for a moment first things first here's my database right here's the folder it's in it's in my tech help folder on my desktop right so i usually record my videos from let's make a flags folder inside of here right right click new folder let's just call this flags so we're going to put our flag images all right open that up now go somewhere around the web where you know you can get flags if you want you can go to the badges page on my website right it's 599c.com badges yeah i'll put a link to that down below too you can steal my flags i don't mind all right scroll down i give my users different badges based on what they've accomplished right beginner expert developer right my memberships insider circles right and here's all the members gold silver platinum all right anniversaries i give people ranks which is kind of cool captain commander all that stuff okay i'm a trek nerd but down here there's the world flags so if you're from a country it'll show up there if you want to grab these be my guest i grabbed them somewhere else i didn't make them myself all right so all you gotta do is just click and drag oh drop them there right uk click drag boom drop it there canada and so on grab whatever flags you want okay now these are the file names we're gonna need okay norway all right to put in our database okay i think i've got well we're good that's good for now all right just grab the ones i've got in my database now you can grab all them if you want to go get more if you want i don't care all right it's your database do whatever you want with it but but notice what these file names are okay now let's go back into our database open up your country table put the flag names in here okay now you don't got to put the whole thing just a piece of it what are you going to put in there just put the file name part you don't need jpeg unless they're different if they're all jpegs you can get away with this watch this so united states is just u.s okay canada is canada okay do i have grease in there i don't think i have grease in there sorry i know i had it on my page no i didn't have it on my page sorry sorry folks from greece um i i picked these based on the countries that i have the most students from i know i've got some students from greece but not a ton so i'll add you guys soon i promise all right but for now i'm not gonna do it for the class but you get the point okay so greece is missing and that's okay all right you might have some flags not in the system it'll just it just won't show anything okay that's okay all right uk right there uk and then where's france and did i put france in there i don't think i put france there all right all right we don't have france either okay so i got uk canada u.s that means i don't have a lot of students from france either i know most people you know english-speaking world i know i don't speak french i'm sorry i i i wanted to learn it but i never got around to it i took latin in high school all right let's put some more stuff in here though germany just so we can pick from stuff all right germany and uh it's not case sensitive so it doesn't really care if you do all uppercase or lowercase okay australia australia okay anyways moving on so we've got our flags that we have in our flag table here okay and our country table excuse me all right let me close this now what we're going to do is we're going to make a country query that has the full path to that flag file name all right how do we do that well let's go make a query all right create query design bring in the country query just the country query bring in the star and right here we're going to do flag full path colon what's that going to be equal to let me zoom in first you can see this shift f2 let me zoom in okay all right go back to your windows explorer window here all right here's your flags folder okay now clicking here there's the full path to the flags folder all right so copy that ctrl c copy that come in here put that inside of quotes all right there's the first part of it all right put a backslash on the end there okay then we're going to concatenate add on to that the flag file name which is flag okay so the flag field whatever's in the flag field and jpeg all right that's string concatenation if you've never done string concatenation before these little ampersand signs go watch that video too i guess there's even more prerequisites i'm not putting it on the form though all right hit ok now let's save this this will be my country q my country query that's got the flag names in it run it and there you go all right there's all your flags oh i'm missing some here okay so that one's missing okay so let's deal with that being uh null and use an if function okay let's come in here and we'll say right here if iif the immediate if function if is null flag glad no flag right comma use that otherwise an empty string let me zoom in again so you can see that better okay there you go if the immediate if function and again if you don't know what that is i've got a video for it pause this go watch that all right is null says there's no value in flag so if is null flag then oh i got these backwards so now backwards empty string comma that right there okay so if flag is null make this an empty string make it nothing otherwise put the actual flag name in there all right that's the immediate if function all right now we can run it and now that'll be blank if there's no flag in the flag field okay now since you watched my images video you know that i can now use this to display that image on the customer's form okay so let's link this flag full path field into the customer form somehow how do we do that well we do that with another query let's make a customer query alright so create query design bring in the customer table which is what our form is currently based on now we're going to link to it that country cue that we made before all right now since that's a query it doesn't always make that relationship so you got to make the relationship country id over here to country id all right now double click on this guy and set it to number two include all records from customer t and only the records from country queue where the join fields are equal in case you have a customer where you didn't pick a country for them which shouldn't happen but it's possible all right you should go back and make the country field required not a bad idea the default is one okay but just in case you don't have the country in the list and you don't feel like adding it blah blah blah that could happen all right that's called a left join and again i got videos on that if you want to watch what this is about but now we can bring in the star from over here and the flag full path over here that's the only one we need okay we don't need the country text because it shows up in the combo box we don't need the flag part of the file name right we just want the flag for path save this as customer queue or customer with flag queue if you want to we don't have a customer queue yet we got that customer lf cube but we don't really use that much that's for last name first name okay now run this and you'll see here's all your customers and their flags all right now i can use this in the form to display that picture so go to the customer form now that i've got all the work done it's easy to do now right design view open this up find the picture control right there this guy image control sorry image put the flag size it for the flag right right about there cancel this now open up the properties for the flag all right the control source is going to be oh i didn't change i didn't rebind the form right that's okay good thing i'm i missed a step that's fine i'm gonna i'm gonna leave this in the video to show you that i do things like this too all right i'm like where is that flag okay well i gotta change where this form gets its data from right the record source for the form drop this down pick customer queue now all right now this form will get its values from that query which is fine all right now i can go in here and change the control source to flag full path and also don't forget to copy and paste and change the name so it's not image32 okay now since this is bound to that field the flag should show up there automatically save it close it open it back up again and there's your flag you can make it as big or small as you want all right go to the next one go to the next one there's canada right and if you change somebody change this to germany it updates automatically no programming involved i haven't done a shred of vba yet i try to keep vba for the extended cut videos anyways i try to keep these basic okay but the query puts together the file name like you just saw a second ago and the file name is what's bound to this picture control so as you change it it just automatically updates right what else we got in there uk boom say that actually jim kirk should be canadian there we go okay and i'm gonna go back to the us perfect so there you go there's your phone with flags now want to see something cool this is what i do with the members you ready look at that pop-up window pick what you want click update's back here and that neat look at that uh-huh hey you like that all right i got into a discussion with alex um i've seen some websites for example where you can click on this and you can have a combo box that has the little flag in it right next to the name so you can pick it and it updates automatically now unfortunately there's no way in access to do that with the built-in native combo box control it's just it's not possible yes i've seen some third-party activex controls that you can get to plug into access to do that but if you've watched any of my other videos or courses you know i hate external components with a passion i've seen them cause all kinds of problems with access i like to keep my design to just what's built into access with very few exceptions but the good news is i can replicate pretty much everything you need with the native controls okay so here's the normal combo box but i change it so you can click on this it pops up a window another form where you can see the list of all the countries and the flags right here pick one of them and it updates right back here just like you would if this was just a normal combo box so i will show you how to do that in the extended cut for members there you go multi-table update queries in the extended cut silver members and up get access to all of my extended cut videos gold members can download the templates that i create for the tech help videos 34 minutes long this one i've got over 100 extended cut videos available so there's plenty for you to watch if you sign up and become a member in this particular one i'll show you how to make that country pop-up and i'll show you how to return the value that's selected to whatever form calls it so you can call that from multiple forms the customer form the customer contact form or whatever and that little pop-up form will return the value to the appropriate form we'll cover that in the extended cut how do you become a member click the join button below the video after you click the join button you'll 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 tech help 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 but don't worry these free tech help videos are going to keep coming as long as you keep watching them i'll keep making more if you like 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'll 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'd like to get an email every time i post a new video click on the link to join my mailing list now if you have not yet tried my free access level one course check it out now it covers all the basics of building databases with access it's over three hours long you can find it on my website or on my youtube channel and if you like level one level two is just one dollar and it's 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 tech help page and you can send me your question there click here to watch my free access beginner level 1 course more of my tech help videos or to subscribe to my channel thanks for watching this video from accesslearningzone.com

Channel: Computer Learning Zone

Views: 8,439

Rating: 4.968504 out of 5

Keywords: microsoft access, ms access, #msaccess, #microsoftaccess, #help, #howto, #tutorial, #instruction, #learn, #training, #database, multi-table update query, update query from another table, update from other table, images in a combo box, pictures in combo box, picture drop down, add flag image to customer form, world flags, display country flag, normalize, normalization, use field in one table to update a field in another table, update table from another table, change data in multiple tables

Id: TVw9rqYWk9o

Length: 29min 41sec (1781 seconds)

Published: Wed Mar 24 2021