Download Planning, Communication & Digital and more Slides Geometry in PDF only on Docsity!
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
Help Section
Exploring MS Access interface and objects
Start MS Access
To launch MS Access from the Start Menu choose Start > Programs > Microsoft > Microsoft Access.
Open An Existing Database
In menu File, choose open. In the pop up windows, browse to the folder " the MIT Server\Data," choose "Cambridge" and click "Open."
The main database window opens. In the left lane there is a list of objects; you can find Tables, Queries, Forms, Report and so on. Click each of them to see what is inside in each object category.
Examine Tables
In the left lane of the Objects list, click "Tables." All the tables in this database are listed on the right of the window. Double click on the table "Cam_geometry" to open it. The table Cam_Geometry" shows in the datasheet view. You can tell the total records number from the bottom status bar.
In the tool bar of the main window, click on , the view switch button to switch to the design view. In the upper table, all the fields names and data types are listed. There is a key symbol left to the field "TRACTID," indicating that it is the primary key. You can tell the datatype of the field "Area" from the column "Data Type" in the upper table.
Click the arrow on the bottom of the window, and the record will change accordingly.
Close the form to return to the database window. Click "Reports" in the objects list and select "rptPopuDensity." Double click to open it.
Close MS Access
From menu File, choose "Exit" to close MS Access.
Create your own database
Create a new database
Launch MS Access again and in the menu File, choose "New." In the right task panel, choose "Blank Database." In the new window titled, "File New Database," browse to "H:\private\11.204\lab5," use "yourusername_lab5.mdb" as the file name and click "Create."
The empty database file appears as follows,
Import data files
In the menu file, choose Get External Data > Import.
Browse to the folder "the MIT Server\Data." In the drop down list called "Files of type," choose "Microsoft Excel." Select the file "cambridge_geometry.xls" and click "Import." An data import wizard windows pops up.
Choose "Show Worksheets." Only one worksheet "Geometry" is displayed. Click Next.
Change to "Choose my own primary key." Select TractID as the primary key and click "Next."
Change the table name to "Cam_Geometry" and Click Finish. A message shows the table "Cambridge_geometry" has been imported.
In the same way, import the files "MA_gender.xls,""MA_HH.xls,""MA_Popu2k.xls" and "MA_Race.xls" following the directions above. Rename the columns as follows (the opportunity to do this appears on the fourth page of the wizard).
{ P001001: Total Population in 2000 (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
Notice that of the five tables, only "Cambridge_Geometry" has 30 records (the number of tracts in the City of Cambridge). The other four tables contain records for the Commonwealth of Massachusetts, therefore, each contains more than 1,300 records (the number of tracts in MA).
Working with tables
Modifying Tables
In the database window, click "Tables" from the left lane, select "Cam_Geometry" and double click to open it. In menu Edit, choose "Find..."
or press Ctrl+F.
Type "25017355000" in the "Find What" section. Choose "TractID" as the Look In field and press "Find Next." MS Access locates the record "25017355000."
Click on the cell and change the contents to "482491.81312."
A pencil icon appears to the left of the record to indicate the change. Press to move to the next record.
The pencil icon disappears, showing that the change has been written into the database.
Now click "add new record" button. In the TractID field, type "25017354900;" in the area field, type "1285551.80508;" in the perimeter
field, type "5768.48054." Click to confirm adding the new record.
Two errors in the original table have been corrected.
The filter results are shown in the table. You can observe the number of records on the bottom line of the table.
Advance Filtering
Go back to the database window and open the table "MA_Gender." In the menu Record, choose Filter>Advanced Filter/Sort.
In the first field, choose "Males" and enter ">2500 and <3000" as the criteria. In the second field, choose "females" and enter ">2000 and
<2500." Then click on the "Apply Filter" button.
Only two records meet these conditions. This example shows how to use the logical operation "AND."
Select queries
Simple Query
Go back to the database window, click on "Queries." From the right side of the window, click "Create queries in design view."
In the "Show Table" window, select "MA_Race," click Add then Close. The query design window looks like,
Drag TractID from the upper pane to the first field in the lower pane of the window and drag "*" to the second field.
Tick off the Show box in the TractID column and leave the box for MA_Race on.
Right click the Criteria cell in the TractID column. From the context menu, choose "Build."
In the expression builder window, type "[MA_Race]![Black] > [MA_Race]![White]" in the upper blank and press OK.
Now we are back to the query design view.
In the query design window, drag TractID in the Cam_Geometry table to the first field and "*" in the MA_Race table to the second field.
Tick off the Show box in the TractID column, but leave MA_Race* on.
Right click the Criteria cell in the TractID column. From the context menu, choose "Build."
In the expression builder window, type "[MA_Race]![Black] > [MA_Race]![White]" in the blank space and press OK.
Go back to the query design view.
In the tool bar, click the "Run" button to execute the query.
Only one Census tract in Cambridge meets these conditions. Here, we ran a query against two joined tables. We use the Cam_geometry table to identify which Census tracts are in the City of Cambridge. Save the query as "qryE2."
Population Density
Start a new query in design view from the database window. Add the two tables for the query -- "Cam_Geometry" and "MA_Popu2k."
Drag "TractID" in Cam_geometry table in the upper panel to the first field in the lower panel, "Area" in Cam_geometry table to the second field and "Popu2000" in MA_Popu2k to the third field.
Add the fourth field by right clicking the cell and choosing "Build" from the context menu.
In the expression builder window, type "PopuDensity: [MA_Popu2k]![Popu2000]/[Cam_Geometry]![AREA]*2589988.100559" in the upper blank and press OK. Tick on the show box in this field.
In the tool bar, click the "Run" button to execute the query.
"johndoe" with your username.) and click "Export All." You can check the results by opening the exported file in MS Excel.
Created July 2002 by Jinhua Zhao. Updated August 2002 by Lorlene Hoyt. Updated October 2002 by Jinhua Zhao. Updated October 2002 by Lorlene Hoyt. Updated July 2004 by Lorlene Hoyt.
