Working With County Names

Intro: 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'

Comments

[seamster]

So, what is this for? Is this for family history/genealogy type work?

(Or is this more of a tutorial aimed at a specific group, like for your work or a school class or something?)

[milessthomas]

I probably should have just made this private, but yes this is a tutorial aimed at a specific group (a GIS modeling group). Data from the American Community Survey and other sources didn't match up, so this was meant as a way to derive clean data (county name) from the source data before importing for visualization in ArcGIS.

I suppose though that this would be useful for anyone looking to clean data sets in Access for the purposes of filtering tables, and creating new ones.

[seamster]

Ah, cool. I was pretty curious!

You certainly can make this private if you wanted. Then you just share the URL directly with people that need it, but it's fine if you leave it as is of course. If you're interested, send me a PM and I can help you out.