Access 2007 Tutorial 1.2. Tables and Relationships

Uploaded by djdates on 12.11.2009

I am going to create a simple database in Microsoft Access 2007. Access differs from
other Microsoft Office applications because it does not open up to a blank document. Instead,
we first need to specify where we wish to save our document before we create it. I am
going to begin by clicking on the blank database icon, and then I select where I want to save
my database by clicking on the open folder icon which indicates that I am browsing for
a location. I am going to create this database on my desktop and I am going to give it the
filename: books. Now that I've specified a location and filename, I can click on the
create button.
Access opens to an empty table in datasheet view. The first thing I want to do is view
this table in design view. I'm going to right-click on my table and select design view. It askes
me for a name for the table. I am going to type books. Now in design view, it shows me
a field that has already been setup in my table called ID. Notice to the left of the
ID field is a key indicator. This means that this field is the table's primary key. A primary
key is a unique piece of information that identifies a row in a table. Notice the data
type for this field is autonumber. This means that each new record in this table will have
a unique number generated from a sequence.
I am going to add a field name for my table and I going to call this field title, and
I am going to select the text datatype. I am going to select another field and call
this field published, and I will select the number datatype. The number datatype means
that only a number can be stored in this field. I cannot store a text value in this field.
I am going to right-click on my table name and open the table back in data sheet view.
Before I can open the table, it askes me if I first want to save my changes to the table
and I will click on yes.
I will now begin entering information into my table. Like Microsoft Excel, I can expand
my column widths. This does not change the data, but it makes it easier to enter data.
Now that I have entered data into my books table, I am going to create a new table called
authors. I click on the create ribbon, click on table, and it creates a new table in datasheet
view. I want to add fields using design view, so I am going to right-click on my table name.
I can right-click on my table name in the navigation pane or I can right-click on my
table name in the tab above the table, and then I go into design view.
First, I need to name my table, and I am going to name my table authors. Again it has created
a default ID field with the datatype auto number and again this field is the primary
key for this table. The ID field in the authors table will uniquely identify each record,
each row, in the authors table. I am using the tab key to quickly move between fields
to assist in data entry. Now that I have created my fields, I will enter the table in datasheet
view. And again saving my table.
I will now add the authors to my database. Now that I have added the authors to my database,
I want to create a relationship between my books and authors table. In order to create
this relationship, I will need to add a field in my books table that will indicate the author
that wrote the book. I am going to go back to my books table and I am going to enter
design view. I am going to add a new field and call it authorID. Because the ID field
in the authors table is an autonumber datatype, I am going to make sure I am using a number
datatype so I can enter the numbers and create the relationship. If I used a different datatype,
then I would not be able to create the relationship, because the datatypes would not match.
I am now going to close and save both of my tables and I am going to click on the database
tools ribbon and then click on relationships. The show table dialog box appears and I am
going to use the control button to select my second table, and then I will click on
the add button to add both of the tables. When I see that both of the tables are in
my relationship screen, I am going to click on close for the show table dialog box.
In order to create the relationship, I select the field that I want to relate and drag that
field onto the field in the other table that I want to relate to, and then let go. Dragging
the author ID field in the books table to the ID field in the authors table. When I
let go, the edit relationship box appears, showing me that I am relating the ID field
in the authors table to the authorID in the books table. I am going to check the box enforce
referential integrity and notice that my relationship type is one to many. When I click on create,
I can see that I have a one to many relationship. This means that one author can write many
books. I am going to close out of my relationship window, saving my changes, and I am going
to go to the books table and began entering authorIDs to relate authors to their books.
Looking at the authors table, I can see the name of the author I am relating, and as I
add the authors, I can see the effect of the relationship. Notice that the highest ID that
I have in my authors table is four. If I try to add an authorID to the books table, that
doesn't exist in the authors table, I will get an error. You cannot add or change a record
because a related record is required in tables. This is called referential integrity and is
one of the many features of a relational database that intends to keep the data valid. I can
only add authors to books that actually exist in the authors table, so I do not get invalid
data. Before I can see my changes in the authors table, I will need to close out of the authors
table and reopen that as well. I can now see that each author is related to the books that
they have written.