Joining Census American Community Survey data in ArcGIS

[Guy905]

I'm just having a heck of a time joining the ACS 2005-2005 data to Census tract shapefiles. I'm using the Summary File Data Retrieval Tool, copying (copy or move feature) the Census "geographic" and "estimates" data to a new spreadsheet, importing into Access 2007, then both converting to Access 2002-2003 and exporting as dBASE. (I'm using ArcGIS 9.2 do have to do this).

I've tried converting the Place of Birth for the Foreign Born into numbers in Excel before importing into Access and also trying to clean the data by pasting and then copying in Word Pad. The best it will do is to display the numbers under Labels in ArcGIS. It won't treat them as numbers in either Symbology or in queries.

Maybe it's how the data is treated in Access? The 2010 Redistricting data works great, but then the Census Bureau has an import tool (into Access) for that.

Thanks in advance!

[kcarney]

Joining Census data to shapefiles can be painful. Quick question: what problem are you trying to solve (i.e. buffer analysis or make heat maps or...)? And for what geographies (i.e. Census tracts in Travis County Texas)?

[Guy905]

I am trying to show the number of people from a selected country by Census tract. I want to use Graduated Colors from Quantities in Symbology. So if 0-20 people from Poland were in one tract, it might show up as a light yellow. If 40-80 were in another, it might show up as dark brown.

The problem is that Symbology and Definition Queries don't recognize the numbers. Thanks.

[kpf]

I can think of two ways for you to show your data with graduated colors, both involve creating new fields.

The first involves converting your data into a numeric format. Create a new field (either integer type would work), let’s call it Pop_Num. Then, use the field calculator to set the Pop_Num field equal to your original field. Done; you now have numbers to work with. The second way would be to create a new field, let’s call it Class (again, integer). Select the records you want in your first class (i.e. 0-20 people). Next, use the field calculator to assign a Class field value of 1 for those records. Continue to do this until all of your records are classified.

With either method, you should get the results you want after you play around with the layer’s symbology.

Hope that helps, and good luck!

[Guy905]

I haven't used the field calculator much so maybe it's time to learn. Thanks for an approach that I wouldn't have though of. I'm definitely going to look into this.

Making slow progress. "Cleaning" the numbers data by pasting into Notepad and then copying back into Excel helps, but then you don't clean the LOGRECNO because it causes errors when importing into Access. You don't need LOGRECNO in ArcGIS so it's fine not to have it seen as a number...