Skip to content U.S. Department of Transportation/Federal Highway AdministrationU.S. Department of Transportation/Federal Highway Administration
Office of Planning, Environment, & Realty (HEP)

CTPP 2006-2010 Census Tract Flows Query Tutorial

High Resolution Version

This file includes census tract to census tract flows from the new CTPP 2006-2010, using the American Community Survey. The Microsoft Access file has a total of 4,156,426 records. Download the database file. (151 MB; requires Microsoft Access.)

The file includes all tract pairs which present flows from the CTPP 2006-2010 including Puerto Rico. This file only include one measure: total worker counts and its associated margins of error. FIPS codes are also provided for residence and workplace State, County and Census Tract.

This tutorial provides a guide on how to query the database.

Example: I want to get flows from all the tracts in Broward County, Florida (County FIPS =11, State FIPS = 12) to all tracts in Florida.

Please note: if you are looking for a Census Tract ID, for example Census Tract ID = 0201.00, you will have to provide number "20100" in the query as all Census Tract IDs are converted to numbers.

Step (1). Double click on the Microsoft Access file to open the database. Select the table named "Tract-flows" on the left panel. The database is open and the total records are 4,156,426.

Title: Figure 1 - Description: This screenshot shows the tract flows database in Microsoft Access format. Total records number is also noted.

Figure 1

Step (2). Go to the tool bar of Create tab and click on Query Design. A "show table" window will pop up. This will lead to a screen in Figure 2.

Title: Figure 2 - Description: This screenshot shows the first step of how to create a query design. The sub-window "Show Table" lists the selected database.

Figure 2

Step (3). Close the "Show Table" window. In the Field row, click on drop down arrow in each cell to add variables . You must add ALL variables from the tract-flows database as only selected variables will be included in the queried database. In the Criteria Row, enter the corresponding FIPS codes to filtrate the desired records. In our example, criteria include:

Click on "!" on the top left corner. The query of "flows from all the tracts in Broward County Florida to all tracts in Florida" will be executed.

Title: Figure 3 - Description: This screenshot shows the second step of query design. The "Field" shows variables to be shown in the output table and "criteria" defines query conditions applied to each field.

Figure 3

Step (4). The output can be saved by right clicking on the table tab "Query1", and then you will be asked to type the table name. Type "Broward County FL". A table index is automatically created On the left panel.

Title: Figure 4 - Description: After query, this operation saves the queried table into a separate file.

Figure 4

Step (5). You can export Access file into Excel by right clicking on table name on the left panel. However, users must be aware that the maximum records of 2010 Excel is 1,048,576. If your queried database is bigger than 1,048,576 records, a statistical software (SAS,SPSS and Access) is recommended to manipulate the data.

Title: Figure 5 - Description: Queried table can be exported into different data formats.

Figure 5

Updated: 3/25/2014
HEP Home Planning Environment Real Estate
Federal Highway Administration | 1200 New Jersey Avenue, SE | Washington, DC 20590 | 202-366-4000