Database Lesson 2
April 12, 2005
First, delete all files created last week, both tables in Access and text files used to construct those tables. I have removed flaws that inhibited loading all data into a single table, a table we will use in coming weeks.
I've found it easier to work with tables that have no primary key for these introductory exercises. Working with tables that are related one to the other usually requires careful use of primary keys but you will have to learn that in a more advanced and comprehensive class on databases.
Today's exercise has 4 objectives:
Importing data into a database table (reinforcing last week's activity).
Performing a simple query or two (reinforcing last week's activity).
Exporting query results for analysis.
Analysis of data in Excel (review Excel operations as we approach our final project date).
Right click each of the above links and "Save target as" text files to your flash drive.
Open Access and import the first file into a new table. Check the box for "No primary key." Review last week's instructions if you must. Then import the second file into that same (existing) table.
Earthquakes without magnitude ratings are useless to us today, so sort your table according to magnitude and delete those records for which no magnitude is listed. I scroll down after sorting until I see the first record with a value for magnitude. Select the magnitude-less record just above that record, then go to the top of the table, hold down the shift key and select the top row. All selected rows turn dark. In the Edit menu, select Delete and permanently delete the selected rows. Then save the table.
Each student will work with earthquakes from his or her own location. Get your assignment from this page.
Each student has a different 2-degree box. Query the earthquake database and extract earthquakes occurring within your area for years 1990 through 1994 and another query for 2000 through 2004. You need year, latitude, longitude, depth and magnitude (month, day, hour-minute-second are not needed).
Open your first query. If it appears to hold a lot of earthquakes fulfilling your criteria, Export (file menu) the query as a tab-delimited text file with headings. Do the same for your second query, exporting it under a different name.
Instructions for using Excel to extract earthquake statistics are on this page. You will have no further use for Access today, so you may close that application before opening Excel.
Save your Access work, we will use this global earthquake data next week or the week after in learning about ArcView.