Session 5: Working with Ms Access
Software
Learning Objectives
By the end of this session, students are expected to be able to:
1.Terminologies used in Ms Access
2.Create a new database
3.Primary Key
4.Relationship between tables
Terminologies used in Ms Access
•DESIGN VIEW- Thisis the view where a user is able
to define different fields in a table. The fields are
defined by entering a field name, a data type, a
description (optional) and setting the field properties.
•DATASHEET VIEW – This is the view of a created
database, where one can view all the information
presented in rows, columns and often view some
records.
TABLE – A table is a collection of records that contain
same fields. It simply is data about a specific subject,
such as products or suppliers of some goods. A business
might create a database to store information about its
employees. All the information would be stored together
in a table. Using a separate table for each topic means
you store the data only once, which makes your
database more efficient and reduces data-entry errors.
Tables organize data into columns (called fields) and
rows (called records).
A FIELD (column) - This is the space where a single piece of
information on a subject is held. A field is created for each piece of
information to be stored, e.g. Name, Address, Date of Birth, ID
number, Department etc.
A FIELD NAME - This is the name that identifies a specific field.
Field names can have a maximum of up to 64 characters.
A RECORD (row) - This is a collection of data that is represented in
a row when viewed in datasheet view of a table, query or a form. It is
a complete set of fields that are related to the same subject. A record
is created for each person by entering the appropriate information
under each field heading.
A PRIMARY KEY- This is one or more fields that uniquely identify
each record in a table.
RELATIONSHIP – This is an association between common fields in
CREATING A NEW DATABASE There are two parts
to creating a new database. The first half involves
creating a template for your table, i.e. telling the system
how many records (rows) and fields (columns) you need,
and the field (column) headings that you wish to use.
This is known as designing the database and is done in
design view. The second part involves entering the data
itself and is done in Datasheet view.
•LOADING MS ACCESS - Double click on the MS-
Access Icon on the desktop labelled MS Access. If this
is not available click on START at the bottom left of the
screen then click on Programs/ Microsoft office/ then
Microsoft Access.
Why Access
• Easy to learn on
• Low cost
• Readily available
• Design principles we will learn can also be
applied to enterprise level database
management systems.
• Access can be used as an “interface” to pull
information from other sources such as MSFT
SQL databases or other databases.
To open Microsoft Access using the Start program:
Click the Start button located in the lower left corner
of the Windows screen.
Click the Programs option on the Start menu.
• Click the Microsoft Access selection.
(If Access does not appear, then click the double down
areas immediately below the Microsoft PowerPoint
entry and locate Access from a complete list of
software installed on your computer.
Major Components of Access Objects
• Tables
• Queries
• Reports
• Forms
• Macros
• Modules
Objects
8
Tables
Tables hold the information, called
data
9
Tables - Data Types
• Text Use for text or combinations of text and numbers, such as addresses, or for numbers that
do not require calculations, such as phone numbers or postal codes (255 characters)
• Memo Use for lengthy text and numbers, such as notes. Stores up to 63,999 characters
• Number Use for data to be included in mathematical calculations, except money
• Date/Time Use for dates and times
• Currency Use for currency values and to prevent rounding off during calculations.
• AutoNumber Use for unique sequential that are automatically inserted with a new record
• Yes/No Use for data that can be only one of two possible values, such as Yes/No, True/False,
On/Off.
• OLE Object Use for OLE objects (such as Microsoft Word documents, Microsoft Excel
spreadsheets, pictures, sounds,
• Hyperlink Use for hyperlinks (hyperlink: Colored and underlined text or a graphic that you click
to go to a file, a location in a file, a Web page on the World Wide Web, or a Web page on an
intranet. Stores up to 2048 characters.
• Lookup Wizard Use to create a field that allows you to choose a value from another table or
from a list of values using a combo box
10
Table Encounter
• The prospect of creating multiple tables almost always intimidates beginning
database users. Most often, beginners will want to create one huge table that
contains all of the information they need, similar to an Excel spreadsheet.
• When thinking about which fields to add to a table, a good first guess is: What
piece of information will only occur once?
• Such as
• A person will probably only have one first name, though it may change
• First_Name is a good candidate for the “PI” table
• A table needs at least on field that never duplicates in the same table
• Two or more people can have the same first, last and middle names
• A person may have more than one award (per name)
• The award title is not a good candidate for the PI table, since we don’t know how many awards a person may have
11
Open a Database
Open
Recent Documents list
• Choose Open to browse for a file or choose a
database from the Recent Documents list
12
Open a Database
Open Recent
Database list
• Choose a database from the Open Recent Database
List or click More to browse for other databases
13
Database Terminology
A database is made up
• Field of one or more tables
Individual fields
• Record
• Table
• Database
Individual tables in a
database
Records
A database consists of one or more tables. Each table consists of records which contain information about a single entity.
An example of one complete record would be the name, author, isbn#, published date and publisher of textbooks. Each set
of information regarding one book is considered to be one record. The name, author isbn#, published data and publisher in
the above example are the individual fields that make up one record.
14
What is a Table
• A set of records.
• Different Views of tables:
1. Design view used to create fields
2. Datasheet view used to add, edit, or delete records
3. Pivot table view used to summarize data about groups of records.
4. PivotChart view creates a chart from the associated PivotTable view.
Work with Table Views
Design View
Datasheet View
• Datasheet View – used to add, modify, delete and
view records
• Design View – used to create and modify the fields in
a table
16
Datasheet View
Primary key field
Navigation buttons Scroll bar
Navigation bar
17
Design View
Key symbol identifies primary key field
• Click F6 to
switch
between the
upper and
lower panes
Set field properties in the
lower pane
18
Primary & Foreign Keys
• Primary Key (PK)- Uniquely identifies each record in a table. It is needed
for searching the database.
• Some times if there is no unique value in a table the database designer
may use a auto number in Access to use a unique serialized number as the
primary key.
• Examples: SIN, Student Number, heath card number.
Primary & Foreign Keys
• Foreign Key (FK)- A field in one table that is a
primary key in another table. It is with these
values we can build relationships between
tables.
• The values of an FK must satisfy one of two
conditions:
- any FK-value is a value appearing in the
referenced table or a null value.
• Non Key- a regular field in a database.
Forms, Queries, and Reports
Report Query Form
• Forms, queries, and reports are all based upon data
contained in a table
21
Forms
Form
First record from table
Underlying table
visible in form
• Forms allow us to create an interface that can be
more user friendly and attractive than Datasheet View
22
Queries
Criterion restricting
dataset to show records
that have a job title of
Sale Representative
Query results showing
only employees who are
Sales Representative
• Queries allow us to question data
• The answer to the query is a dataset
• The question asked is formed using criteria – the rules or norm
that is the basis for making judgments
23
Backing-up and Renaming
Access Files
• Save As – different in Access than other Office
applications
• Save As saves only the current object, not the entire
database
• To save a database with a new name you must either:
• Backup the database
• Copy, paste, and rename the database
24
Backing-up a Database
Default filename of a
backup file is the name
of the database and the
current date
• Backing-up an Access file will produce a copy of your file
with a default filename
25
Compact and Repair
Compact and Repair is located
under the Manage menu
• Fixes problems due to inefficient file storage and
growth of a database
• Should be performed everyday
• Often decreases the file size by 50% or more
26
Filters
• Create a subset of records
• Do not change underlying table data
• Two types
• Filter by Selection
• Filter by Form
27
Filter by Selection
Table before filter by
Results of filter
selection
Filter by selection being applied
from pre-determined criteria
• Selects only the records that match pre-selected
criteria
28
Filter By Form
Inequity setting used in a Filter
by Form process
Selection of criteria
during Filter by form
process
• Allows the user to select criteria with which to filter
by
• Allows the specification of relationships in the criteria
29
Applying and Removing a
Filter
Toggle Filter icon
Filter icon in the Sort
and Filter group
• Once a filter is applied, the Toggle Filter icon will be
available
• The Toggle Filter icon can be used to apply and
remove the current filter as many times as desired
30
Sorting Table Data
Last Name field Last Name field
sorted ascending sorted descending
• Lists records in ascending or design order according to
one or more fields
31
Key Points
•Steps of create a database
•A database may have one or many tables with
relationships.
•A table should be uniquely identified with a primary
key.
Evaluation
•Name the main components of a datasheet?
•Explain the field properties?
•What are table relationships?
•What is a primary key?
References
1. Access. Retrieved May, 2010 from http://www.gcflearnfree.org/computer/
2. Cook, L.R. (2001). Computer Fundamentals-Understanding How They Work (1st
ed.).Vintage Press.
3. Ed Bott & Carl Siechert. (2001). Microsoft Windows XP Inside-out.
4. Joos, I. Whitman, N. Smith, M. Nelson, R. et al. (2006). Introduction to Computers
for Healthcare Professionals (4th ed.). London: Jones & Bartlett’s Publishers
International.
5. Morris M. & Charles, M. (2003). Logol Computer Designer Fundamentals. Prentice
Hall.
