Spring 2007 - Very Basic MS Access
This handout very briefly describes how to create a simple database "application" using Microsoft Access. It assumes the reader is familiar with the example database in Figure 9.5 (p. 399) of the textbook containing EMPLOYEE, JOB, and ASSIGNMENT relations and reproduced here. These relations are used as examples throughout this document.
EMPLOYEE Empl Id Name Address SSN ----------------------------------------------------------- 25X15 Joe E. Baker 33 Nowhere St. 111223333 34Y70 Cheryl H. Clark 563 Downtown Ave. 999009999 23Y34 G. Jerry Smith 155 Circle Dr. 111005555 : : : : JOB Job Id Job Title Skill Code Dept -------------------------------------------------- S25X Secretary T5 Personnel S26Z Secretary T6 Accounting F5 Floor manager FM3 Sales : : : : ASSIGNMENT Empl Id Job Id Start Date Term Date -------------------------------------------------- 23Y34 S25X 3-1-1999 4-30-2001 34Y70 F5 10-1-2002 -- 23Y34 S26Z 5-1-2001 -- : : : :
For this class, we will create new blank databases. After
launching the MS Access application, if this choice is not available
on the start page, use File -> New, then choose Blank
Database.
MS Access database applications consist of 4 parts: Tables,
Forms, Queries, and Reports. Each is described below. For this
course, we will use MS Access itself as the actual application.
Normally, after creating a database and its associated forms, queries,
and reports, it would be embedded into an actual application. This is
beyond the scope of this course.
For this class, we will create tables using the Design view.
Double-clicking on this choice will cause a form to appear for typing
in field names and data types. Most of the field data types will be
Text, but occasionally there may be number or date data. The form may
be navigated using tabs and arrow keys. When a table is saved for the
first time, a dialog box pops up to ask for the table name.
Every table should have a primary key. A primary key
is a field that is unique for each entry in the table and is used as
the link between tables. The primary key may be a field that is
defined for the data being stored, such as EmplId in the EMPLOYEE
table or JobId in the JOB table. These fields are designated as the
primary key of their tables by right-clicking on them and choosing
Primary Key. Some tables do not have a naturally occurring primary key
field, such as the ASSIGNMENT table. None of the fields in the
ASSIGNMENT table are intended to be unique. For these tables, MS
Access will ask if one should be created when the table is saved. Yes
should be clicked and MS Access will add a primary key field (with
default name ID) that is an auto-number field. This means that it will
be automatically filled with a unique number every time a new record is
added to the table.
Most often data will be entered into the database via a form,
which is explained below. However, if data is available at the time
the database is created, it can be entered directly into a table using
the Datasheet view. If the table is not open, double-clicking on the
table item under the Tables tab will open it in Datasheet view. If it
is open in Design view, right-click on the title bar and choose
Datasheet view. A place to enter new record data is automatically
created at the bottom of the table whenever a new record is entered.
One caveat about data: many symbols have special meaning to MS Access;
in particular "*" is one such special symbol, so the textbook
example of using it to represent an empty termination date in the
ASSIGNMENT table will not work in MS Access. Its use has been
replaced with "-" in the reproduction above.
Fields may be added to the table after creation by
right-clicking on the field to the right of where the new field is to
be inserted and choosing Insert Column. The name of a field can be
changed by right-clicking on it and choosing Rename Column.
Once all of the tables of the database have been defined they
should be closed. (An open table may cause other actions to fail
because MS Access will not overwrite data in an open table.) Then
they must be linked together into relationships. These links
basically are permanent JOIN operation where clauses that determine
which field in one table means the same as a field in another table
when more than one table is involved in a query. For example, the
EmplId field in the EMPLOYEE table means the same as the EmplId field
in the ASSIGNMENT table. Relationships are created by using Tools
-> Relationships. A Show Table dialog box will pop up asking
which tables to add to the relationship diagram. To link two tables,
click on the field that is to be the link in one table, then drag and
drop it onto the corresponding field in the second table. A dialog
box will pop up to confirm, click OK. Finally, the relationships
diagram should be Saved.
For this course we will create queries using the Design view.
Double-clicking on this choice will pop up a Show Table dialog box
asking which tables or queries to add to the new query. A query is
built by specifying which fields are to be considered during the query
and whether the field should be shown in the resulting table. (Shown
fields essentially are the PROJECT operation arguments.) For example,
the textbook query to create a list of employee names and the
department they work in would include the EMPLOYEE.Name field, the
JOB.Department field, and the ASSIGNMENT.TermDate field. However, the
ASSIGNMENT.TermDate field is not to be shown. Instead, it is used to
select the records of interest after joining the three tables. (I.e.,
the unshown fields basically are the fields used in SELECT operations
where clauses.) The selection is done by specifying a match Criteria.
Matching one of several criteria can be done by use of the OR boolean
operation. Criteria regarding equality can be typed in directly; MS
Access will automatically add the enclosing double quote marks if the
field is a Text field.
From the Design view, a query may be executed by pressing the
exclamation point (!) icon button on the toolbar. This will result in
a Datasheet view of the result. To get back to Design view,
right-click on the title bar and choose Query Design. When a query is
saved for the first time, a dialog box pops up to ask for the query
name. A query also may be executed by double-clicking on its entry
under the Queries tab.
The choices made in the wizard determine what the form will
look like. If none of the standard choices are suitable, a form can
be created using a designer similar to the VB GUI designer. Doing
this is beyond the scope of this course, as is creating forms that
allow entering data into more than one table.