CSE 100 G - Access Lab
Rebecca Miller-Webster
Januaray 2006



1. Download the US News and World Report College Ranking Info
    It is located at: http://www.cs.wustl.edu/~loui/100s05/
    File Name: 2004USNEWS.xls 
    
    Now open the file.  
    Find the schools with a peer assessment score above 4.5.
    How did you do it?

    Now find the schools with a Graduation and Retention Rate above 20.

    What is you wanted to find the schools with a peer assessment score above 4.5 AND a Graudation Rate above 20.
    Try it.  
    hint1: you have to create a function.
    hint2: you can you use the same comparision operators (>, <, =, <=, <> (not equal) as in math
    hint3: for boolean (true or false) statements you use AND, OR, NOT

    It's hard isn't it?

    But there's an easier way ...  a database!

2.  Open Microsoft Access (Start -> Programs -> Productivity -> Microsoft Office -> Access)

    Create a new database (after you say new database, click "Blank Database" on the left.)

    Click on "Create Table in Design View"

    Databases are made up of tables with rows and columns ("fields") similar to a spreadsheet.  Database are made to analyze data.

    In field name, type "id" then click on Data Type to see the dropdown menu.  Click "auto-number" for datatype.  
    Create a few more fields:
	"MyName" with type text (text limits you to 255 letters, memo is unlimited text). Go down to the bottom of the screen.  In "Field Size" change this to "255" (some people, like me, have LONG names!)
         "BDay" with type "DateTime".  Go down to the bottom of the screen to "Validation Rule", type ">1/1/1900"  (what am i saying here?)

	Now save the table (click the disk icon at the top until Edit)
	Access gives you a pop-up.  something about a primary key.  what is it talking about?  A primary key is a field that is guarented to uniquely identify each record - like your student id or a social security number.  It is a good idea for any table in a database to have a primary key so you can reference any row from another table.  Go ahead and click yes.  (if you want more info about this, ask a ta)

	Now click on the arrow icon in the upper left corner under File.  Click on "DataList View"
        Enter your name and birthday.
        Now try entering a birthdate before 1/1/1900 - what happens?

3.  Now we are going to import the data from the excel spreadsheet into Access.

    Go to File -> Get External Data -> Import
    At the bottom of the file dialouge change File Type to "Microsoft Excel"
    find the USNew Spreadsheet and click it
    	1) Select "Show Worksheets" and highlight Sheet 1 (or whatever sheet has the data on it) click next.
	2) Check "first row contains column headings" click next
	3) Select "In a new table"
	4) Click Next
	5) select "let Access add a primary key" (remember the primary key?)
	6)Name the table. click finish.

	you get a pop-up talking about errors. click ok. (what happened - access choose the data type for each field and it wasn't always right - meaning the data wasn't always entered the same way.  if you want to see which data was messed up click on the _ImportErrors table)

	click on the table you just created and revel in how awesome you are.

	now.  lets get back to the analyzing.

4.   close the table.
     Do you see where it says "Queries"? Click Queries. Click "Create Query in Design View"
     Add the table you just created.
     Click close.
     In the first column, in the row field select "id"
     2nd column = rank
     3rd column = school (state)
     4th column = peer assessment ...
     5th column = graduation rate ...
     Save your query.
     In the upper left corner, click the arrow and select "Datasheet view"  what do you see?
     now go back to the design (in upper left, click arrow and select "design view"
     in the peer assessment column, go down to "criteria". in the text box enter ">4.5"
     now click datasheet view.  what do you see?
     go back to design view.
     in the graduation column, go down to criteria. in the text box enter ">20"
     now click datasheet view.  what do you see?
     go back to design view. 
     in the graduation column, go down to "or". in the text box enter "<17
     now click datasheet view.  what do you see?

     sweet.

5.   in the upper left corner, click the arrow and select "SQL view"
     
     what is sql? the standard query language.  it is how almost all databases analyze and query data.  it is pretty cool and pretty simple.  let's learn it.

     look at the statement show.  do you understand it? (if you don't ask a ta.)

     now delete the field names between SELECT and FROM (do NOT delete select and from) and replace them with *
     now click datasheet view.  what does * mean?

     go back to sql view.

     try changing some of the values or column names after WHERE.  put parenthesis around parts (remember the rules you learned in grade school?)  add new stuff. (remember: use: >,<,=,<>,<=,>=, and, or, between, not.)  google for SQL if you want more suggestions.

     you're done. rock.