Field Value Substitutions 2
By Richard Rost
7 months ago
Fix Typos with Field Substitution, Update Queries Part 2 In this Microsoft Access tutorial, we will learn how to manage field value substitutions by identifying and correcting unknown entries in a table of countries. We'll build on concepts from part one, focusing on creating an update query to swap misspelled entries and identifying countries not recognized in our database. You'll learn how to link customer data to a country table using left joins to retrieve mismatched entries, and how to refine these entries to ensure accurate data. This tutorial is an essential guide for maintaining clean, validated data entries in your Microsoft Access database. This is part 2. PrerequisitesRecommended CoursesUp Next
Keywords TechHelp Access, field value substitutions, misspelled entries, update query, unknown country table, query design, data validation, inner join, outer join, left join, country misspelling fix, customer data cleanup, automate corrections, country table update, handling misspellings, database maintenance, AccessLearningZone.com
Transcript
Welcome to another TechHelp video brought to you by AccessLearningZone.com. I'm your instructor Richard Rost. Today is part two of my field value substitutions video series. What does that mean? Well, you've got a list of known items, and your users type stuff in wrong, so we're gonna swap these for these. Could be countries, could be products, could be whatever. This is part two, so if you haven't watched part one yet, go watch that first. You'll find a link down below and then come on back.
Alright, so yesterday in part one we set up a known country table where we got a list of countries and how people usually misspell them. We made an update query so we could take all the known substitutions and substitute them out, but now we've got to find the ones that we don't know are spelled wrong. It's a little more difficult.
So this time create Query Design and again turn this thing off. I wish there was an option to not have this thing pop up because I normally add my table over here anyway. Let's bring in our customer table, drop that there, and we're also gonna bring in our country table again. This time, I want to find countries that are typed in here. So bring that down. This should be is not null.
And I want to see the countries that don't exist in this country list, not as a misspelling, just in general not a country, because we already eliminated the ones that are known misspellings. This time I'm gonna link this guy with that. Now, if I do it this way, let me bring this down so you can see. If I bring that down with this here is an inner join, it's gonna try to match these together. It's gonna show only the records where these are the same, which at this point is only gonna show you the ones where you know what the country is.
I don't want that though. I want to see the ones that are over here that are not the same. So I'm gonna turn this into an outer join, a left join specifically, so I can see all the records from customer T and only the records from country T where the join fields are equal. In other words, show me all of these whether or not there's a match over here, and if there is a match, show me the match.
So now when I run this, I'm gonna see all of these, and if there is a match, it'll join them up. But I don't want to see all the ones I know about already so I want this one to be null, in other words, I don't have a match on this side so we come down here now and we say is null. Now I get a list of all the items that are in this table that I don't have a match for in the known country table.
I didn't put France in there, right? America, America, America, right? I didn't put UK or Canada in there. So now I have this stuff. This is my unknown country Q so let's call this country unknown Q. Okay, and now I could take this data and now it's up to you to figure out what this stuff is, which ones of these are right, which ones of these are misspellings?
Now we could see there are some valid countries that we didn't add in here and some misspellings we got to add. So go back to your country table and put the ones in here that show up in the other one. So you got France, well, that's a valid spelling, I'm just gonna leave that as is. There's no misspell, I mean, you could put a misspelling in there if you know. Like we have over here, we have French, so if there is a misspelling feel free to add it, so French. What else we got? We got USA like that. Oh, I'm in the wrong column again. Should be USA over here and then USA spelled like that.
America, we got United States. We got America, that's only for the Southern states. I'm just kidding. We got Canada, it's spelled Canada, right? So C-A-N-A-D. UK is valid. You're gonna see stuff like this. I can almost always find one good misspelling, but you don't have to put a misspelling in there for it. In my table, for example, also some people type in Scotland. Scotland like Kevin, one of my moderators, he likes to have Scotland under his record. So I had to put a change in my database just for him because technically the country is the United Kingdom, but that's fine. He wants Scotland and he wants his country's flag, Scottish flag. No problem, Kevin, whatever you need, I got you.
And I think that's all. I'm right. Okay, so now we're gonna save the country table, close that.
Alright now if I rerun my country unknown Q, alright now I'm just seeing these, but that's because I didn't run my fixum update query. Let's fix them, double-click. Now I have all my warnings turned off. I should have mentioned this in the last video, I think in the update query video that I told you is a prerequisite for the last one, I'm talking about that. I turn all my warnings off because I don't want to see them. But now if I run this, oh look at that, that query is now empty because the database now has all corrected countries in it.
We come over here, you'll see now my update query fixed them all. Pretty cool, huh?
So now if a customer comes in here and types in something like Strill, right? First thing you're gonna do, run your country misspelled Q, it'll fix any ones that it knows about. Then you can run your country unknown Q and oh, I think that was in there, wasn't it? Yeah, put that in there. Yep, Australia is in there, should have fixed it. They fixed it, there we go.
If they do type something in like Austria and then on another one they type in USA X or whatever, right? Well, run your country misspelled first, that'll fix anybody that it knows about. Open this up, and it will show you all the ones that you might need to add to your table. In this particular case in my country table I'm gonna add Austria, doesn't need a misspelling, and then USA, USA X. Sometimes you're just gonna want to go through and just fix these, you know, one at a time. That's why you don't have to add them all to the table. But if it's something that you see on a regular basis and you want to make sure this is automated, you know what that is, now when I run it, it's empty and it should be fixed. There we go.
Now that brings up the next thing. This is good for fixing your database, you know, if you want to go through once a month and do it, or if you import lots of records you can do it with that. But what about on a day-to-day basis as people are working right in here? How do we fix that? We'll talk about that in tomorrow's video. So tune into tomorrow's, same bat time, same bat channel, or if you're a member again, you can watch it right now because I'm gonna keep recording today.
That is gonna be your TechHelp video for today. Hope you learned something. Live long and prosper my friends. I'll see you tomorrow for part three.
TOPICS:
Field value substitution using update queries
Creating a query to find unknown country values
Joining customer and country tables
Changing from inner join to left join
Filtering records with null values
Identifying unknown/misspelled countries
Handling valid and misspelled country entries
Running the update query to fix country data
Maintaining an automated correction process
Preparing for part three: real-time data correction
COMMERCIAL:
In today's video, we're continuing with part two of our field value substitutions series. You will learn how to identify and correct unknown misspellings in your country field using queries. First, we'll create a query to find countries that aren't matched in our known country table. Next, we'll set up an outer join to display unmatched entries, allowing us to identify misspellings or completely unknown entries. We'll provide practical steps to update and correct these entries in your database for ongoing accuracy. 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 main objective of part two in the field value substitutions video series?
A. To create a new customer table
B. To identify and correct unknown misspelled country names
C. To delete mismatched records in the database
D. To design a new table for storing product details
Q2. What is the purpose of using a left join in the query design discussed in the video?
A. To show only records from the customer table
B. To display records only from the country table
C. To see all records from the customer table and matching ones from the country table
D. To combine duplicate records from both tables
Q3. What is the function of the "is null" condition in the context of the video?
A. To find matching records in both tables
B. To list known misspellings of countries
C. To display records without matches in the known country table
D. To exclude all valid country names from the query
Q4. Why does the instructor suggest turning off warnings while running the update query?
A. Because warnings slow down the query
B. The warnings are irrelevant for this process, according to the instructor
C. It helps to test different scenarios without interruptions
D. Warnings can alter the query results
Q5. What should you do if you encounter a frequently misspelled country name according to the instructor?
A. Delete the misspelled entries immediately
B. Add the correct and misspelled names to your table for automatic updating
C. Ignore it as an irrelevant error
D. Manually correct each misspelling every time
Q6. What's the instructor's suggested method for dealing with country name issues during daily operations?
A. Correct them only during monthly database maintenance
B. Create a separate table for daily corrections
C. Address the corrections in a future video
D. Implement a daily automated script to handle them
Q7. What type of join is suggested to identify countries that are not in the known list but appear in the data?
A. Inner join
B. Equi-join
C. Right join
D. Left join
Q8. In the video, what temporary solution is provided for dealing with new and misspelled countries?
A. Running the queries manually each time an error is found
B. Installing new software to handle misspellings
C. Deleting the entire customer data and starting fresh
D. Automatically updating the database without user intervention
Answers: 1-B; 2-C; 3-C; 4-B; 5-B; 6-C; 7-D; 8-A
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 the second part of our series on field value substitutions. I'm your instructor, Richard Rost. We're addressing the issue of users inputting incorrect data, particularly when it comes to lists of known items like countries or products. If you haven't checked out part one of this series, I recommend watching it first to get the full picture of what we'll tackle here.
In the first part, we set up a table of known countries and common misspellings. We also created an update query to replace these misspellings with the correct entries. Now, the challenge is identifying and correcting incorrect entries we don't yet recognize as misspellings.
To start, we will create a query design to spot entries that don't exist in our current known country list, not due to misspellings, but because they are simply not a valid country name. We will use this approach to look for entries in the customer table that don't have a corresponding entry in the country table. To achieve this, we'll set up a query with a left join, which will show all records from the customer table alongside those from the country table where the fields match. We aim to see the entries where there isn't a match, indicating a potential erroneous entry.
After running the query, you'll see a list of entries from the customer table without any match in the known country table. At this point, it's up to you to differentiate between valid country names not in your current list and common misspellings to update. Enter these valid countries or correct misspellings accordingly in your country table.
Once adjustments are made, save your country table and rerun your query. If your data is updated correctly, it should no longer display entries needing correction. This process ensures your database reflects accurate, standardized country names.
To maintain accuracy, run this check periodically, especially after importing bulk data. Be aware, we'll discuss fixing errors in real-time as data is entered in the following tutorial. So, check back for part three, where we'll address day-to-day data corrections.
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 List
Field value substitution using update queries
Creating a query to find unknown country values
Joining customer and country tables
Changing from inner join to left join
Filtering records with null values
Identifying unknown/misspelled countries
Handling valid and misspelled country entries
Running the update query to fix country data
Maintaining an automated correction process
Preparing for part three: real-time data correction
|
