Creating a 2010 Census Database


So you’re interested in the 2010 Census results.  Well they’re publicly available at http://www2.census.gov/census_2010/03-Demographic_Profile/ and with a little bit of work, you can have yourself a fully stocked Access Database with complete 2010 Census data.

Here’s a simple step-by-step using Microsoft Office – Access 2010 to create a database and import the data for the state( s ) you want to collect.  Later I’ll show you how to turn the resulting Access 2010 file into a SQL Database for SQL 2008r2.

Download the “DPSF2010_Access.accdb” file from the url above.  Also download the various .zip files under the states your interested in.  Then open up the Access file and perform the following steps as described in their documentation…

SNAGHTML1c746f7

SNAGHTML1c7c20f

We’ll first import the demographics data… so select the non-“geo” file.

SNAGHTML1c80833

SNAGHTML1c84a61

SNAGHTML1c88b95

SNAGHTML1c8bcd2

Select the “Data Part…” specification since this relates to the Demographics file.  When you repeat this wizard for the Population (geo) file, you’ll need to choose the other “GeoHeader…” option on this screen.

SNAGHTML1c9071a

The specs will automatically change to the correct format.

SNAGHTML1c931e2

SNAGHTML1c95e10

SNAGHTML1c98b37

SNAGHTML1c9adf3

This step is important… so specify the correct primary key.

SNAGHTML1ca1fa9

Name your table… I’m importing everything, so I’ll name it the state and attach Demo onto the end and Pop on the end of the geo file.  This will give me tons of data in two different tables for each state.

SNAGHTML1ca62b1

 

Have fun with data… if you have any questions or comments, please add it below!

Advertisements

11 thoughts on “Creating a 2010 Census Database

  1. Pingback: Migrating Access Data to SQL 2008r2 « DanielHeth's Blog
  2. If you’d like an export of my SQL 2008r2 database for the 2010 Census, let me know by posting a comment. I’ll respond with a downloadable link.

  3. This is pretty awesome; I’m wondering if there’s a similarly easy way to access data at the block level?

  4. Hey Daniel, I’d love to have an export of your SQL 2008r2 database for the 2010 Census! Any way you’d be willing to share?

  5. I’d love to have an export of your SQL 2008r2 database. More specifically, I’m looking for data for California but I’ll take whatever you have.

  6. Unfortunately my sql server died a horrible death, taking everything with it. That’s what I get for working more on projects and less on a backup procedure.
    I only have the two items that I’ve linked to in other comments.

  7. Well thank you anyway. I also noticed your walk-through of how to to migrate from Access to SQL Server so I’ll try that when I get a moment. I’m doing a project for an econometrics class and the data wasn’t very friendly from the .gov so I hope that SQL can give me what I need.

  8. Hi Daniel,
    Are the instructions above to download the fully stocked Access Database with complete 2010 Census data?
    That means I will have all data for SF1 files? Does this include data for foreign born and languages spoken at home too?
    Thanks
    Elaine

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s