Welcome to another TechHelp video brought to you by Access Learning Zone dot com. I'm your instructor Richard Rost. Today we're gonna talk about field value substitutions. What does that mean? Well, you've got a list of stuff. It could be country names, state names, products, people's names, whatever. Any list of stuff. And that's the list of allowed stuff. But you still want to allow your users to type in their own stuff.

A lot of times users type in things wrong. For example, when people place orders on my website, I didn't want to maintain an entire list of all the countries in the world. I want to be able to allow people to type in whatever country they want. But people misspell their own country names or they have localized versions of it. For example, on the screen here, you can see Polska. Some people from Poland type that in.

So this is a video where I'm gonna show you how to take a list of known items. We're gonna do countries and the spellings that you want. And we'll be able to look at all of the common misspellings and we'll just automatically replace them. But again, this works with pretty much anything. You can do it with names, you can do it with products, you can do it. You name it.

So today's question comes from Brandon in Torrance, California, one of my platinum members. He says, "I've got a bunch of country names spelled wrong in my customer table. Is there an easy way to fix them without forcing users to choose from a set list? I still want people to be able to type in whatever they want since I don't want to keep a complete list of all the countries."

Yes, I went through the exact same thing a few months ago with my database. I just wanted to do a simple query to see sales by country. Which countries do I get the most sales from? Of course, United States was number one. I think Canada was number two, UK was number three, and so on. But at that point, I noticed how many people type in weird different things for their countries. Even people from the USA type in a bunch of different stuff. Right? United States, USA, U.S.A., and so on.

So I decided to basically put together a standardized listing. But I still want people to be able to type in what they want because country names do change from time to time. And I wasn't to the point where I wanted to force people to pick from a list. So this is an option.

And I know country names are something you could just put together a big long list and make everybody pick from a list. But sometimes I've built other databases like POS systems where the client wanted the user to be able to pick from a list of products but still wanted to be able to freely edit it and put freeform text in there for either walk-ins or custom jobs or whatever. So even though the countries might not apply to you, you might find some other use for this stuff.

Now, some things you should know before we get started. This is an expert level video. What does that mean? Well, it's beyond the basics. You should know all the basics. It's a little more advanced. We're gonna be working with some relationships and some action queries. So let me give you a list of videos you should watch first.

First off, if you've been following me for any length of time and you know my TechHelp free template, go watch this video. Last week, I put this together where I changed it so that the default country in my database is USA. The reason being is because I used to just leave USA blank. But then I realized recently that blank means I don't know what it is, and I had people in the table where I literally did not know what their country was. So that is what null is for. So I really should have had a default value in here. So I changed it to USA. Go watch this for information on how I did that.

You will need to know some relationships. So go watch this video if you're not comfortable with relationships yet. You should know what an outer join is. If not, go watch this one. You should know how to create an update query. If you need any of this stuff, go watch these videos. They're free. They're on my YouTube channel. They're on my website. Watch them and come on back.

Here I am in the TechHelp free template. This is a free database you can get off my website if you want to. In here, I got a customer table. And the customer table has a country table. Now, let's say you get some people typing in some weird stuff. Right? You got U.S., you got USA, you got America, you got United States, you got America. You got all - I see everything, trust me. Here are some of the substitutions in my own table.

Sometimes, for example, you get regional spellings like that. So the person that lives there wants to spell it that way, and that's fine. But I want to correct it in my database to America, and I use the version. Sometimes it's just a matter of ampersands. I like to have the actual whole word "and" in my database. Again, it's just a matter of standardization. So if I do a query and I want to see how many sales I have from a specific country, it knows it by name.

Sometimes, like in my database, I don't get the non-standard USA characters. The little guys with the umlauts over them and all those things, they come in as question marks. So I'm happy to change that over. Some things like here, Deutschland again. All right, Czech Republic. There's some different spellings for that. A lot of it is just a matter of people misspelling. Australia was really bad. I actually fixed Australia many months ago before I put this table together, and I did them all by hand. There are so many different variations on Australia, and they were all misspellings. So as far as I'm aware, there's only one way to spell Australia. I know other countries, there's lots of different ways to spell them.

Anyway, so this is the table that I use in my database. This is all just what people have typed in for me. Sometimes there's extra spaces, they can do it too. Right? You get a little extra spaces in places. There wasn't one, there's some other ones I saw where there's extra spaces, but anyways.

Let's type in a couple other different ones. Let's say someone typed in Canada wrong. Like "Canad" like that. We got UK or we got a French. Right, so this is different misspellings in here now. Again, I want to emphasize this is for something where you don't want to necessarily standardize it in a table where the user has to pick from a list of countries and then you have to maintain that table. It's an option. Again, I'm just telling you all the different Lego pieces that are available. You can put them together however you want. If you want to make a standardized country table, that's up to you.

Now I'm gonna set up a simple table that has the country name in it the way I want it spelled in my database and the known misspelling. There's lots of different ways to do this. This is how I'm doing mine. Okay. I'm gonna basically call this the country T and it's gonna be the country and the misspelling. Now this is one of those rare instances where I'm not gonna bother to put an ID in this field because I'm only using it for this one purpose. It's not gonna have any relationships anywhere else except to itself maybe. And the relationship that I'm gonna have is gonna be based on the country name to try to match it up with what's in the customer table.

So this is the rare instance where I don't think I need an ID. You can add one if you want to. You think you'll need it later on. I'm not gonna. So country T or yeah, country T. Primary key. No, I'm gonna say no this time. It's very rare that I do this, but I am doing it today.

Let's say you've got some misspelling that you know, like USA and just US. We don't know the other ones yet. Let's say we've got Australia. Oh no, this is the right one. So this should be Australia. When I was going through that list and fixing them by hand, I sometimes couldn't tell if it was Austria or Australia. So if there was an L anywhere in it, I assumed Australia. We'll just put those in there for now. There's just those two. So save and close that.

Now we're gonna make a query to match those up with the ones that are in the customer table. Match up our known misspellings. We're gonna create a query. I don't need this and I don't need that, we can shut those down. I'm gonna bring in the customer table first and the country that's in here. We can shorten that up a little bit by saying is not null. You can deal with nulls on a whole separate one. You know, if you don't have a country then that turns into null. You gotta just get the country right. Then we're gonna bring in the country table over here and we're gonna relate the country over here with the misspelling over there. I want to find out which ones of these are misspelled. This is why I'm making that weird relationship. I'm bringing over the misspelling if you want to see it.

If I run this now, this just shows me the countries in this table that have known misspellings. There are still some other ones, but these are just the known ones. Now what I can do is I can turn this into an update query and this will automatically fix this guy to be that guy. This is how it's supposed to be spelled. So let's turn this into an update query and I'm gonna update this to, now since I got two tables that have the name Country in it, I have to use its full name. I want to update this to CountryT.Country. Just like that.

I'm gonna save this as my Country Misspelled Q. My Country Misspelled Q. Now when I run this, that should fix that misspelling. So if I go and look in my customer table, it should have fixed just that one. Where is it? Right there. It fixed the US. It was just US before, right, and it fixed the US. That's the only one that I had in my known country table. US got changed to USA. I didn't have any Australia.

That is the known misspellings. What about the unknown misspellings? The unknown misspellings are a little harder to find. Those are the ones we don't know are spelled wrong. After you update your table with this, so we fixed all the ones we know about, now we can find the ones we don't know about, and we will do that in tomorrow's class.

So tune in tomorrow, same bat time, same bat channel. Or if you're a member, you can watch it now because members at any level can watch stuff as soon as it's finished. I'm gonna work on it right now and record it, and upload it, and you'll be able to watch it. Otherwise, come back tomorrow. Unless you're already watching this in the future, in which case it's probably already online. Today is Monday, April 14th, 2025, so come back tomorrow.

That's gonna do it for your TechHelp video for today. I hope you learned something. Live long and prosper, my friends. I'll see you tomorrow.

TOPICS:
Field value substitutions  
Handling misspelled country names  
Using a standardized country list  
Creating a table for known misspellings  
Setting up a query to match misspellings  
Updating records with corrected spellings  
Creating an update query in Access  
Dealing with regional spellings and ampersands  
Managing non-standard characters in inputs

COMMERCIAL:
In today's video, we're discussing field value substitutions. We'll explore how to handle when users enter misspelled or localized versions of list items, like country names. You've got a list of allowed stuff—countries, state names, products, etc., but we still want users to type freely. You'll learn how to automatically replace common misspellings using a simple query setup. We'll focus on matching and standardizing these entries without enforcing a strict drop-down list, perfect for when you want flexibility with user-input data. If you've ever struggled with messy databases filled with inconsistent entries, this one's for you. You'll find the complete video on my YouTube channel and on my website at the link shown. Live long and prosper my friends.