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.