Information Systems for Managers

Access Tutorial

(You may link to this tutorial, print it out, or refer to it using proper scholarly citation practices, but you may not copy its contents.)

What we're going to do here is go through most of the steps to create a database application. We'll use Microsoft Access 2000, one of the major microcomputer-based DBMSs on the market. We will only scratch the surface of what Access can do, but hopefully this tutorial will whet your appetite to learn more about it or another DBMS. Although the mechanics of the tutorial are specific to Access, the concepts apply to any DBMS you may use to create your application.

At the end of this tutorial, a hotlink will be provided for you to download a copy of the database created here, to compare with what you create.

It is assumed you know about navigating in a Windows 95/98/Me/XP environment, and have a user's familiarity with working with a personal computer. This includes concepts of saving and retrieving files, launching applications, managing windows on your screen, and mouse mechanics.

The Application

Imagine you have been called in by the special assistant to a Fortune-500 company CEO. She feels overwhelmed by the volume of correspondence coming in that has to be handled, and wants a way to keep track of it. As letters and email arrive, it is usually assigned to an assistant for handling, who may further assign the matter to a division or department. The special assistant wants a way to stay on top of this correspondence, so that none of it gets misplaced, mishandled, or improperly ignored.

Table Design

Let's assume we are keeping track of correspondence items. This defines what a record will be in our database. What are the characteristics of a correspondence item that are of interest to us?

The following list may not be as complete as all of you may like, but it will serve the purpose of illustrating how to build an application in Access.

Date received
Form of correspondence (mail or email)
Sender's name
Sender's organization
Assigned to (the initials of the assistant to whom the matter has been assigned)
Category (complaint, request for information, request for services)
Response date
Response form (letter or email)
Number of staff hours devoted to the matter
Has the matter been closed?
Comments

Some comments on the design, and what the design implies:

Usually any application will have at least one date field, and often more than one. We could add other dates to this table to track the life of a correspondence matter, but we'll keep it simple for now. (One can always add fields to a table, if necessary).
We'll store the sender's name and organization, but not address, phone, etc. This is a design decision, not a technical one. This application won't be used to generate form letters or be the repository of "everything". The assumption is that there will be paper files that have a copy of the correspondence items and other supporting information. One has to make decisions like these when designing the application, based on how it will be used.
I'm sure you can think of other ways to categorize correspondence items, like priority, status of sender, etc. That's fine. The point is, the designer has to work closely with the user to create a table design that allows retrieval of information in useful ways.
Often there will be a "comments" field added for entering information not easily structured. Again, that's fine. But a caveat: data entered in comments are not easily searchable using standard DBMS features. If you find over time that you are always putting a priority code in the comments field, then consider creating a separate priority code field.

One could go on and on fine-tuning the design of the table, but we need to proceed. So let's go!

Invoking Access

When you start up Access, you will get a dialog box asking if you want to open an existing database or create a new one. Select "Create a New database Using Blank Database" as shown.

After clicking OK, save the new database, calling it Correspondence. Access, unlike many other DBMSs, puts all the components of a database into one large file. It is convenient, in that it is easy to keep track of when moving the database from one place to another. The disadvantage is that the file can quickly grow to a large size.

The next screen you will see shows the various kinds of components of a database that Access maintains. We will be dealing only with Tables, Queries, Forms and Reports. Clicking on each button at the left will show what components of that type are in the database. Since this is a new database, there are no components, yet.

We will sequentially go through the steps in creating the database, starting with creating the table.