Field Value Substitutions
By Richard Rost
11 months ago
Fix Typos with Field Substitution, Update Queries In this tutorial, I will show you how to handle field value substitutions in Microsoft Access to manage and correct misspelled entries without restricting user input. We'll explore how to create a standardized list for data such as country names, while still allowing for freeform input. You'll learn how to set up a country table to correct common misspellings like "US" to "USA," and how to create an update query to automate these corrections. This video offers practical solutions for improving data consistency when users enter non-standard spellings in databases. Brandon from Torrance, California (a Platinum Member) asks: I have 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. PrerequisitesUp Next
Keywords TechHelp Access, field value substitutions, automatic value correction, common misspellings replacement, user data standardization, typo correction, customer table update, country names spell check, data entry error handling, advanced queries, update query, database relationships, freeform text standardization, expert level Access tutorial, action queries, database standardization techniques
Intro In this video, we'll talk about how to handle field value substitutions in Microsoft Access when users enter misspelled or localized versions of list items like country names. I'll show you how to set up a table of known misspellings and create an update query to automatically correct them, all while still allowing users to type freely instead of picking from a fixed list. We'll cover dealing with common spelling issues, regional differences, extra spaces, and update queries to keep your database standardized and accurate.Transcript 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.Quiz Q1. What is the purpose of field value substitutions as described in the video?
A. To force users to select from a predefined list of values
B. To allow users to type in their own values while correcting common errors
C. To create a list of all possible country names
D. To enhance database security by restricting data entry options
Q2. Why does the instructor prefer not to maintain a complete list of all possible country names in the database?
A. It is too time-consuming to update regularly
B. Countries never change their names
C. Users might type in localized or misspelled versions of their country names
D. All users spell country names correctly
Q3. How does the instructor suggest handling users' misspellings or variations of country names?
A. By blocking any input that doesn't match the allowed list
B. By updating a lookup table with known misspellings and automatically correcting them
C. By ignoring any input that doesn't match the allowed list
D. By sending users an error message to correct their input
Q4. What type of query is used in the video to correct known misspellings of country names?
A. An append query
B. A delete query
C. An update query
D. A make-table query
Q5. What does the instructor emphasize about using a table without an ID field for this substitution process?
A. It's common practice for all database tables
B. It's a rare exception for situations that don't require relationships
C. It's a recommended practice for all lookup tables
D. It's essential for maintaining data accuracy
Q6. After correcting known misspellings using the described method, what is suggested as the next step?
A. Deleting all entries that are still wrong
B. Finding and addressing unknown misspellings in the next class
C. Requesting users to re-enter their data
D. Locking the corrected entries to prevent future changes
Q7. What is the main reason for setting a default country value in the database, as the instructor mentions?
A. To handle cases where a user's country is unknown (null)
B. To avoid null entries appearing in user output
C. To prevent users from entering any country name
D. To enforce a single country entry for all users
Q8. In the context of the video, what does the instructor identify as an issue with certain special characters in country names?
A. They are always handled correctly by the database
B. They can appear as question marks in the database
C. They enhance the readability of the names
D. They do not affect data processing in any way
Answers: 1-B; 2-C; 3-B; 4-C; 5-B; 6-B; 7-A; 8-B
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.Summary Today's TechHelp tutorial from Access Learning Zone focuses on field value substitutions. I'm Richard Rost, your instructor. We all know the hassle of managing lists, whether they're of country names, products, or people's names. While it's great to have a list of allowed items, sometimes it's necessary to let users input their own entries. The challenge is dealing with incorrect entries, especially with things like country names, which are often misspelled or presented in a localized form.
For instance, maintaining a comprehensive list of all countries on my website was impractical, so I allowed users to input their country, leading to variations like 'Polska' instead of Poland. Today, I will show you how to handle such variations by taking a list of known and correctly spelled items and creating a system to replace common misspellings automatically. This technique isn't limited to just country names; you can apply it to any list you manage, like product names or client names.
Brandon from Torrance, California, reached out with a common issue: his customer table had a bunch of misspelled country names, and he wanted a way to correct them without forcing users to select from a predefined list. He expressed a desire to allow free input because maintaining an exhaustive list of all countries wasn't feasible for him either.
I've faced a similar problem. I needed data on sales by country, and while gathering the info, I noticed many variations for the United States alone - such as United States, USA, U.S.A., etc. While it is possible to create a standardized list, I still wanted users to be free to type their answers because country names can change. This method also proved useful in other scenarios, like point-of-sale systems, where users needed the flexibility to input custom entries or choose from a list.
This tutorial requires you to be comfortable with some advanced techniques, such as creating relationships and action queries. If you're not familiar with these concepts, I recommend watching some of my foundational videos first.
One example of a recent project involved setting a default value for country entries to avoid blanks, which I erroneously equated with unknown values. Previously, I left blank entries for the USA, leading to uncertainty about customer locations. To correct this, I now set a default value of 'USA' in the database.
For the demo, I'll use my free TechHelp template. Here, we've got a customer table that links to a country table where users often input varied spellings of country names like U.S., USA, and America. My goal is to standardize these entries for consistency in my queries and reports.
To manage substitutions, I create a simple table with the correctly spelled country name and its common misspellings. In this instance, I won't use an ID because this table serves only one purpose: mapping misspellings to standardized country names.
We'll turn this setup into a query that checks the customer table against the known misspellings and updates entries accordingly. This involves creating a relationship between misspellings in one table and country names in another. By running an update query, we correct these entries, ensuring consistent data across our records.
What about the unknown misspellings, those we haven't identified yet? Once you've corrected the known ones, the next step is to find and fix these unknown variations. We'll tackle this in tomorrow's class.
That's all for today's TechHelp tutorial. For a complete video guide with step-by-step instructions on everything we've discussed, visit my website at the link below. Live long and prosper, my friends.Topic List 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 |
