Dilip's Tutorial on using Microsoft Access
In the database tutorial, we discussed relational
database theory and gave a few example screens from Microsoft Access. Here
we go into a bit more detail to guide you to creating a simple Access database.
Note that I am using Access95 version 7.0 under Windows95; there is
a more current Access97 now available.
Scenario
In the database tutorial, we worked with Employee Table.
In this tutorial, we will create this table and execute several
queries against it.
Creating a Table
From Windows95, we invoke the program and get the following screen, where we
elect to create a blank database. (Of course, we can use an existing
database as well.)
We are then asked for a file name to store the database. By
specifying the name "Dilips Tutorial", we are creating a file
named "Dilips Tutorial.mdb".
We now have a screen where we can click on the New button
to create a new table.
It is helpful to open a design view where we can enter
the attributes of the table, but we can get a jump start by
getting a helper "wizard".
The wizard has a generic employee database, and we use it to
get started. Here, we have selected fields that are close to what we need.
After using the Rename Field... button to get the field names
right, we push the Next> button to
name the table
When we push Next> again, we are prompted for the key
(later we can change the key by right clicking on the field
we want to be the key and selecting Primary Key).
Next, we choose to go to the design view to verify and possibly
customize the table's definition.
Here is the design view. Since SSN is the key, it is
represented as the first attribute, which is more conventional than
what we had done.
It looks like we don't need to do any customization (hurrah for
the wizard!), so let's enter our data!
Under the View pulldown menu, we switch from Table Design
to Datasheet.
Here is what it looks like in the midst of entering the third record.
We even spelled "Binghamton"
correctly now!
When we're done, we close the window (click on the "x" in the top right of the
edit window).
That's it! Our table is defined! We may have made it seem quite
laborious by presenting so many screens, but especially with the
help of the wizard, it is quite easy to do.
Making Queries
We need not write explicit SQL in Access. For example, let's write
a query that shows the names and salaries
of all employees making more than $50,000.
The SQL would look something like:
Select Name, Salary
From Employee Table
Where Salary > 50000;
In Access, we start by moving from the Tables to Queries
tab and pushing the New button.
(We'll be less laborious; there's not much to see yet, so let's keep
"talking".) We won't use a wizard this time, but go right into the
design view. This is what things look like here.
Access helps us by making accessible table attributes.
(If we had other queries, we could include them for reference too.)
We push the Add button and now we're ready to start.
We just fill in the form in a very intuitive manner and in seconds we
are done!
When we close this window, we can name this query.
All we need to do is double click on the name of this query
to get what we want!
Finally, let's look at the SQL by changing to the SQL view.
Access has needlessly fully qualified the attribute names
(in general, if there are multiple tables and some of the attribute
names coincide, qualification would be necessary, as discussed
in the database tutorial), but otherwise
the SQL we guessed looks correct!
Summary
Hopefully, this tutorial showed how easy it is to work with Access.
Operations are fairly intuitive; for example, if we want to add additional
data (i.e., tuples to our table), we would double click on the name of our table.