Lab 2: Microsoft Access

Introduction

This lab introduces Microsoft access as a database tool for use with ArcGIS.

Instructions

This is entirely a hands-on exercise. Follow the instructions below to import, query, and export data using Access. Before working with the lab data, copy it from the course website onto your flash drive.

Deliverables

Answer the questions marked in bold and produce the required outputs. Your lab document should be typed, well organized, and submitted according to the course “How To” guidelines.

Importing Data into Access

· Open up ArcMap and in the ArcCatalog window create a new blank personal geodatabase in a folder on your flash drive.

· Name this geodatabase, NewJerseySocial

· Add the NJ_CensusTracts shapefile to your map. Change the coordinate system of the data frame to the NJ State Plane 1983 meters.

· Export the dataset into the NewJerseySocial geodatabase, using the dataframe’s spatial reference information.

· Close ArcMap and open up Microsoft Access

· Open the NewJerseySocial geodatabase in Access

· You should see a variety of files in there, including the NJ_CensusTracts table.

· Now, you will want to add the ACS_11_5YR_NewJersey table into your database. Go to ‘External Data’ tab and import the excel table.

o During the import, set the primary key as the GeoID column (first column in the table)

o It will give you a warning that only the first 255 columns will be saved – that is fine for the purposes of this assignment.

Design a Query in Access

You are going to design a query (or maybe more than one) to create new tables that only include the data you want to map from the Census. There are many, many, fields in the table. Select a field that you are interested in mapping for NJ and use that as the basis for your query. You will create a few queries and tables so that you can join them for mapping in ArcGIS in the next step.

· First you will need to create a relationship between the two tables using the primary key (GeoID)

· Find the Database Tools menu and go to relationships

· Click on GeoID in one table and drag it over to GeoID in the second table. This should create a visible link between the two. Close the relationship, making sure to save it before closing.

· Next, use the query wizard to create a new query. Go to the Create menu and click on Query Wizard. This will help you walk through the steps.

· Create a simple query, and select the Table: ACS_11_5YR_NewJersey. The first table you will make will be for households. Make sure you include the following fields of data:

o GeoID, Geodisplay-label, HC01_VC03 (number of households), and HC01_VC20 (Average household size).

· Click on Finish and the table should appear on your screen.

· Change the columns names so that they are compatible to ArcGIS standards (refer back to the review lecture – no longer than 13 characters, no symbols other than underscore (_), etc.)

· Right-click on the table name in the Table of Contents (left side of the screen) and go to Design View

· Once you are in Design View, you should see a new menu appear – Design Tools

· Click on the Make Table icon and when the wizard appears, name the table ‘Households’ and click OK.

· Next, click on the Run icon and click ‘Yes’ when it ask about pasting rows into a new table. A new table, households, should appear in your list of tables.

· Create two more queries and tables, following the previous 7 steps.

o one for MaritalStatus (HC01_VC36, never married; HC01_VC37, now married; HC01_VC38, separated; HC01_VC39, widowed; HC01_VC40, divorced)

o one table for Education (HC01_VC79, high school enrollment; HC01_VC80, college or graduate school enrollment; HC01_VC87, high school graduates)

o Be sure that all columns names are ArcGIS compatible!

· Save the database and close Access

Create the Map(s)

You are going to create at three maps showing one characteristic from each of the three tables you created (Households, Marital Status, and Education). You can choose what three characteristics you want to map from those tables.

· Open up a new blank map in ArcMap and connect to the folder with your .mdb database.

· Add your NJ_CensusTracts feature class, as well as the new tables you created in Access to your map.

· Join each table to the NJ_CensusTracts feature class individually (using GeoID as the primary key field), and export each as a new feature class in the geodatabase. After doing this you should have 3 new feature classes: NJ_Households, NJ_MartialStatus, NJ_Education.

· Add all three new feature classes to your map.

· Create 3 maps of NJ using one characteristic from each of your new datasets. This can be one map with three data frames or three separate maps – your choice.

**TIP** since there are many census tracts in the state of NJ, try eliminating the outline color so that the maps are easier to read.

1. Export your map(s) as a jpg and insert into your lab document.