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.Examining Queries to switch to design view. Close the table "Cambride_geometry" and go back to the database window. Click "Queries" in the left lane and select the query "qryPopuDensity." Double click it to open it in the datasheet view and click on There are two tables shown in the upper part of the window, which are linked by a line. The field names in bold font are the primary keys for each table. In the lower part, four fields are listed and all four boxes in the "Show" row are ticked on. Right click on the PopuDense field, in the pop up context menu, choose "Build... ." The expression builder window appears. In the top left of the window, we can see the expression for the field "PopuDense" is "PopuDense: [population]/[area]*2589988.100559." The number "2589988.100559" is used to convert population per square meter to population per square mile. Examining Forms and Reports Close the query, "qryPopuDensity," to return to the database window. Click "Forms" in the left lane and select the form named "frmPopuDensity." Double click to open it.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.The first row of this table contains the fields names so keep the box ticked on and click Next. Choose "Store the data in a new table" and click Next. Change the name of the first field from "STCNTYTR" to "TractID." In the drop down list called"Indexed" choose "Yes (No Duplicates)." Leave the other field unchanged and 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.Simple Sorting the menu Record, choose Sort> Sort Descending (or hit ). The table should look like this, Go back to the database window, double click on the table called "MA_Popu2k" to open it. Click on the column "Popu2000" to select it. In The first five records are the most populous Census tracts in Massachusetts. Filtering Tables Open the table "MA_HH." In menu Record, choose Filter>Advanced Filter/Sort. .In the first field, choose "HHUnits" and in the criteria row, type ">3000." Then click on the "Apply Filter" buttonThe 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