Massachusetts Institute of Technology Department of Urban Studies and Planning 11.204: Planning, Communication & Digital Media Fall 2004 Lab 5: Using Access to Query Multiple Data Sets Assigned: Rec. 6 Due: Rec. 7 at 5PM I. Scenario This lab exercise is designed to help you learn more about MS Access, while enhancing your understanding of Central Square. So, you should not only keep an eye toward the functions packaged in Access, but also think about the data you are manipulating as you move through the following exercise. II. DirectionsA. Introduction to relational database concepts and MS Access 1. Give two examples of databases from daily life. 2. List at least two database vendors and products in addition to Microsoft's Access. 3. Identify one row, one field, and one cell of one of the databases you answered to the question A1 above. B. Exploring MS Access interface and objects 1. Launch MS Access XP and examine the interface. [Help] 2. Open the database file "Cambridge.mdb" located in the MIT Server\Data\. In the database window named "Cambridge" examine the different objects in MS Access. They are: tables, queries, forms, reports and so on. [Help] 3. In the Objects list, click "Tables." How many tables are there in the list? Double click on the table "Cam_geometry" to open it. Switch between design view and datasheet view to examine the table. Then, answer the following questions. How many records are there in the table? Which field is the primary key? What is the data type of the field "Area?" [Help] 4. Close the table "Cam_geometry" and go back to the database window. Click "Queries" in the left objects list and select the query "qryPopuDensity." Open it in the design view, and ask yourself the following questions. How many and what tables are involved in this query? What are their primary keys? How many fields are there in the query result? What is the expression for the last field, "PopuDense?" Can you explain the number "2589988.1005?" Think about the unit conversion between square mile and square meter. [Help] 5. Close the query "qryPopuDensity" to return to the database window. Open and examine the other two objects "Form" and "Report." There is one form in this database named "frmPopuDensity" and one report named "rptPopuDensity." They contain the same contents as the query result "qryPopuDensity," but in different formats. [Help] 6. Close all the windows you opened inside MS Access and exit the application. [Help] C. Create your own database1. Launch MS Access XP again. Create a new database. Save the database file to "H:\private\11.204\lab5\johndoe_lab5.mdb". Replace "johndoe" with your username. [Help] 2. Import from "the MIT Server\Data\" the data file "cambridge_geometry.xls" (which contains the areas and perimeters of all Census tracts in Cambridge) into the database. Follow the requirements below. [Help] z Use the data from worksheet "Geometry" z First row contains column headings z Store the data in a new table; name it as "Cam_Geometry" z Change the first field name to "TractID". Index it, and allow no duplicates. Choose this field as the primary key. 3. In the same way, import the files "MA_Gender.xls,""MA_HH.xls,""MA_Popu2k.xls," and "MA_Race.xls" according to following information. [Help]z Change the field "AreaKey" to "TractID." Index it and allow no duplicates. Choose this field as the primary key. z Import the four files as four individual new tables. Name them according to the Excel file names. z Name the columns as follows: P001001: Total Population (Name the column as "Popu2000") P015001: Household Units (Name the column as "HHUnits") P012002: Males P012026: Females P003003: White P003004: Black P003005: NatAmer P003006: Asian P003007: Pacific Note: Of the five tables, "Cam_Geometry" only has 29 records (the number of Census tracts in the City of Cambridge.). The other four tables contain data for Census tracts located throughout the Commonwealth of Massachusetts; each contains more than 1,300 records. D. Working with tables 1. To become familiar with tables, we ask you to modify the table "Cam_geometry." There are two errors in the original data source "Cambridge_geometry.xls." Modify the table you created in MS Access, not the original Excel file. [Help] z First, the area of Census tract "25017355000" should be "482491.81312" instead of "842491.81312." z Also, one record is missing. You should add the missing record, which is shown below. PERIMETER 25017354900 1285551.80508 5768.48054 STCNTYTR AREA Now that you've modified and added records to the table, we ask you to try sorting and filtering functions. 2. Sort the table "MA_Popu2k" to find the five most populated Census tracts in Massachusetts. [Help] --simple sorting 3. Filter the table "MA_HH" to see how many Census tracts in Massachusetts have more than 3,000 households? [Help] --simple filtering 4. Use the advanced filter tool to identify tracts in MA with the male population between 2,500 and 3,000 and the female population between 2,000 and 2,500. [Help] --advanced filtering, logical operator "AND". E. Select queries 1. Construct a select query that tells you how many Census tracts there are in the Commonwealth of Massachusetts in which there are more Black people than White people. Save this query as "qryE1." [Help]--simple query based on one table 2. Construct a select query that tells you how many Census tracts there are in the City of Cambridge in which there are more Black people than White people. Save this query as "qryE2." [Help]--query based on table join 3. Build a select query to calculate the population density (number of people per square mile) for each tract in the City of Cambridge. Sort the records by population density in descending order. Which tract has the greatest density? Save this query as "qryE3." [Help]--query based on table join, sort, calculation by using query, expression 4. Export the query result of "qryE3" into an Excel file, name it as "johndoe_lab5_qryE3.xls" (Replace "johndoe" with your username) at H:\private\11.204\lab5.[Help] --export a table or query result. III. Things to ponder In addition to your observations in the field, think about what the Census data on race, gender, income, etc. are revealing. In Access, you were able to relate these data tables to create a relational database and ask complex queries. Now that you've completed the lab exercise, you should take a moment to