Learning is not attained by chance, it must
be sought for with ardor and diligence.
-Abigail Adams
Home   News   Courses   Tips   Templates   Forums   Help   Order   Contact   Logon  
List Cleaning Example
By Richard Rost   Richard Rost on Facebook Richard Rost on Twitter Richard Rost on LinkedIn Email Richard Rost   6/4/2020 5:17:12 PM

So I wanted to take some time today to figure out which countries most of my sales come from. The database nerd in me needed to do it right, so I created a nice query showing total sales per country as a percentage of my total sales, etc. It's all very pretty and nice. 

But... this let me realize that my database is full of garbage data in the COUNTRY field. On my main order form, I have "United States", "Canada", "United Kingdom", and then OTHER where I let people type in their own country names. Well... that data is a mess. When I set up the web site in 2004, I didn't think I'd have many customers from other countries. Well, 16 years later, it's a nightmare to sort out.

So, I started list cleaning manually, but that was a pain. I decided to create a table showing "known misspellings" for names. This would handle things like "NZ" for New Zealand. I counted about 10 different misspellings for "Australia" and that's from people who supposedly live there. LOL. According to the Royal Mail Guide to Preparing Mail, the Country field for UK citizens should be "United Kingdom", not the individual countries like England, Scotland, etc. So I used this tool to edit those.

As far as abbreviations and periods go, I decided to standardize on what Google Maps displays. So USVI becomes "U.S. Virgin Islands" but "St Lucia" and "St Vincent" don't get a period after the "St." I did make one exception: I decided to change "St Kitts & Nevis" to "St Kitts And Nevis" only because the programmer in me knows that & signs (along with ', ", <, >, and a few others) can cause problems in code, SQL, etc. As long as you're consistent, you're fine.

In any case, when you give the customer a text box to type things in freely, that will happen. I don't know how many people picked "Other" and then typed in "US of A." Ugh.

Once I had this table built, it was pretty easy to load it up with all the common misspellings, and then run a query on it. Problem solved, and now I can use it in the future to quickly clean the list OR even run it when orders are imported. Plus now I can generate a list of KNOWN countries for my database and my web site. Yeah, I could have found one online somewhere, but I always prefer generating my own data in cases like this. I've got customers from just about every country in the world.

Anyhow, I'm thinking of adding this form to the next Access Developer course because it's got a little bit of everything: SQL, VBA, weird string manipulation, etc. I think you'll like it.

BTW if you're curious, the image above shows my actual data for the past year (6/4/2019 to today). No surprise that 73% of my customers are from the US. The next three don't surprise me either (UK, CA, AU). Way to go Netherlands, Israel, Bonaire, Belgium, and Norway! Thanks. For small countries, you guys show your love. India, you need to step up your game. You're a huge country. Less than 1% - come on. LOL. Just kidding. Love you too.



Post Your Comments
Only students may post right now. Click here for more information on how you can set up an account. If you are a student, please LOG ON first.

Access - index
Excel - index
Word - index
Windows - index
PowerPoint - index
Photoshop - index
Visual Basic - index
ASP - index
Account Login
Online Theater
Lost Password
Free Upgrades
Insider Circle
Student Databases
Change Email
Latest News
New Releases
User Forums
Topic Glossary
Tips & Tricks
Search The Site
Waiting List
Production Schedule
Collapse Menus
Live Chat
Customer Support
WalkThru Tutorials
Consulting Services
Affiliate Program
Richard Rost
Free Lessons
Mailing List
Video Tutorials
MYOLP Memberships
Learning Connection
Idiot's Guide to Excel
Volume Discounts
Payment Info
Terms of Sale
Live Chat
General Info
Support Policy
Contact Form
Email Richard
Mailing Address
Phone Number
Fax Number
Course Survey
Facebook    Twitter

YouTube Channel    LinkedIn

Blog RSS Feed   
Keywords: list cleaning countries  PermaLink