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.
- 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.
- Opening tables, Automatic record saving,
- Data types: Text, Memo, Number, Date/Time, Currency, AutoNumber, Yes/No,
OLE Object, Hyperlink
- Primary keys, indexes
- Adding and updating records
- 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:
- Use the Explorer or My Computer to create a new directory on your floppy
called ACCESS, then
- If Access is not already runing
- Go to: Start, Programs, Office 97, Microsoft Access, Blank Database,
OK
- otherwise Select: File, New Database, General tab, Double click:
Blank Database template icon
- 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)
- Be sure that yourSave in: window has Drive A:\access
selected.
- Select Create
- 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.
- 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:
- Click anywhere in the row of cities field
- In the Field Properties below, click theIndexed text
box
- Click the down arrow and select YES(Duplicates OK)
- 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:
- Be sure the Table tab is selected, then double click on
Tornadoes
- You make up three records of some appropriate data for each field. Use
the TAB key to move to the next field.
- Edit any field by selecting the field and overtyping the data in the
field box.
- 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
- To see a list of all your records, click the DataSheet View in the main
menu
- You can change the display order of the fields (columns) by pointing the
to field name and dragging it to a new location.
- 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.
- Close the table by clicking on the X box in the table window
- Close the database by selecting File, Close Database
Turn in your disk at the end of the lab.