CSCI 107 Lab 12, Creating a Database in MS ACCESS

We are a nation and a world of data. We must be able to manage all this data in some way. The usual method is by use of some Database Management System (DBMS) about which we have discussed in the class lectures. We will be using Microsoft's ACCESS. In this lab you will learn techniques for creating and updating a database table file. We will follow the outline below.

  1. Your instructor will give a brief discussion and demonstration of some of the procedures that are necessary to create and modify a table in an ACCESS database application.
  2. The remainder of this handout will guide you through some steps to create a simple table.

Creating new database and a table object

We will create a database that could be used by a research group studying Tornado activity. From the data entered, one could make various queries to maybe help in predicting Tornado touch downs. Like WordPerfect and Presentations, Access also has predefined templates for commonly used databases. However, we will not use the the Wizards or the templates and will create our own from scratch. To begin the creation of the new database and the first table:

  1. Use the Explorer or My Computer to create a new directory on your floppy called ACCESS, then
  2. If Access is not already runing
  3. Type the name of the database Tornadoes (unlike other applications where the file is saved and named when you are finished, you must name the file at the begining. The records are saved automatically as they are entered and it is not necessary to save the database, just close it when you are finished)
  4. Be sure that yourSave in: window has Drive A:\access selected.
  5. Select Create
  6. Select: Tables tab, New, Design View, OK

Using the gid below, type in the field name; TAB to the next column and select the data type from the drop down list; TAB to the next column and type in the description. The field name should be a unique name which must begin with a letter followed by letters,numbers, spaces or punctuation. Text fields have a default width of 50 character, but Access stores only the data you enter in the field

Field Name Data Type Description
TornadoID AutoNumber A unique number automatically created to identify a tornado
City Text Nearest city where the tornado touched down
WindSpeed Number Wind speed in MPH
Hail Yes/No A logic field whether hail was contained in the tornado
TouchDate Date/Time Date tornado touched down
DamageCost Currency Estimated cost of damage caused by the touch down
Notes Memo Special characteristics noted by observers

You would normally need to specify a primary key to uniquely identify each record in the table. We used the AutoNumber which is automatically indexed, but if we had not specified that as one of the fields you would have had to create a unique index on a selected field to create the primary key. This primary key is used by Access for searching and defining relationships between tables.

  1. You can specify other indexes for sorting which are not unique. For instance you could create an index on city so that you could later list the records in order of the city names where a tornado could hit a city more than once. To create a non-unique index:
  2. Click anywhere in the row of cities field
  3. In the Field Properties below, click theIndexed text box
  4. Click the down arrow and select YES(Duplicates OK)
  5. Close the the table by clicking the X box in the table window

All we have done at this point is create the structure, but we have not entered any data. To enter data:

  1. Be sure the Table tab is selected, then double click on Tornadoes
  2. You make up three records of some appropriate data for each field. Use the TAB key to move to the next field.
  3. Edit any field by selecting the field and overtyping the data in the field box.
  4. To delete a record, select the whole record by clicking in the small column to the left of the first field, then press the DELETE key
  5. To see a list of all your records, click the DataSheet View in the main menu
  6. You can change the display order of the fields (columns) by pointing the to field name and dragging it to a new location.
  7. You can display the records (rows) in any order, by clicking any row in the field to be sorted by, then selecting one of the two sort icons in the main main menu. These are only temporary sorts, unlike the indexes created above which are saved with the table.
  8. Close the table by clicking on the X box in the table window
  9. Close the database by selecting File, Close Database

Turn in your disk at the end of the lab.