Transcript Lecture Note 9: Introduction to the MS Access

Lecture Note 9:
Introduction to the MS Access
Brief overview of Relational Databases and Database Applications

The first databases implemented during the 1960s and 1970s were based
upon either flat data files or the hierarchical or networked data models.
These methods of storing data were relatively inflexible due to their rigid
structure and heavy reliance on applications programs to perform even the
most routine processing. In the late 1970s, the relational database model
which originated in the academic research community became available in
commercial implementations such as IBM DB2 and Oracle. The relational
data model specifies data stored in relations that have some relationships
among them (hence the name relational).

In relational databases such as Sybase, Oracle, IBM DB2, MS SQL Server
and MS Access, data is stored in tables made up of one or more columns
(Access calls a column a field). The data stored in each column must be of
a single data type such as Character, Number or Date. A collection of values
from each column of a table is called a record or a row in the table.

Different tables can have the same column in common. This feature is used
to explicitly specify a relationship between two tables. Values appearing in
column A in one table are shared with another table.
Below are two examples of tables in a relational database for a local bank:

Customer Table
CustomerID
Name
Address
City
State
Zip
Number
Character
Character
Character
Character
Character
1001
Mr. Smith
123 Lexington
Smithville
KY
91232
1002
Mrs. Jones
12 Davis Ave.
Smithville
KY
91232
1003
Mr. Axe
443 Grinder Ln.
Broadville
GA
81992
1004
Mr. & Mrs. Builder
661 Parker Rd.
Streetville
GA
81990

Accounts Table
CustomerID
Account Number Account Type Date Opened Balance
Number
Number
Character
Date
Number
1001
9987
Checking
10/12/1989
4000.00
1001
9980
Savings
10/12/1989
2000.00
1002
8811
Savings
01/05/1992
1000.00
1003
4422
Checking
12/01/1994
6000.00
1003
3322
Savings
12/01/1994
500.00
1004
1122
Checking
11/13/1988
800.00

The Customer table has 6 columns (CustomerID, Name, Address, City,
State and Zip) and 4 rows (or records) of data. The Accounts table has 5
columns (CustomerID, Account Number, Account Type, Date Opened and
Balance) with 6 rows of data.

Each of the columns conforms to one of three basic data types: Character,
Number or Date. The data type for a column indicates the type of data
values that may be stored in that column.

Number - may only store numbers, possibly with a decimal point.

Character - may store numbers, letters and punctuation. Access calls this
data type Text.

Date - may only store date and time data.

In some database implementations other data types exist such as Images
(for pictures or other data). However, the above three data types are most
commonly used.

Notice that the two tables share the column CustomerID and that the values
of the CustomerID column in the Customer table are the same the values in
the CustomerID column in the Accounts table. This relationship allows us to
specify that the Customer Mr. Axe has both a Checking and a Savings
account that were both opened on the same day: December 1, 1994.

Another name given to such a relationship is Master/Detail. In a
master/detail relationship, a single master record (such as Customer
1003, Mr. Axe) can have many details records (the two accounts)
associated with it.

In a Master/Detail relationship, it is possible for a Master record to
exist without any Details. However, it is impossible to have a Detail
record without a matching Master record. For example, a Customer
may not necessarily have any account information at all. However,
any account information must be associated with a single Customer.

Each table also must have a special column called the Key that is
used to uniquely identify rows or records in the table. Values in a key
column (or columns) may never be duplicated. In the above tables,
the CustomerID is the key for the Customer table while the Account
Number is the key for the Accounts table.
Starting Microsoft Access

As with most Windows 95/98/NT/2000 programs, Access can be executed by
navigating the Start menu in the lower left-hand corner of the Windows Desktop. A
view of a Windows Desktop is given here: Note that your Windows desktop may look
slightly different).
To start Access, click on the Start button, then the Programs menu, then move
to the MS Office menu and finally click on the Microsoft Access menu item. The
MS Office Professional menu is shown below.

In the above file name, the a:\ indicates that the new database will be created on the
A: disk drive. bankdb is the name chosen for this particular database and .mdb is the
three letter extension given for Microsoft DataBase files.

It is advisable to keep the name of the database (bankdb in the above example)
relatively short and do not use spaces or other punctuation in the name of the
database. Also, the name of the database should reflect the database's contents.
Once the new database is created, the following main Access screen will appear

MS Access 2000
The main features of this main screen are the menu bar that runs along
the top of the window and the series of tabs in the main window. The
menu bar is similar to other Microsoft Office products such as Excel.
The menus include:

File - Menu items to Open, Close, Create new, Save and Print databases
and their contents. This menu also has the Exit item to exit Access.

Edit - Cut, Copy, Paste, Delete

View - View different database objects (tables, queries, forms, reports)

Insert - Insert a new Table, Query, Form, Report, etc.

Tools - A variety of tools to check spelling, create relationships between
tables, perform analysis and reports on the contents of the database.

Window - Switch between different open databases.

Help - Get help on Access.
The tabs in the main window for the database include:






Tables - Displays any tables in the database.
Queries - Displays any queries saved in the database.
Forms - Displays any forms saved in the database.
Reports - Displays any reports saved in the database.
Macros - Displays any macros (short programs) stored in the
database.
Modules - Displays any modules (Visual Basic for Applications
procedures) stored in the database.
Review of Starting Microsoft Access

Use the Start button on the task bar to open: Programs -> MS Office ->
Microsoft Access

To create a new database, choose Blank Database and specify a new
file name for the database. Be sure to use the drive letter (A:) and a
descriptive name for the new database. Click on the OK button to create
the new database. To open an existing database, choose Open an
Existing Database, highlight More Files... and click on the OK button.
Then navigate to the A: drive, highlight the existing database file on the
floppy disk and click the OK button again to open the database.

To exit Access, pull down the File menu and select the Exit menu item
Creating a Table Using the Design View

To create a table in Access using the Design View, make sure the Tables tab is
displayed (that is, Access should be set to work with tables rather than with queries,
forms, reports, etc.) and perform the following steps:

For Access 2000, double click on the "Create Table in Design View" item.
The Table Design View will appear. Fill in the Field Name, Data Type and Description
for each column/field in the table. The CustomerID field is filled in below:

Note that the default name given for the table is Table1. In a later step, we will assign
an appropriate name for this table.
Fill in the information for the fields as follows:
Field Name
Data Type
Description
CustomerID
Number
The Unique Identifier for a Customer
Name
Text
The Name of the Customer
Address
Text
The Address of the Customer
City
Text
The City of the Customer
State
Text
The home State of the Customer
Zip
Text
The Zip Code of the Customer
A figure showing the design view with the new table definition filled in is
given below:

Now that all of the fields have been defined for the table, a Primary Key
should be defined. Click on the CustomerID field with the Right mouse
button and choose Primary Key from the pop-up menu.

Notice that a small key appears next to the field name on the left side.
Note: To remove a primary key, simply repeat this procedure to toggle the primary key off.

As a final step, the table must be saved. Pull down the File menu and
choose the Save menu item. A dialog box will appear where the name of the
new table should be specified. Note that Access gives a default name such
as Table1 or Table2. Simply type over this default name with the name of
the table.

For this example, name the table: Customer Then click on the OK button.
Exercise: Creating a Table



Create the Accounts table by following the same steps used to create the
Customer table.
Click on the New button and highlight Design View in the dialog box that
appears. Then click on the OK button.
The Table Design View will appear. Fill in the Field Name, Data Type and
Description for each column/field in the Accounts table.
Field Name
Data
Type
Description
CustomerID
Number
The Unique Identifier for a Customer
AccountNum
ber
Number
The Unique Identifier for a Bank Account
AccountType
Text
The type of account (Checking, savings, etc.)
DateOpened
Date
The date the account was opened
Balance
Number
The current balance (money) in this account
(in $US)
A figure showing the design view with the new table definition filled in is given below


Define a Primary Key for the Accounts table. Click on the AccountNumber
field with the Right mouse button and choose Primary Key from the pop-up
menu.
Save the new Accounts table by pulling down the File menu and choosing
the Save menu item. Fill in the name of the table: Accounts Then click on
the OK button.
Viewing and Adding Data to a Table



Data can be added, deleted or modified in tables using a simple spreadsheet-like
display. To bring up this view of a single table's data, highlight the name of the table
and then click on the Open button.
In this view of the table, shown in the figure below, the fields (columns) appear across
the top of the window and the rows or records appear below. This view is similar to
how a spreadsheet would be designed.
Note at the bottom of the window the number of records is displayed. In this case,
since the table was just created, only one blank record appears
.
Adding Data to a Table

For this exercise, open up the Accounts table and add data for the seven
accounts. Be sure to enter the data exactly as shown including the capitalization
of the data in the AccountType field. e.g., type Savings instead of savings or
SAVINGS. Note that when entering the dates, type in the full four digits for the
year. By default, Access only displays the last two digits of the year; however, all
four digits are stored in the table.

Be sure to save the data when you are done. The figure below shows the
Accounts table and data as it should appear when you are done with this
exercise.
To add data to the table, simply type in values for each of the fields (columns).
Press the Tab key to move between fields within a record. Use the up and
down arrow keys to move between records.
Enter the data as given below:
CustomerID Name
Address
City
State
Zip
1001
Mr. Smith
123 Lexington
Smithville
KY
91232
1002
Mrs. Jones
12 Davis Ave.
Smithville
KY
91232
1003
Mr. Axe
443 Grinder Ln. Broadville GA
81992
1004
Mr. & Mrs. Builder 661 Parker Rd.
Streetville GA
81990





To save the new data, pull down the File menu and choose Save.
To navigate to other records in the table, use the navigation bar at the bottom of
the screen:
To modify existing data, simply navigate to the record of interest and tab to the
appropriate field. Use the arrow keys and the delete or backspace keys to change
the existing data.
To delete a record, first navigate to the record of interest. Then pull down the Edit
menu and choose the Delete menu item.
To close the table and return to the Access main screen, pull down the File menu
and choose the Close menu item.
Review of Creating and Viewing Tables

Creating a new table requires the following steps:

Click on the Tables tab on the Access main screen

Click on the New button.

Choose the Design View and click the OK button.

Fill in the name, data type and description of each of the fields in the table.

Designate a primary key by clicking on one of the fields with the right mouse
button and then choose Primary Key from the pop-up menu.

Save the table by pulling down the File menu and choosing Save.

Close the new table by pulling down the File menu and choosing Close.
To change the design of an existing table (e.g., to add, change or
delete a field):

Click on the Tables tab on the Access main screen

Highlight the name of the table to be modified and click on the
Design button.

Make the necessary changes.

Save the table by pulling down the File menu and choosing Save.

Close the table by pulling down the File menu and choosing Close.