Microsoft ACCESS - Introduction to Database
management
April 5, 2005
Word to the wise: this will not be easy to pick up without the demonstrations. Please be ready to go at 9 sharp.
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.
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 some spreadsheets listing global earthquake epicenter data: origin time (year, month, day, hour, minute, second), epicenter (latitude and longitude), magnitude and focal depth. Download and save one of the following workbooks.
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.
(Files and links deleted, data were flawed as set up).
I prepared these lists by importing text files into Excel, adding column headings and then exporting as tab-delimited text files. Access is supposed to accept Excel files but some spaces exist in the "Magnitude" field. This does not bother Access so long as the space lies between a pair of tabs but Access will not accept a spreadsheet with blanks.
In preparing these files, I was careful to organize each file such that fields are in the same order and have the same headings.
Now we are ready to begin filling a table. From the menus select
Insert - Table - Import table
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 next 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. Note how I wrote the depth_km field name so that the database thinks this is one word. Access does not like fields without headings or fields with headings consisting of more than one word.
This Excel workbook was set up with for the purpose of importing it into Access - in most cases you may accept the default by clicking 'next' - I would suggest typing in a new name for the Table, the default name is accurate for the data in one worksheet but will not be once we begin adding more data from other worksheets.
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.
Step one: compile the earthquake data table in Access. Once you successfully import all sheets from one workbook, repeat the procedure with the other files linked to this instruction sheet.
Now that we have hundreds of thousands of earthquakes in our table (note - you can fit only 65,000 or so 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. We double click "queries" and select "Create query in Design view." First think is to add the table that we wish to query (a database may contain many tables!) and then select the fields we wish to extract from the table.
After selecting fields, we click "Criteria" and are taken to the next 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.
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: 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 e-mail by noon, April 11.
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.
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.
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.