Microsoft Access Update Query: Values from Another Table to Normalize a Field. Plus add 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?

BONUS FOR MEMBERS:
Silver Members and up get access to an Extended Cut of this video. Members 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.

MEMBERS VIDEO:

BECOME A MEMBER:

LEARN MORE:

LINKS:

COURSES:

FREE TEMPLATE DOWNLOADS:

ADDITIONAL RESOURCES:

KEYWORDS:
microsoft 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

QUESTIONS:
Please feel free to post your questions or comments below. Thanks.

[raphiel]

Richard, I am an avid access user, it is now my daily routine to watch your tech help videos before I go to work in the morning, your knowledge and help has made my work easier and more productive, thanks a million

[Jojosmith]

all thumbs up for the greatest teacher Richard as always. thanks a lot Richard for teaching us the most valuable lessons

[Cindanela]

Thank you, part of this was exactly what I needed, I had values in another table and wanted to replace null value with value 1 of that table and I couldn't use search and replace.

[stanTrX]

Hello and thanks from Turkey 🇹🇷 Richard :)) 16:41

[rabidfollower]

With experience, a user will learn to design tables, control user entries, etc. with normalization in mind, because it could be tough to normalize a table after it has been used for a while. If it involves changing field names (such as changing "Country" to "CountryID" in your video), it could affect existing queries and VBA code that use those old names. Normalization usually has to be decided upon early on. My rule of thumb is if a field has a lot of repeating values, that field probably needs to be normalized. But sometimes it doesn't. Sometimes it is useful to NOT normalize. For instance, if a customer re-locates, and you want to keep her old address in past invoices for record-keeping, the only way is to store customer addresses as non-normalized fields in the invoices. Normalization is a design choice, not a compulsory one. Like many things, database design is an art, not a science.

[Stache]

When scrubbing a field, use a totals query on the field alone, after each query pass on another query to fix undesirable data, run the totals query again. (I'm not sure if a totals query is case sensitive, Richard will have to chime in)

[Australia-TownsvilleVe-qftg]

Hi Mate, love your videos, they are super helpful. I have say two tables member 1 & 2, within these tables I have a first name and last name fields. Member 1 is the primary source for data entry, how can I enter a members name in Member 1 and have it automatically entered on Member 2 Member 1 & 2 Tables are also related to Member 1 & 2 forms

[cjlion]

thanks for replying, you're welcome. I think I was asking about append queries now that I have watched more of your videos. I would like to add a non-normalized row of entries into a junction table, like a transform. from a click/command I want to normalize the row/record into a junction, just as you have described, i think iwant to collect a rows values and insert into my 1tomanyto1, just wanted you to point me ito the right video

[cjlion]

your videos are great! very helpful, and I have watched alot of them to see how 6ou have improved over time, nice job. Do you have a video that shows how to do this from the attributes of many fields from one table, to a normalized list for records in a junction? A video on the concept/link where several quantity values in a row on a subform append to the junction table

[rabidfollower]

I always try to use existing, real-world data for normalization whenever possible. E.g. state codes: AL, GA, NY, PA, OH, etc. Country codes: USA, MEX, CAN, etc. Two-letter country codes: US, MX, CA, etc. I prefer these to any autonumber or custom counter as the primary index. These real-world data are already unique, because they are designed for computerization already.

[ledahbernardi]

Let me ask something about the extended Cuts. If I become a member, here in Youtube, can I have the access for the Extended Cuts? Tks

[gonefishing]

Quick question, and I know this an older video. But on the FlagFullPath. Couldn't you have said FlagFullPath: [CurrentProject].[Path] & "\" & [ImagesDir] & [Flag] & .".jpg" . [ImagesDir] being an extra field in the CountryT that is the subdirectory in the projects folder where the images are stored. That way if I move the database and the image directory to a new home I don't have to spend days changing codes. By the way while mentioning moving to a new home, the website seems much much faster in it's new home.

[rajashreenat]

I love your videos as I am a self taught access student trying to improve my DB everyday to have it run for my business. Can you help me with this issue? I have three tables connected by foreign keys. There is only one relation from table A to B and Table B to C. I never connected A to C and now I have to do it for a query and a report. I never put foreign key in A or C to link them. Now I have about 1000 records and I am not sure how to connect them. Do I make sense? If so how do update Table A to put the foreign key to connect with Table C. Your input will be invaluable. Thanks

[tma]

Thank you for the video! If the user creates the value and that value is later used in another form through combo box, are we able to utilize that data through a query without the user viewing the identifier field? I am currently having an issue with the identifier field displaying instead of the name when brought up in a list box, continuous form or search. Thank you for any help!

[rabidfollower]

Do you have any videos (and/or opinions) on variable naming conventions, like Reddick convention and others? You don't seem to use it in your videos, and neither have I in my Access work. But it seems useful in certain situations. If you see a VBA code such as TotalAmt = Units * UnitPrice * Discount, you don't know if these are global variables, local variables, global constants, integers, real numbers (single, double), etc. A naming convention would differentiate them: e.g. sngTotalAmt = intUnits * sngUnitPrice * gsngDiscount. I imagine this is like adding comments in your code to make it more readable to other people. I've never used it because it makes the code look messy as you see.

[Murshidtv]

Hello sir. I have forms how i can append the record to table through query. When i run query all data append. I just want one the deaired row or record to be appended using checkbox. What should i write in criteria of append query?

[CB]

After adding the flag table to your menu you switched from customer table to customer query now your menu was based on the customer aquarium before it was based on the customer table. Doesn’t that change things?

[emilygould]

Hello, I am new to Access. I use Access to run mileage and maintenance reports for our company. Do you happen to know how I can update the mileage rate? TIA