Click to edit Master text styles
Microsoft Access is the fourth major
application in the Microsoft Office. It
is used to create and manage a database so you can add, edit, and delete
records, run reports and queries, filter records, and allows for the use of
multiple tables. Many businesses use
Access because it is a great database to store many different records and is
very easy to use once you learn it.
Since we are dealing with computers, you
must know the language of information systems.
Therefore, here is some vocabulary you should be familiar with when you
are using Microsoft Access. A field is
a basic fact or data element and an example is a book title or a telephone
number. A record is a set of
fields. A table is a set of records,
and a database consists of one or more tables.
This is a quick view of the database
window. It displays various objects
just as tables, queries, forms, reports, pages, macros, and modules. It also shows you the options of creating
tables in design view, creating tables by using wizard, and creating tables by
It is also necessary to know the
following descriptions of these objects.
A table stores data about a person, place, or thing and is the basic
element of any database. A table
appears to be columnar in appearance, with each record in a separate row of
the table and each field in a separate column.
A form provides a convenient way to enter, display and/or print the
data in a table. A query answers
questions about the database. A report
presents the data in a table or query in an attractive fashion on the printed
page. A page is an HTML document that
can be posted to the Web and can be viewed in a web browser.
The final two objects used are macros and
modules. A macro consists of commands
that are executed automatically one after another to automate the performance
of any repetitive task. A module
provides an even greater degree of automation through programming in Virtual
There are two ways to view a table in
Microsoft Access. The first way is to use
the design view. The design view is
used to design the table initially, to specify the fields, and to modify the
This is just a quick view of the design
view. You can see the different rows and
The datasheet view is the other way to
view a table in Access. With the database
view, you can add, edit, or delete records.
The first row in the table contains the field names and each additional
row contains records (the data). Each
column represents a field (one fact about the data).
This is a view of the datasheet
view. You can see the first row is made
up of the field names. There are also
some symbols which are explained in the next slide.
You should understand what each of the
symbols mean. A triangle icon means that
the record has been saved to the disk.
A pencil means that you are currently working on the record but the
record has not been saved. An asterisk appears
next to the blank record at the end of every table. An insertion point is a flashing vertical
bar which appears at the point where text is being entered. The primary key is a field or combination of
fields that is unique for very record in the table.
Let’s start with the very basics of
Microsoft Access. To open Access, click
start in the lower left hand corner of the screen. Move the cursor over the programs and select
Access. A screen will prompt the user
to start a blank database or open an existing file. Click the circle next to “open an existing file”
and click the file name you wish to open.
Click OK to open this database.
You will need to add a new record when
you are entering data into your table. You
add a new record by pulling down the insert menu and clicking “new record”. The new record will then appear at the
bottom of the datasheet.
When you want to exit Microsoft Access,
the first thing you do is close all of the tables and databases you are
working on. Then pull down the file
menu and click close. Answer yes to
save any changes. Then pull down the
file menu again and click exit. You
will now be out of the Microsoft Access Program.
The Microsoft Access database can contain
as many as seven different types of objects.
In this lesson, the student will be familiarized with three of these objects,
forms, queries, and reports.
student will be:
1. Introduced to forms, queries, and reports
2. Taught how to add, edit, and delete a
3. Able to demonstrate data validation
Forms provide interfaces that are easier
to understand and use than tables. The status
bar at the bottom of the form indicates which record you are currently viewing.
A query consists of a question and an
The results of a query resemble
those in a table with the difference being that the query contain selected records
and the selected fields for those records.
Thus, a query may list the records in a different order than does a
A report presents the records of a table
or query in a professional output. Because
they are in a more presentable fashion, they are preferable to printing tables
The Find command is very useful when
attempting to find a particular record within a large quantity of
records. To use the find command, first
open a desired table. Click the field
in which you wish to search within.
This will highlight all the records in the column. Pull down the Edit menu and click Find.
In the Find What text box, enter a phrase
that describes the record you are looking for.
You can narrow down the results by choosing the field to look within. Clicking the Find Next command, will prompt
Access to find this particular record within all the records.
As with all the other Microsoft Office
programs, Access provides many shortcut buttons on its toolbar. These convenient buttons increase work speed
and thus heighten productivity and efficiency.
An example of these shortcut buttons is the find button. The fastest way to find a record is to click
the shortcut find button located on the Microsoft Access toolbar represented
by a pair of binoculars. Clicking this
button will cause the Find dialog box to pop up without having to access the
command from the edit menu.
The replace command incorporates the
find command and allows you to not only to locate specific records but also to
alter them. It is very useful when it is
necessary to make a change to a large quantity of identical records. To demonstrate this, click on a field to
highlight its records. Click edit and
click Find. Click on the Replace
tab. Type in a phrase next to the Find
what text box and the phrase you want to replace it with in the Replace with
text box. By clicking OK, all the words
in the Find what text box will be changed to the ones in the Replace with text
Mistakes arise and can be reversed by
using the Undo command. To use this function,
pull down the edit menu and click on undo typing. This will allow you to undo the last
command. While the Undo command is
common to all Microsoft Office applications, Access enables the undo of only
the most recent command. The fastest
way to access the undo command is to click the shortcut undo button also
located on the Microsoft Access toolbar.
The delete command allows the user to
remove any unwanted records in a particular database. To use this function, click on any record to
select it, the record should be highlighted.
Pull down the edit menu by clicking edit. Click on select record to selected the
entire record. Press the delete key on
the keyboard to delete this record. A
prompt similar to the one shown above will appear confirming the deletion of
the record. Keep in mind that Microsoft
Access only allows the most recent events to be reversed by using the undo command.
Data validation is a crucial part of any
system. It anticipates errors that the user may make and rejects those errors
prior to accepting data. This can be demonstrated
by typing a non-numerical value into a record that requires a numerical entry
(i.e. zip code, copyright date).
Microsoft Access will detect this as an invalid value and a dialog box
will appear indicating this.
Reports are used to put information from
a table into a presentable fashion. Creating
reports is made fairly simple with the report wizard that comes with Microsoft
Access. Click the reports button in the
database window. Click new report and
select the report wizard. Add all the
fields from the table by clicking the double arrow button. Click finish and you will have made a report
in Microsoft Access.
When all else fails, Microsoft Access
provides its own interactive Office Assistant.
To access the assistant, click on the help menu and click on show the
office assistant. Click on the
assistant represented by a paper clip.
From this point, the user can select a topic from the menu or type in a
question to be answered.
Microsoft Access, as with many other
Microsoft programs, is suitable for both slow workers and those who have to
work in a hurry.
When there is no time
to make a report, luckily there is an easier and faster way to make obtain information
from a database.
In this chapter, the
student will be familiarized with filters and sorting and learn how these
concepts apply to databases.
Filters and sorts are most useful in
organizing large groups of data. A
filter simply “filters out” the records that the user does not wish to display
and shows only those specified by the user.
A sort works in conjunction with a filter and sorts the records from
the filter in a given sequence (I.e. alphabetically, numerically, etc)
Filters and sorts can help college
students like Deo and Coco to quickly gather information from a database. For instance, if there were books on Deo’s
book list by a particular author that he wanted to check, he could use the
filter command to view only those books written by that author. Furthermore, he can then sort these books
alphabetically to make the information easier to read.
The easiest way to implement a filter is
to click in any cell that contains the value of the desired criterion (i.e.
any record that contains Johnson). Then
click the shortcut filter by selection button on the Access toolbar. The result will be a database filtered so
that only those records with the author Johnson will show up.
This is an example of a filtered view of
a table I which the user wishes to only see those books that are written by
the author, Johnson. The status bar on
the bottom of the screen shows that this is a filtered list and that there are
three records that satisfy the criteria.
Note that the book titles are displayed in alphabetical order.
An alternate and more efficient way to
apply a filter is to use the filter by form command. Clicking the filter by form button will a
screen on which the user can select criteria from a drop-down menu for every
field in the database. This allows the
user to apply multiple criteria simultaneously. The most useful advantage of filtering by
form is that the user can specify relationships within a given criterion (i.e.
books with a copyright year greater than 2000). Filtering by selection, on the other hand,
requires that the user specify criteria equal to an existing value. Another advantage of filtering by form is
that the user can specify alternative criterion (i.e. book titles with
copyright year of 2000 and book titles by author Johnson). This is done by clicking the “or” tab located
on the bottom of the screen.
The sort command allows the user to list
the records within a field in a specific sequence. To use this command, click in the field on
which you want to sequence the records.
Click on either the sort ascending or sort descending button on the
Access toolbar. The sort ascending
button is appropriate for names while the sort descending button is more
appropriate for numeric fields.
The sort ascending button is appropriate
for fields such as last name, if you want to display the records with the
smallest value listed first.
The sort descending button is
appropriate for numeric fields such as salary, if you want to display the
records with the highest value listed first.
By clicking the Remove Filter button, it
will display the complete table at any time.
The power of Access is seen in its
ability to demonstrate relationships between multiple tables. The use of such functions becomes apparent
as more and more databases are added. With each database, common areas can be
related. These four points are the objectives of this section. The student will learn what a relational
database is and why it should be used.
They will also become more skilled at creating and utilizing relational
databases. Lastly, they will learn how
to use the referential integrity tool.
Start from the most basic explanation of
databases. The most generic database will contain only one table. This is very
simple and information can be retrieved using the scroll bar or sorting
A relational database contains more than one table. With more
tables, the amount of information is multiplied and it becomes hard to locate
certain things or to view certain groups of information.
The key to a
relational database is to relate certain information such as names, crops, and
area so that you can view all related information which pertains to each
A finished relational database can be used to retrieve all the
necessary information regarding one category using a single query.
Try and keep another computer and perform the tasks in the
Perform example queries to show how it is a hassle to do it every
Relational databases can help students
like Deo who want to know more information about their college books.
These three tables represent tables that
are related. The Books table is related
to the Publications table by Book ID and the Books table is related to the Topic
table by topic ID.
In order to find the necessary
information, Deo would have to first search the books table for the title of
the book “Fundamentals of Psychology” in order to obtain the Book ID. He would use this Book ID and search for it
in the Publication table to obtain the publication place. To find the topic, Deo would again have to
search the books table for the title of the book “Fundamentals of Psychology”
in order to obtain the Topic ID. He
would use this Topic ID and search for it in the Topic table to obtain the
topic of the book. This relationship
between the Books table and the other two tables is called one-to-many
This is an overview of how we are going
to set up relations between the databases. There are three databases we are
going to use as examples. The first one is called Books, the second one is
called Publications, and the third one is called Topics.
We are going to draw a relation between
Book ID’s which is an entry in both the Books database and the Publications
The usefulness of such a task is that in the future, we are
able to retrieve all information which relates to Book ID. For example, if we
wanted to know the place of publication name, as well as the type of cover, we
can do it with one query instead of two.
Now we will learn how to create
relationships between tables using Access.
Start by closing all windows and starting at this basic screen.
To set up the relationship between two
first click on tools and scroll
down to relationships.
anywhere on the screen, and select show table. Highlight books to add it and
highlight publications to add it.
Now, double-click anywhere on the gray
The Edit Relationships dialog box
will pop up.
This is the main menu for
beginning to draw relationships between the selected databases.
Click Select New.
Now, we must select the two tables we
are going to establish relationships for. On the left table, select Books, on
the right table, select Publications.
Now, this is the category which we
will relate the two databases with. Lets select Book ID for both the left
column and the right column.
Now, double click on the Publications table.
You can see that there is a “+” sign on the left of the first entry.
Click on it.
This brings you directly to all the other entries which is related to
From here, it is easy for you to view related data between both tables which correlate
to Book ID, and also change it if you wish.
With data entry, it is often important
to make sure that data is consistent. We are going to set up Referential
Referential Integrity is a function which is used to make sure
that data is consistent between related databases.
Go back to the
relationships screen. Double click on the line between the two tables. This is
the Edit Relationships menu. Click ok on Referential Integrity menu and click
enforce referential integrity. An error message will be displayed if the data