Working With County Names

So for our most recent assignment in UP505, I found that the data across years for individual counties were inconsistent in their naming. Some stated simply 'Santa Rosa' while other stated 'Santa Rosa County.

This method is confusing down the road when we create information from our data, and Excel will not combine the records efficiently.

Follow the steps attached to clean your county names before working with the data.

Start by going to create > Query Design.

Step 1: Adding Your Fields to Create Two New Tables

Access has a bug that will not allow you to clean/replace your county labels where there is an excess of 5000 records, so first I sought to just create two new data tables in the format I need so that I could work with smaller data sets.

We're going to first make a new table with all of the fields from the existing table, but only the records that we need to establish the commute from 'Subject County' to their job in 'Any County.' Start by making a new query, then...

• Input all of the fields I have included in the image, and replace your criteria for 'fips_res' with the county you're working with.
• Now under the design toolbar, select the icon that says 'make table' and name the table something descriptive like 'CommuteFromCounty" and choose okay to keep it in the current database.
• Then click run, and open the new table
• The new table should be just like your original but only show the records for every year from your subject county to the county the commuter works in.

Step 2: Clean Your Data

Now that you have the table, you can click the 'Home' toolbar and choose 'Replace'

• Set your replace prompt just like in the image attached, and select 'Replace all'
• Choose 'yes' to any prompt to clear all instances of 'county' from the names

Now that your data is clean, you can run the queries from class without having repeating names like 'Santa Clara' and 'Santa Clara County'