Microsoft Access
Robert H. Smith School of Business
University of Maryland – College Park

Slide 2

Lesson #1 – The Basics of Access
You will become more familiar with   the vocabulary used in Access.
You will learn about both the  Datasheet and Design View.
You will learn the primary functions      of Access.

What is Access?
MS Office Program
Used to create and manage databases
Add, edit, and delete records
Run reports and queries
Filter records
Use multiple tables

How Can Access Help?
College students have extensive book lists
Access can help students like Deo and Coco organize these books according to their titles, authors, and prices

Vocabulary to Know
Field
basic fact or data element
Record
set of fields
Table
set of records
Database
consists of one or more tables

The Database Window

Describing the Various Objects
Table
Stores data about an entity
Form
Way to enter/display/print data
Query
Answers questions about database
Report
Presents data in table or query
Page
HTML document/Post to Web

Describing the Various Objects
Macro
Automates performance of repetitive task
Module
Provides greater degree of automation

Tables:  The Design View
Used to define the table initially
Used to specify the fields
Used to modify the table definition

A View of the Design View

Tables:  The Database View
Used to add/edit/delete records
First row contains field names
Additional rows contain records
Each column represents a field

A View of the Database View

Quiz: Question 1
Which sequence represents the hierarchy of terms from smallest to largest?
database, table, record, field
field, record, table, database
record, field, table, database
field, record, database, table

Quiz: Question 1 Answer
Which sequence represents the hierarchy of terms from smallest to largest?
database, table, record, field
field, record, table, database
record, field, table, database
field, record, database, table

Description of Access
Symbols and Terms
Triangle
The record has been saved
Pencil
Currently working but not yet saved
Asterisk
Blank record at end of table
Insertion point
Flashing vertical bar
Primary key
Unique field for each record

Opening a Database
Start Access
Click on “open existing file”
Highlight the desired database
Click OK to open the database

How to Add a New Record
Pull down the insert menu
Click new record
New record appears at bottom of datasheet

How to Exit Access
Close tables and databases
Pull down file menu
Click close and yes
Pull down Menu
Click exit

Quiz: Question 2
A pencil indicates that:
a record has been saved
a record has not yet been saved

Quiz: Question 2 Answer
A pencil indicates that:
a record has been saved
a record has not yet been saved

New Lesson!!!

Lesson #2 – Forms, Queries, and Reports
You will:
Be knowledgeable of the basics                        of forms, queries, and reports
Be able to make basic additions,                edits, and deletions of records
Understand the concept of data validation

Forms Revisited

Queries Revisited

Reports Revisited

Using the Find Command
Click field to search
Click Edit
Click Find to display the box shown on the right

More on the Find Command
Enter a searchable phrase in “Find
What” text box
Click the “Find Next” command
Access will move to this record

Slide 29

Quiz: Question 1
The shortcut find command can be accessed by clicking which symbol located on the toolbar?
Magnifying Glass
Lightning Bolt
Binoculars
Question Mark

Quiz: Question 1 Answer
The shortcut find command can be accessed by clicking which symbol located on the toolbar?
Magnifying Glass
Lightning Bolt
Binoculars
Question Mark

The Replace Command
Click on any field
Click Find shortcut button
Click on the Replace tab
Type a phrase to be replaced with another phrase and click OK

The Undo Command
Used to reverse a recent action
Pull down the edit menu
Click Undo typing
This reverses the last command made

The Delete Command
Click any record
Click select record in the edit menu
Press the delete key
The prompt shown here confirms this action

Quiz: Question 2
If Coco wanted to find all              books written by a certain author, which of the following commands   would she use?
Replace Command
Undo Command
Delete Command
Find Command

Quiz: Question 2 Answer
If Coco wanted to find all              books written by a certain author, which of the following commands   would she use?
Replace Command
Undo Command
Delete Command
Find Command

Data Validation
Click on a record that requires                     a numerical entry
Attempt to enter a non-numerical value
Press tab key to move to next field
Access will display a dialog box         indicating that the value is invalid              for the field

Making a Report
Click Reports in the Database window
Click New report
Select Report Wizard and click OK
Click the double arrow (>>)
Click Finish

Office Assistant
Click show the office assistant in Help menu
Click on the paper clip helper
Type question

Quiz: Question 3
If Deo wants to put his book list records located in his table into a presentable and printable fashion, what type of object would he create?
Form
Report
Query
Database

Quiz: Question 3 Answer
If Deo wants to put his book list records located in his table into a presentable and printable fashion, what type of object would he create?
Form
Report
Query
Database

New Lesson!!!

Lesson #3 – Filters and Sorting
You will:
Have an understanding of filters and
sorting
Be able to filter by selection
or form
Learn to sort ascending and descending

What are Filters and Sorts?
A filter displays a subset of
records according to a set criterion
A sort lists these records in
a specific sequence

How do they Help?
Deo wants to check the prices
on certain books on his list,
he can use the filter command
Coco wants to organize these books
by author, she can use the
sort command

Filtering by Selection
Click cell that contains the value
of the desired criterion
Then click the Filter by Selection
button on  the Database toolbar

Filtering by Selection Continued
A list filtered by Author

Filtering by Form
Click Filter by Form button

Quiz: Question 1
A filter:
Lists records in a specific sequence
Displays a subset of records from the table according to a specified criteria
Removes records from a field permanently
Creates a printable version of a table

Quiz: Question 1 Answer
A filter:
Lists records in a specific sequence
Displays a subset of records from the table according to a specified criteria
Removes records from a field permanently
Creates a printable version of a table

Sorts
Click on field to sort
Click on the sort ascending or
sort descending

Sort Ascending
The author field is sorted from
A-Z

Sort Descending
The quantity field is sorted from largest descending to smallest

Quiz: Question 2
To sort numeric values, the most appropriate command to use is:
Sort Ascending
Filter by Form
Sort Descending
Filter by Selection

Quiz: Question 2 Answer
To sort numeric values, the most appropriate command to use is:
Sort Ascending
Filter by Form
Sort Descending
Filter by Selection

Remove Filter Button
Clicking this button will remove the
filter
The complete table will be displayed
again

New Lesson!!!

Lesson #4 – Relational Databases
You will:
Learn what a relational database is
Learn why to use it
Become comfortable with using and creating one
Learn to use the referential integrity tool

What is a relational database?
Most basic databases contain one table
Relational databases contain at least two tables
Show relationships between multiple tables
Enables the user to extract information
in a single query

How Do Relational Databases Help?
Say Deo wanted to know the
publication place and topic for “Fundamentals
of Psychology”
He would use the following tables

Slide 61

How is it Used?
Search Books table for the title
to obtain the Book ID
Search Publication table for this ID
to obtain publication place
Repeat above steps to find topic

Quiz: Question 1
What is the minimum number of databases needed for a relational database?
1
2
3
4
5

Quiz: Question 1 Answer
What is the minimum number of databases needed for a relational database?
1
2
3
4
5

An Overview
Database containing three tables:
Books
Publications
Topics

Drawing a Relation
Drawing a relation using TopicID
Between Books
table and Topic table

How to Create Relationships
Close all windows to main page

Setting the Relationships
Click Tools and select Relationships
Right click on screen
Select Show table
Add Books and Topic tables

Editing Relationships
Double-click anywhere on the gray area
Edit Relationships menu pops up
Select Create New…

Establishing the Relationships
Select Topic Table on the left and Books on the right for table name
Select TopicID for right and left column name

More on Relationships
Select the Topic table.
“+” allows for easy access to:
Entry of related database data.
Viewing of corresponding data.

More on relationships
Click on tools, and select relationships.
You should get to this screen.

Referential Integrity
Used to prevent data entry error
Double-click on the line between tables
Click on Enforce Referential Integrity
Click Ok

Referential Integrity
Notice the 1 and “infinity” sign.
This denotes the nature of the relationship.
This is a one to many relationship.
There is 1 topic to every book.
There are many books per topic.

Quiz: Question 2
What is referential integrity used for?
The verification of spelling
The consistency of numbers
The making sure that data is consistent with with other related data
The making sure that databases fields are compatible

Quiz: Question 2 Answer
Referential integrity used to:
Verify spelling
Assure the consistency of numbers
Assure that data is consistent with with other related data
Assure that databases fields are compatible

Slide 77

Credits
Mark Rabinowitz
Jimmy He
Sen Lin
Hannah Chang