|
Free Lessons Courses Seminars |
TechHelp Fast Tips Templates |
Topic Index Forum ABCD |
|
||||||
|
|
Multi-Table Update By Richard Rost 
5 years ago
Update from Another Table. Plus World Flags! In this video, I'll show you how to properly normalize the country field in your customer database retroactively by creating a country table, and then saving your existing data with update queries. Then, as a BONUS, I'll show you how to display that country's flag on the customer form. Louis from Arlington, Virginia (a Gold Member) asks: 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, U.S., U.S.A, United States, and others. How can I limit them to just ONE value? MembersMembers are going to learn how to create a popup form to allow the users to pick the country flag from a list of world flags and return that selection to ANY form that calls it.
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, multi-table update query, update query from another table, update from other table, CurrentProject.Path, 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
IntroIn this video, we will talk about how to use multi-table update queries in Microsoft Access to retroactively normalize a field using data from another table. We will look at how to fix inconsistent country names in a customer table by creating a separate country table, adding a foreign key, and running an update query to match records with country IDs. We will also discuss preparing your data, creating lookup combo boxes for user entry, and showing related images like country flags on your forms.TranscriptWelcome to another TechHelp video brought to you by AccessLearningZone.com. I am your instructor, Richard Rost.In today's video, we are going to talk about multi-table update queries. We are going to use an update query with values from another table to retroactively normalize a field. What does that mean? I will explain it in just a second. Today's question comes from Louis from Arlington, Virginia, one of my gold members. Louis says, I have my state and country fields as text like you show in your videos. However, my users are entering values any which way. I have US, USA, U.S., U.S.A., United States, and others. How can I limit them to just one value? Well Louis, this brings up a topic called database normalization. I spend a lot of time covering this in my Access Expert Level 2 class, along with a bunch of other topics like referential integrity. But normalization basically says you do not want to have data in your database in any table that repeats itself. There are a lot of other aspects to normalization, but that is the big one. Take a look at this customer table for example. We have the country field here, but we have US, US, US, that is repeating data. Then someone down here types in USA and then US again. That is not good. If I want to generate a query or a report showing all my customers from a particular country, now I have to 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 have a country query, but it is the same thing, and you link it by country ID. You can see in your tables now with the country ID, the user picks from a combo box where I say this gets saved as a one for the country and that translates to United States. See how that works? That is properly normalizing that table for the country field. Now, to pick the country, the user can just pick from a combo box, like Canada. How much do I have to put that little world flag in there in a few minutes too? The problem is right now we have all of this data in our table. How do we take this and fix it and normalize it, so we do not have to re-enter all this information? More importantly, why do I show you how to do it this way first? This is easier for new users to comprehend. When you are starting off building a database, it is difficult to teach people an advanced concept like relationships, normalizing, and all that stuff when they are still learning how to build tables. That is why in my intro and my beginner classes, I tell them just to type in the country and then we will fix it later. It is not that hard to fix. You are going to see in just a second here. Remember, you have to learn how to walk before you can learn how to fly. We are going to be flying pretty soon with our database, but right now we are still in the jogging stage. Now, before we continue, three prerequisites: relationships. If you have no relationships, go watch our relationship video if you do not 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 are going to use part of that today to fix all these multiple different country names. Then, when it comes to updating it from the proper list of countries and setting the ID, we are going to do a multi-table update. So that will be new. 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. Watch these three videos right now. Pause and go watch those, then come back. I will put links down below in the link section. You can click on them. Here I am in my TechHelp 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 do not know how I did it and you are not familiar with it already. The first thing we have to do is come into our customer table and scrub this and get it correct. Get it so that all the countries are the same. That is literally what I showed you how to do in the previous update query video. We take New York, New York, New York, all these misspellings of New York, and fix it so they are all the same thing. Do that first with your countries. Now, I tell people leave your home country blank because that is probably where most of your customers are coming from. So mine, United States, is all blank. That is fine, but I want to change these all to be United States and make sure all your other ones like France, Germany, whatever, they are all spelled the same. So you have to scrub that field first before you can do what we are going to do. You just have to do it once. There is no way around it. You have to use manual update queries and fix the current list of all your countries in your table. There is no magic pill for that unless you want to put all of the misspellings in your table, which I am going to show you how to do in a minute, which is kind of silly. So just scrub it once and then it will be good from that point on. I am going to make a quick update query to change all of the null values to United States. Create query design, bring in my customer table. Change this to an update query. Bring in country. Criteria is null. So where it is null, I want to set it to United States like that. Run the query. You might get the warning message. But now if I look in my customer table, I have United States. This is good. Close that. Close. Save changes. No, I am not going to save that one. We are good. So our customer data is all good: United States, United States, United States, United States, and I have one from France. For the purposes of class, let us change a couple of these. Let us make this one here Canada, which is fitting because Shatner, who just turned 90 today (by the way, today is Bill Shatner's 90th birthday, happy birthday Bill) is from Canada. Let us make this one Greece. Now we have a couple of different countries in here. Now we have to make a country table so we can convert these over to IDs. Let us make the country table first. Create, Table Design. Country ID, that is my AutoNumber. The country name. Or just country, that is fine. Then let us put flag. Now flag is going to be a file name to wherever your flag image is, and we will talk about this at the end of the video. This is bonus material. Save this as my country table, CountryT. Make sure you have the R in there. I always type in county by accident. It is country. Now we are going to have to have a place to store that country ID in the customer table. So design the customer table. Come down here, right near country is fine. We can insert a row: right click, insert row, and put in here CountryID. That will be a number of type long integer. It is a foreign key pointing to the primary key in the country table, which is CountryID. Save it. Close it. Now if you only had six records like me, you could just come in here and type in 1, 3, 6, 2, 1, whatever these values happen to be. One more thing: you might want to set the default value to be 1, which will make United States or whatever your home country is. Let us do that real quick. CountryID, I am going to make this default value 1, which I am going to make United States in the table so we put some data in it. Now, like I was saying before I rudely interrupted myself, if you have only got six records, no biggie, just type them in. But let us pretend we have got 60,000. I have got 50,000 in my customer database. I would not want to sit there and type those out. So an update query will work just fine, but we are going to update from the other table. I am going to pull in the ID for the appropriate country. How do we do that? First, let us throw some countries in our country table. It is hard to do an update if you do not have any data in here. So 1 will be United States. We will put the flags in in just a minute. We will do Canada. What else have we got? Greece. We will do United Kingdom. We will do France. We will just start with those five. Close that. Save changes. Sure. Create, Query Design, bring in the customer table first, then bring in the country table. Access sees that you have got an AutoNumber over here called CountryID, and it relates to them, and that is normally what you want. But in this case, I do not 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. We do not 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. If I brought in, let us say, first name, last name, country over here, country over there, you should see that they are matched up. The relationship has been formed based on the country field. We only want this for our update query. Back in design view, let us clear these fields here. Let us change this over to an update query now. What do I want to update? I want to update the CountryID field, and I want to set it to whatever this CountryID field is right there. Down below here, we are going to set Update To = CountryT.CountryID. I am going to update the CountryID in the customer table to the related CountryID in the country table. Go ahead and run it. You only have to do this once. You will get a warning message if you do not have warnings turned off. That is fine. I am going to save this query. You will not need it again, but I am going to save it so when the gold members download the database, they have it. Let us call this the country update query. That way it is in your database for you. You can check it out if you need to. Close this. Let us check out our customer table now. Take a peek in here. Let us make sure the CountryIDs have been updated. Yes, they have. United States is 1, France is 5, Greece is 3, and so on. Looks good. Now that we have got the CountryID set, we no longer need this country text. We can go ahead and delete this from the customer table. Go back to design view. Find the country text field and go ahead and delete it. It will say you are permanently deleting it. Are you sure? Have you got your backups first? Make sure you backup your data before you run any kind of update queries or delete any fields. Go watch my backup video. I just recently made a backup TechHelp video. Watch that. I will put a link down below. Very important stuff. Always backup your data. So I am going to delete this country field. Yes, OK, it is gone. Now I have just the ID. That is fine, that is all we need. Now, I am going to go to the form. Nobody's got #Name in there now, because this form is based on a field called country, and Access is like, I do not know what this country field is. Design view. We are going to replace this with a combo box. If you do not know how to do this, I have a video called relational combo boxes on how to make a combo box that is based on data from a different table. Go watch that video too. I should have made that a prerequisite as well. Let me change the thing, hang on. There it is. I will put a link down below in the link section too. You can just click on that, unless you like typing; then go ahead and type it out. This one is real easy. Find a combo box, right there. Drop it right there. I want to find values in a table or query. From the country table, bring in the CountryID and the country field. Do not need the flag field. Next. Sort it by country. Next. There is our list of countries. Add more if you would like. Next. Store that value in the CountryID field. Remember, when you pick a value from a combo box, that first column is the ID, that is the one that actually gets stored in the CountryID field in the customer table. That is what is bound to this form. Next, what label do you want? Country. We are done. Slide it up and slide it over. I am going to use format painter to get that thing. Go to the format tab, format painter, and paint. There you go. Make that a little bigger. Adjust the tab order on the design tab. Tab order. It is combo30. Let us fix that. I do not want Alex yelling at me. Let us fix this. There is so much to do. So many little details. Let us change combo30. I do not like combo30. Let us make this the country combo. You can name it CountryID if you want to. I like to call my combo boxes combo. Let us put that in the right spot, in the tab order. Let us take country combo and move it right after zip. Slide it up. I cover this in my Access beginner classes, so go watch those if you missed anything I just did there. Save it. Close it. Open it up. There we go. United States. Drop it down. You can pick. Go to a different one. There is Canada. Greece. Now that is 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 2. This table is now properly normalized as far as the country is concerned. You could do the same thing with state if you want to. Usually I do not have a problem if state is just a US two-digit state, but you can get the same problems with country as you can with state. That is a whole different video. 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. I have a video on that too, on cascading combo boxes. I will put a link to that down below. That is actually one of my older videos. I will probably update it sometime soon. In that one I let you pick the state, and then you get a list of cities from the state. You could add a third one if you want to put country in there too. Now, are you ready for the bonus material? Ready to have some fun with flags? Put a little flag down there! I mentioned to Alex earlier today in chat that I was going to show this flag video, and he posted the Big Bang Theory Fun with Flags—one of my favorite shows, by the way. I want to put a little picture of the flag down underneath here. We made a field in our country table to store the flag. We are going to put the file names for our flags in here. How do we know what the file names are? We need to go grab some flags somewhere. I have some on my website. You can use the ones I have if you want. I grabbed them somewhere else. I do not know where. So close out of your database for a moment. Here is my database. Here is the folder it is in. It is in my TechHelp folder on my desktop where I usually record my videos. Let us make a flags folder inside of here. Right click, new folder. Let us call this flags. We are going to put our flag images in here. Open that up. Go somewhere on the web where you can get flags. If you want, you can go to the badges page on my website. It is 599cd.com/badges. I will put a link to that down below too. You can use my flags. I do not mind. Scroll down. I give my users different badges based on what they have accomplished, like beginner, expert developer, memberships, insider circles. There are all the members, gold, silver, platinum. Anniversaries. I give people ranks, which is kind of cool: captain, commander, all that stuff. I am a Trek nerd. Down here, there are the world flags. If you are from a country, it will show up there. If you want to grab these, be my guest. I grabbed them somewhere else, I did not make them myself. All you have to do is just click and drag, drop them there—USA, click drag, drop it there. Canada, and so on. Grab whatever flags you want. These are the file names we are going to need: Norway. Put them in your database. That is good for now. Just grab the ones I have in my database for. You can grab all of them if you want. Go get more if you want. It is your database, do what you want with it. Notice what these file names are. Let us go back into our database. Open up your country table. Put the flag names in here. You do not have 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 do not need .jpeg unless they are different. If they are all JPEGs, you can get away with this. Watch this. United States is just US. Canada is Canada. Do I have Greece in there? I do not think I have Greece in there. No, I did not have it on my page. Sorry, folks from Greece. I picked these based on the countries that I have the most students from. I know I have some students from Greece, but not a ton. I will add you guys to it, I promise. But for now, I am not going to do it for the class. You get the point. Greece is missing and that is okay. You might have some flags not in the system; it just will not show anything. UK, right there. And then, where is France? Did I put France in there? I do not have France in there. That means I do not have a lot of students from France either. Most people are in the English speaking world. I do not speak French, I'm sorry. I wanted to learn it, but I never got around to it. I took Latin in high school. Let us put some more stuff in here: Germany, just so we can pick from stuff. Germany. It is not case sensitive, so it does not really care if you do all uppercase or lowercase. Australia. Anyway, moving on. Now we have our flags that we have in our country table. Let me close this. Now we are going to make a country query that has the full path to that flag file name. How do we do that? Let us go make a query. Create, query design. Bring in the country table. Bring in the star. Right here, we are going to do FlagFullPath: what is that going to be equal to? Let me zoom in first so you can see this. Shift+F2. Let me zoom in. Go back to your Windows Explorer window. Here is your flags folder. Click in here. There is the full path to the flags folder. Copy that. Control+C. Come in here. Put that inside quotes. There is the first part of it. Put a backslash on the end. Then we are going to concatenate (add onto) that the flag file name, which is flag. The flag field, whatever is in the flag field, plus ".jpeg". That is string concatenation. If you have never done string concatenation before using these little ampersand signs, go watch that video, too. Hit OK. Save this as CountryQ, my country query. That has got the flag names in it. Run it, and there you go. There are all your flags. Oh, I am missing some here. If that one is missing, let us deal with that being null, and use the immediate if function. Come in here and say right here, IIf(IsNull([flag]),"", <path and flag stuff>). Let me zoom in again so you can see that better. There you go. The immediate if function, and again, if you do not know what that is, I have a video for it. IsNull checks if there is no value in flag. So, if IsNull(flag), give an empty string, otherwise put the actual flag name in there. That is the immediate if function. Now we can run it, and now it will be blank if there is no flag in the flag field. Now, since you watched my images video, you know that I can now use this to display that image on the customer's form. Let us link this FlagFullPath field into the customer form somehow. How do we do that? We will do that with another query. Let us make a customer query. Create query design. Bring in the customer table, which is what our form is currently based on. Now we are going to link it to that CountryQ that we made before. Now, since that is a query, it does not always make that relationship, so you have to make the relationship: CountryID over here to CountryID. Double click on this guy and set it to number 2. Include all records from CustomerT and only the records from CountryQ where the join fields are equal. In case you have a customer where you did not pick a country for them (which should not happen, but it is possible), you should go back and make the country field required, not a bad idea. The default is 1. But just in case you do not have the country in the list and you do not feel like adding it—that could happen. That is called a left join. I have videos on that if you want to watch what this is about. Now bring in the star from over here and the FlagFullPath over here. That is the only one we need. We do not need the country text because it shows up in the combo box. We do not need the flag part of the file name. We just want the FlagFullPath. Save this as CustomerQ or CustomerWithFlagQ if you want to. We do not have a CustomerQ yet. We have that CustomerLFQ, but we do not use that much; that is for last name, first name. Run this, and you will see here are all your customers and their flags. Now I can use this in the form to display that picture. Go to the customer form. Now that we have all the work done, it is easy to do now. Design view. Open this up. Find the picture control. Image control. Put the flag, size it for the flag, right about there. Cancel this. Now open up the properties for the flag. The control source is going to be—oh, I did not rebind the form. That is OK. Good thing I missed a step, I am going to leave this in the video to show you that I do things like this too. You have to change where this form gets its data from. The record source for the form, drop this down, pick CustomerQ now. Now this form will get its value from that query, which is fine. Now I can go in here and change the control source to FlagFullPath. Also, do not forget to copy and paste and change the name so it is not Image32. 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 is your flag. You can make it as big or as small as you want. Go to the next one. There is Canada. If you change somebody to Germany, it updates automatically. No programming involved! I have not done a shred of VBA yet. I try to keep VBA for the extended cut videos. I try to keep these basic. The query puts together the file name and the file name is what is bound to this picture control. As you change it, it just automatically updates. What else have we got in there? See that? Jim Kirk should be Canadian. There we go. I am going to go back to the US. Perfect. There you go. There is your fun with flags. Now, want to see something cool? This is what I do with the members. Look at that. Pop up a window, pick what you want, click, updates back here. Is that not neat? I got into a discussion with Alex. I have 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. You can pick it and it updates automatically. Unfortunately, there is no way in Access to do that with the built-in native combo box control. It is just not possible. Yes, I have seen some third-party ActiveX controls that you can get to plug in to Access to do that. But if you have watched any of my other videos or courses, you know I hate external components. I have seen them cause all kinds of problems with Access. I like to keep my design to just what is built into Access, with very few exceptions. The good news is I can replicate pretty much everything you need with the native controls. Here is the normal combo box. But I have changed 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. 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 TechHelp videos. I have over 100 extended cut videos available, so there is plenty for you to watch if you sign up and become a member. In this particular one, I will show you how to make that country pop-up, and I will show you how to return the value that is 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 will 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 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 have built 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. You will no longer receive 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. 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 purpose of using a multi-table update query as described in this video?A. To update unrelated tables with random data B. To retroactively normalize a field by assigning values from another table C. To create a new table from scratch D. To convert text fields to image fields Q2. What database concept was emphasized as important when handling fields like country or state in a table? A. Data redaction B. Database normalization C. Data encryption D. Database backup Q3. What is the primary problem with letting users type country names freely in a text field? A. Users may erase the data accidentally B. There is no way to retrieve the data later C. The data may be entered inconsistently, causing duplicates and misspellings D. It prevents you from running any queries at all Q4. What is the solution for standardizing country entries suggested in the video? A. Delete all inconsistent entries B. Replace all country names with numbers at random C. Use a separate country table and store a CountryID as a foreign key in the customer table D. Ignore the problem, as it will fix itself over time Q5. Why does Richard say he teaches new users to start with text fields for country and state before normalizing? A. Normalization is not needed in small databases B. Beginners cannot use lookup tables in Access C. It is easier for beginners to understand and normalization can be applied later D. Access does not support relationships in early stages Q6. What must be done before you can use a multi-table update query to assign CountryIDs? A. Delete all the customer records B. Import a flag picture for each record C. Scrub and standardize all country text entries D. Change all fields to Yes/No type Q7. What field type is recommended for the CountryID field in the customer table? A. Short Text B. Currency C. AutoNumber D. Number (Long Integer) Q8. What is the default value setting used for CountryID to represent the primary or home country? A. 0 B. 1 C. United States D. Null Q9. When creating the update query to set the CountryID, which fields are the relationships temporarily linked by? A. CustomerID and CountryID B. Country text fields in both tables C. Last Name and CountryID D. State and CountryID Q10. After assigning CountryIDs with an update query, what should be done with the original country text field in the customer table? A. Hide it on forms B. Set it to blank C. Permanently delete it after backup D. Convert it to a hyperlink Q11. What is the benefit of using a combo box bound to CountryID on your customer form? A. It lets you type random country names B. It restricts users to choosing valid countries only C. It hides the country name D. It only accepts numbers Q12. What do you need to do if you want to add a flag image for each country? A. Store the flag image in the customer table B. Add a flag field in the country table and reference image filenames C. Draw flags directly in Access D. Only use the country name as the flag image path Q13. In the query that builds the image path for flags, what function is used to handle missing flag filenames? A. Len() B. IIf() C. Sum() D. Nz() Q14. Which type of relationship was created in the customer query to ensure all customers are listed, even if no matching country record exists? A. Right join B. Full join C. Left join D. Cross join Q15. What is NOT true about displaying flag images as described in the video? A. The image control is bound to the FlagFullPath field B. No VBA coding is required for basic image display C. Only third-party controls will work for this feature D. The form's record source must be updated to a query with the flag path Q16. What limitation does Access have compared to some websites regarding country combo boxes? A. Access supports images directly in combo box lists B. Access combo boxes cannot display images natively C. Access can only handle five countries at a time D. Access does not support foreign key relationships Q17. When performing any table structural changes or update queries, what important action is recommended first? A. Notify users by email B. Print a hard copy of the data C. Make a backup of your database D. Add a new password Q18. Which Access control is used to select countries after normalization? A. Option group B. Combo box C. Text box D. Checkbox Q19. What does database normalization help to avoid in this context? A. Reducing the number of records in your database B. Having repetitive, inconsistent data entries C. Preventing queries from running at all D. Creating multiple tables for the same information Q20. If you want to display state and city based on the selected country, what feature should you use? A. Relational combo box B. Data macros C. Cascading combo box D. Hyperlinks Answers: 1-B; 2-B; 3-C; 4-C; 5-C; 6-C; 7-D; 8-B; 9-B; 10-C; 11-B; 12-B; 13-B; 14-C; 15-C; 16-B; 17-C; 18-B; 19-B; 20-C 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 explores how to use multi-table update queries in Microsoft Access to help normalize a field after data has already been entered in a less-than-ideal way. I will be showing you how you can use values from another table to update and standardize your data, taking you step by step through the process of cleaning up a text field that contains duplicate and inconsistent inputs.Suppose you have a customer table with a text field for country. As is common, users may enter variations such as US, USA, United States, or even different abbreviations. This inconsistency can create big problems when you want to run queries, create reports, or ensure overall data quality. The solution is database normalization, which I discuss in depth in my Access Expert Level 2 class. In short, normalization means you want to avoid repeating data in your tables. For country data, the best approach is to store a numeric country ID in your customer table and use a separate countries table that holds the actual country names (with one record per country). If you take a look at an unnormalized customer table, you will notice plenty of repeated and misspelled country names. Not only does this waste space, but it also makes it much harder to work with your data. Instead, the normalized approach is to present your users with a combo box tied to the countries table. The user selects the country name, and the associated country ID is stored in the customer table. The challenge, of course, comes when you have an existing table full of inconsistent country names. You want to convert this data over to use country IDs, but without retyping everything by hand. Before working on the update, it's crucial to understand a few prerequisites: you should be comfortable with table relationships in Access, know how update queries work for modifying data in a table, and have some experience updating values using lists from another table. You should also know how to work with images in Access if you want to follow along with the flag-image bonus material. To start the cleanup process, the first step is to make sure your country names are consistent across all records. For example, change every variant and misspelling to a single, correct value. This often requires running update queries or making manual corrections for a one-time data scrub. My advice is to leave the country field blank for your home country if most of your customers are domestic, but standardize everything else. As an example, suppose you want to set all blank (null) country fields to "United States." You can use an update query to accomplish this. Once your data is consistent, you will be ready to move on. The next step is to build a country table. This table should have an AutoNumber primary key for CountryID, a field for the country name, and, optionally, a field for the flag image file name. You need to then add a CountryID field to your customer table. This is a Number field of type Long Integer, acting as a foreign key pointing to the country table. For a small database, you could fill in the CountryID values by hand. But, in a database with many records, that is not practical. Instead, you need to perform an update query that pulls the correct CountryID from the countries table and updates each customer record with the corresponding value. To do this, first populate your countries table with all the unique country names present in your customers, making sure each has an assigned ID. To set the CountryID in your customer records, you will need a multi-table update query. You relate the country name field in the customer table to the country name in the country table (temporarily), so that you can copy over the proper ID for each country. Once you run the query, your customer records will now have the correct CountryID set. Once this is done, you no longer need the old text country field in your customer table. It is perfectly fine to delete this field, but make sure to back up your data first. I recommend always making a backup before any destructive operation or major update. With your customer table now storing only CountryID, you will probably notice that your related forms and controls referencing the old country field need to be updated. The solution is to use a combo box control that draws its unique list of options from the countries table, displaying the names, but storing the IDs. If you are unfamiliar with building relational combo boxes, I have additional videos that cover this topic in detail. When adding the combo box, set it to display country names from the country table, but ensure it is bound to the CountryID field in the customer table. Adjust the control's name and tab order as needed, then try adding and modifying records to verify everything works as expected. Any changes made in the form will now correctly update the CountryID in your customer table, maintaining proper normalization. The same approach can be used for state or province fields, especially if you are experiencing similar inconsistencies. While I typically don't see as many issues with U.S. state abbreviations, the normalization concept applies equally. You can even make your combos "cascade," for example, filtering the list of states based on the selected country. There are videos on cascading combo boxes and related techniques available on my website. As a bonus, I also cover how you can display flag images alongside each country. In your country table, add a field for the name or path of the flag image. Put the flag image files into a known folder (such as a "flags" folder within your database directory). Only the base file names are needed in your table if all your images are the same file type. The next step is to build a query that concatenates the folder path with the file name from the country table. If you do not have an image for a particular country, use an immediate-IF function to handle missing values gracefully. Create a query that combines all required fields with the computed field giving the full flag path for each country. You will then want to adjust your customer data source so your customer form is based on a query that links customers to their associated countries. Bring in the full path of the flag image using the joined tables or queries. You can then add an image control to your customer form, bind it to the flag image path, and you should see the flag automatically update as you select different countries for your customers. No VBA is required for these steps; it's all handled by queries and form controls. Some users may ask about having a combo box that displays actual flag images inside the drop-down list. Access does not support images inside combo box items using the built-in controls. While some third-party add-ons and ActiveX controls claim to provide this functionality, I strongly recommend sticking to native Access features to avoid compatibility problems down the line. For advanced members interested in even more user-friendly interfaces, I also demonstrate in the extended cut how to create a popup form where users can pick a country from a list, complete with flag images, and have their choice automatically update the customer form. This popup selection form can be reused from any form in your database. I walk through building this step by step in the extended cut, available to Silver members and above. Gold members can download all of my sample databases, including those from TechHelp videos, and have access to my code vault and many full-length classes. Platinum level grants access to my comprehensive library, including Microsoft Word, Excel, Visual Basic, and more. As always, make sure you subscribe to the channel and turn on notifications for updates. You can find all the additional resources, including links to prerequisite videos, download samples, and sign up for the mailing list, on my website. If you are just getting started, try my free Access Level 1 course, which covers all the basics step by step. And if you have a question you would like answered in a future video, visit my TechHelp page. 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 ListWhat is database normalization and why it's importantProblems with inconsistent country data entries Scrubbing and standardizing existing country data Creating an update query to fix null country values Designing a Country table with AutoNumber ID Adding a CountryID foreign key to Customer table Setting default value for CountryID in Customer table Populating the Country table with country records Creating a multi-table update query to set CountryID Relating tables temporarily by country text for migration Updating Customer table with the correct CountryID Removing the old country text field from Customer table Replacing the country field on the form with a combo box Binding the combo box to the CountryID field Adjusting tab order and naming the combo box Demonstrating form functionality with the new combo box Creating a query to build the full file path for each flag Using Immediate If (IIf) to handle missing flags in queries Linking Country flag images for display in forms Building a Customer query that joins Country flag data Setting up the form to display the corresponding country flag Binding the form to the new query as a record source Inserting and binding an image control for the flag Demonstrating automatic flag updates based on country selection |
||||||||||||||||
|
| |||
| Keywords: TechHelp Access multi-table update query from another table update from other table CurrentProject.Pat, 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 PermaLink Multi-Table Update in Microsoft Access |
