Microsoft ACCESS - Introduction to Database
management
March 29, 2011
You need your flash drive to save all of these data.
A simple database consists of tables and queries. A table consists of columns (fields) and rows (records). The format of a field can be text, numerical (integer or floating point) or logical (yes or no).
A table can be filled with information either by entering cell contents using the keyboard or by importing information stored in a text file or in a spreadsheet.
We will open a blank database. Note that the dialog box asks us to specify database name and storage location right out front. Pick a name and location (folder on your flash drive).
A database operates differently from most other applications in that the stored copy is updated frequently, not simply when we want to store the updated copy. Deleting a record might permanently remove that record from our database if we do not immediately "undo" (and I am not certain if even that will allow us to recover records we have deleted).
Minimize Access and import some data. I have prepared a text file listing global earthquake epicenter data: origin time (year, month, day, hour, minute, second), epicenter (latitude and longitude), magnitude and focal depth. Download and save these data.
Right click on each link and "Save target as" to some location (your Flash drive) that you will recall 10 minutes from now. "Saving target" works faster than first downloading into Explorer. If you do click on the file and get a list of numbers on your browser, "Save as" a text file to your flash drive. Download both files and save as text files (different file names, of course)/
Earthquakes prior to 11/07/2010 earthquake catalog
Earthquakes since last event in catalog (updated Monday, 3/28/2011).
Step one: compile the earthquake data table in Access.
Now we are ready to begin filling a table.
Get external data - Import
Navigate to the locations where your imported spreadsheet is stored. You may not see it listed - you may need to change Files of Type in order to display Excel workbooks. The files you downloaded and saved are text files. Select last year's earthquake catalog for import.
The first dialog box allows you to select the worksheet from those saved in the workbook specified. A database likes a header row that consists of one-word labels. Access does not like fields without headings or fields with headings consisting of more than one word.
Fields are: Year, month, day, hour, minute, second (together these are the origin time, when the earthquake began), longitude (east hemisphere positive), latitude (north hemisphere positive), depth (in kilometers) and magnitude.
Double or single? Integer or floating point? Long integer or integer? Long integers are needed for numbers over 32,000 (more or less, I am not certain where the cutoff occurs) and double precision is needed for floating point numbers with more than 16 digits. Designating too much precision wastes time and space, too little precision can generate errors if precision is needed. Year, month, day, hour, minute are integers. The rest are floating point. Depth is usually expressed as an integer for global earthquake catalogs but some studies have higher resolution, so floating point is appropriate. Set each field to single precision.
Do not let Access add a primary key. Do not select a field as a primary key. Select the 'no primary key' option. Advanced users find primary keys useful for linking tables in a complex database but I find they get in the way when I want to add more records to a table.
Once your new table is complete, Save it. The close the table ("X" in upper right corner).
This table is not complete. This data set begins in 1973, over 10 years after the World-Wide Seismic Network became operational as a tool for monitoring underground nuclear tests in the former Soviet Union. It ends early last March, when this lesson was last taught. The second file you downloaded consists of 'new' earthquake data. I set this up text file up with fields identical to those in your new table, this time you can 'Get external data - append the data in an existing table. Select from the table titles (you should have only one table so this should not be difficult).
Now that we have hundreds of thousands of earthquakes in our table (note - I can fit only 104,000 or so records into a single page of an Excel workbook), we can do what databases are designed for - extract those records (or parts of records) that match particular criteria which we designate. This is done by performing a query.
For once, the wizards are very helpful. Under the "Create" tab, click "Query Design." Select the table you wish to query and the fields you wish to view.
After selecting fields, we click a "Criteria" cell and then the Build wizard.. This wizard is called by clicking the magic wand button. We need to locate the table (click the Tables folder and then open the folder). We then build the query using items from the table together with =, < and >, along with such logical conjunctions as "and" and "or" along with brackets if necessary to make our meaning clear to the computer.
A little bit more on logical expressions
Once the query criteria are specified in the Expression Builder, we click "OK" and are returned to the query window. Clicking the red exclamation point (!) button executes the query. We now have a new table containing only those fields of those records that match the criteria expressed in what we wrote in Expression Builder.
Assignment 1: where and how big was the biggest earthquake that occurred on the date of your birth? Where and how big was the biggest earthquake that occurred on a subsequent birthday? How old were you on that date? Send me the dates and earthquake parameters via Blackboard message by Friday, November 12.
Negative latitudes are south latitudes, positive latitudes are north latitudes. Negative longitudes are west, positive are east. After getting the latitude and longitude of your birthday earthquake, look up this location on a map and provide me with a geographical name that I can Google.
Assignment 2: How many earthquakes of each size hit each year? Query the database for earthquakes equal to or greater than a given magnitude, beginning with small earthquakes and then for larger earthquakes, in some systematic manner (for example, greater than or equal to 4.0, greater than or equal to 4.5, etc.) from magnitude 4.0 to 9.9. Make (in Excel) a table listing earthquake ranges and number in each bin (similar to 'frequency' in Excel). Note that Excel counts the number of records for you! All you need do is query and then write down the number of earthquakes greater than or equal to that magnitude, as a function of magnitude.
Divide the number of earthquakes in each bin by the number of years covered by the database (3/28/2011 - 1/1/1973 = 37 years 3 months - the extra 3 months equals 0.25 year so 37.25 is close enough) to generate a column 'earthquakes per year'. Now plot Log10(N) greater to or equal to each magnitude M, where N is the number of earthquakes greater than or equal to each bin's lower range (in my example, I'd use 4.0, 4.5, 5.0, etc.). Interpret this graph. For part of the range the data typically fall in a more or less straight line. What happens at the low magnitude end? What happens at the high magnitude end? On the average, how many earthquakes > 7.5 happen each year, on the average? how many > 8.0?
Dr.D. will demonstrate building tables via import and then the procedure for building a query.
Ignore for the time being the fact that these dates and times are Coordinated Universal Time, the date and time of the prime meridian, which is 4 hours ahead of EDT and 5 hours ahead of EST. When it is 4 PM in Toledo, it is already 8 PM in eastern England (during the month of April). Save your table and database, we will use them next week.
Before saving your database, delete all queries! You will not need these again, and (more seriously), desktop security often blocks your opening a file with 'active content' (internal programs) because they might include a computer virus. Unfortunately, security has been unable to discriminate between a real virus threat and built-in legal and legitimate activity (like Access queries). Getting started next week might be easier if you delete all queries before saving.
I heard on the grapevine the rumor that Bill Gates was attempting to write software that functions as both a database and a spreadsheet. The Windows version of this software is, of course, Excess.