Transcript Microsoft® Office Access 2003 Training

CGI presents:
Microsoft Office
Access 2003 Training
®
Get to know Access
Course contents
• Overview: Database basics
• Lesson 1: Benefits of using a database
• Lesson 2: Table that data
• Lesson 3: Analyzing, viewing, and reporting data
Each lesson includes a list of suggested tasks and a set of
test questions.
Get to know Access
Overview: Database basics
Do you want to manage a large
collection of information? A database
helps you enter data easily, find it
quickly, use it to create labels or
mailings, and summarize it in printed
and online reports.
This course will cover the benefits and
structure of an Access database and
get you familiar with ways to get data
into one and out of one (such as using
queries and reports).
Get to know Access
Course goals
• Learn the difference between a database and a list.
• Get familiar with tables, fields, records, and primary keys.
• Find out how to use the four main items, called objects,
that most databases contain.
Get to know Access
Lesson 1
Benefits of using a database
Benefits of using a database
A database is much more than just
a list or table. A database:
• Gives you true command of your
data, enabling you to retrieve it,
sort it, analyze it, summarize it,
and report results in moments.
Access unlocks the full
value of your data.
Get to know Access
Benefits of using a database
A database is much more than just
a list or table. A database:
• Can combine data from various
files, so that you never have to
enter information twice.
Access unlocks the full
value of your data.
• Can even make data entry more
efficient and accurate.
Get to know Access
Better than a collection of lists
Scenario:
You're the secretary of a large hiking
club with a lot of lists to manage.
One club member, who appears on a
number of lists, changes her
e-mail for the second time this year.
A key Access benefit:
avoiding the complications
of multiple lists
Get to know Access
Better than a collection of lists
With only a set of lists, you'd have
to change that information
everywhere it occurs.
But with a well-structured database,
you'd have to change it only once.
The database takes care of
everything else.
A key Access benefit:
avoiding the complications
of multiple lists
Get to know Access
Making friends with relations
Access creates relational
databases. Data is stored in
separate tables by subject or task,
but the data is related.
Relationships link data
from individual tables to
increase its usefulness.
In other words, information in one
set of data is associated with the
applicable information in the other
set of data.
Get to know Access
Making friends with relations
Database planning tips:
• To make the most of your
database, set up the tables of data
to reflect the subjects and tasks
associated with your data.
Relationships link data
from individual tables to
increase its usefulness.
• Consider the scenarios in which
people will be entering data,
looking up data, or reporting data.
A little forethought can go a long
way.
Get to know Access
How a database is structured
Access databases consist of
objects, such as the following four
important ones:
• Tables store data in rows and
columns.
Objects: the most
important part of a
database
• Queries retrieve and process
data.
Get to know Access
How a database is structured
Access databases consist of
objects, such as the following four
important ones:
• Forms control data entry and data
views.
Objects: the most
important part of a
database
• Reports summarize and print your
data.
Get to know Access
Suggestions for practice
1. Remember a database. Think about recent situations in
which you’ve seen a database in use. Jot down your
thoughts.
2. Note database uses. How did people use the database:
to look up your customer information? Scan price tags
into the register or computer? And so on.
3. Imagine database activities. If you’re planning to
create a database, write down two or more situations in
which you’re likely to use the data.
Get to know Access
Test 1, question 1
Which of the following is not a database object?
(Pick one answer.)
1. Table.
2. Report.
3. Query.
4. Worksheet.
Get to know Access
Test 1, question 1: Answer
Worksheet.
Although you can import worksheets from Microsoft® Excel®
into your database, worksheets are not database objects.
Get to know Access
Test 1, question 2
Which of the following describes a relational
database? (Pick one answer.)
1. It provides a relationship between integers.
2. It consists of separate tables of related data.
3. It retrieves data related to its queries.
Get to know Access
Test 1, question 2: Answer
It consists of separate tables of related data.
The separate tables are associated with each other through
relationships.
Get to know Access
Test 1, question 3
What are the main building blocks of a
database? (Pick one answer.)
1. Lists.
2. Queries.
3. Tables.
Get to know Access
Test 1, question 3: Answer
Tables.
Every Access database contains one or more tables that
store your data.
Get to know Access
Lesson 2
Table that data
Table that data
All databases in Access contain at
least one table.
In this lesson, we'll show what a
table is made of, and how you can
structure tables to fit your data.
Tables organize your
data.
Get to know Access
Tables, the building blocks of databases
Tables store data, so they’re
essential building blocks of any
database.
Separate tables make up
a database.
A database should have a separate
table for every major subject, such
as employee records or customer
orders. Data should not be
duplicated in multiple tables.
Each table contains rows called
records and columns called fields.
Get to know Access
Tables, the building blocks of databases
A record is a collection of facts
about a particular person, event, CD,
or other item of interest.
Each row constitutes a
record.
For example, Nancy Davolio and her
employment details are a record in
an Employees table. Speedy Express
and its contact information are a
record in a Shippers table.
Get to know Access
Tables, the building blocks of databases
A field is a single kind of fact that
may apply to each person, event, or
other record.
The Phone and other
columns are fields.
For example, Postal Code is a field in
an Employees table. Phone is a field
in a Shippers table.
Get to know Access
Fields of data
The fields in your database have
settings that determine the type of
data they can store, how the data is
displayed, and what you can do with
the data.
Settings include the field
name, data type, primary
key, and field properties.
Get to know Access
Fields of data
1. Field name. If an existing field
name isn’t descriptive enough,
you can rename the field.
Settings include the field
name, data type, primary
key, and field properties.
2. Data type. A field’s data type
limits and describes the kind of
information you can enter in a
field. It also determines the
actions you can perform on a field
and how much memory the data
uses.
Get to know Access
Fields of data
3. Primary key. The primary key is
a unique identifier for each record
in your table.
Settings include the field
name, data type, primary
key, and field properties.
4. Field properties. These are a
set of characteristics that provide
additional control over the details
inside the field and that make it
easier to enter and manage data.
Get to know Access
Each record is unique
You may have heard that no two
snowflakes are alike. This
characteristic also applies to records
in a well structured database.
Employee details should
form one unique record.
Each record in each table should be
unique—in other words, you
wouldn’t have two identical records
about Nancy Davolio in the same
database.
Get to know Access
Each record is unique
But what if you have two employees
named Nancy Davolio?
Employee details should
form one unique record.
To distinguish one record from
another, tables can contain a
primary key field. The primary key
is an identifier that’s unique to each
record.
Get to know Access
Each record is unique
Access can assign a numeric primary
key that increases by 1 each time
you add a record to a table.
Employee details should
form one unique record.
This number continues to be
associated with this record, even if
you add and delete other records
entered before this record in your
database.
Get to know Access
It’s all relative
Primary and foreign keys
Primary keys allow you to tap into
the power of a relational database,
instead of working with a fistful of
repetitive lists that are hard to
maintain and cannot cooperate.
Get to know Access
It’s all relative
A primary key separates similar
information and makes each record
unique. It also brings information
together.
Primary and foreign keys
You relate one table to another
using a primary key. This is how
tables share data, and how you can
avoid repeating information in both
the tables.
Get to know Access
It’s all relative
When tables relate, the primary key
of one table becomes a foreign key
of the other table.
For example, in this picture:
Primary and foreign keys
1. Employee ID appears in the
Employees table as a primary
key…
2. …and in the Orders table as a
foreign key.
Get to know Access
Suggestions for practice
1. Open a table.
2. Look inside a table.
Online practice (requires Access 2003)
Get to know Access
Test 2, question 1
Data should be organized into tables based on:
(Pick one answer.)
1. Field properties.
2. Subjects associated with the data.
3. Character lengths.
Get to know Access
Test 2, question 1: Answer
Subjects associated with the data.
A well structured database has a table for each subject to
which data belongs, such as Employees, Students, or
Products.
Get to know Access
Test 2, question 2
A row in a table is also known as: (Pick one
answer.)
1. A field.
2. A record.
3. A data type.
Get to know Access
Test 2, question 2: Answer
A record.
A row is one record in a table; it contains a collection of
data about something, such as a product or employee. A
record includes data from several fields that might have
names like Unit Price or Employee ID.
Get to know Access
Test 2, question 3
A primary key is: (Pick one answer.)
1. A unique identifier that can be used to relate tables.
2. The key that precedes the secondary key.
3. The password that validates a database.
Get to know Access
Test 2, question 3: Answer
A unique identifier that can be used to relate tables.
A primary key distinguishes one record from another and
links data in one table to data in other tables.
Get to know Access
Lesson 3
Analyzing, viewing, and
reporting data
Analyzing, viewing, and reporting data
Tables are great for storing data.
But to get the full benefit of Access,
you need to understand other
Access database objects.
In this lesson, we'll tell you more
about queries, forms, and reports,
and how they can help you.
A database window
opens to show the list of
database objects.
Get to know Access
Queries
Do you have questions that you
want to answer with your data?
Results of a query
Queries can answer those questions
by assembling stored data from your
database, or by performing
calculations with the data to provide
further information.
Get to know Access
Queries
To answer questions, queries
retrieve, filter, sort, and assemble
data on command.
Results of a query
Another important power of queries
is to combine the data from several
tables into a single view.
Get to know Access
Queries
Results of a query
The picture illustrates the results of
a query. This query extracted the
names of employees who live in the
United Kingdom from a larger
Employees table.
Get to know Access
Queries
Results of a query
When a query finds data and shows
it to you, it can also process that
data according to your instructions
and perform calculations using the
data.
A query can also remove data.
Note: Use caution when executing queries
that change data, and consider backing up
your data first.
Get to know Access
Forms
Forms make data friendlier by
enabling people to enter or view
data in your database easily.
Forms:
• Control and simplify data input.
Elements of a form
• Make data from a table or a query
easier to understand by presenting
it in visually appealing designs.
Get to know Access
Forms
Forms make data friendlier by
enabling people to enter or view
data in your database easily.
Forms:
Elements of a form
• Provide drop-down lists,
instructions, navigational controls,
and graphics to help users work
with your data.
Get to know Access
Forms
Common elements of forms:
1. Graphic elements, such as lines
and rectangles, are stored in the
form's design.
Elements of a form
2. Data comes from the fields in the
underlying table or query.
Get to know Access
Forms
Common elements of forms:
3. A calculation comes from an
expression, which is stored in the
form's design.
Elements of a form
4. Descriptive text is stored in the
form's design.
Get to know Access
Printed reports
Do you routinely report sales
figures? Provide a catalog of your
inventory? Print out labels for CDs,
DVDs, and videos at home? Produce
invoices and receipts?
Reports convert data into
formatted documents.
Reports convert data into
documents.
Get to know Access
Printed reports
Reports come in various shapes and
sizes, but they are all designed to
present your data in print.
Reports convert data into
formatted documents.
Reports provide methods to format
the printed appearance of your data
in ways that are most effective for
your purpose.
Get to know Access
Printed reports
Using reports, you can group your
data, perform calculations on it, and
add headings and other formatting
to make it more meaningful and
easier to read.
Reports convert data into
formatted documents.
After you've created a report, you
can save its format so that it looks
the same each time you print it,
however the data changes.
Get to know Access
Suggestions for practice
1. View a query.
2. Explore a form.
3. Examine a report.
Online practice (requires Access 2003)
Get to know Access
Test 3, question 1
Which of the following best describes a query?
(Pick one answer.)
1. A query enables people to enter or view data in
your database easily.
2. A query summarizes and prints data.
3. A query retrieves data that satisfies conditions that
you set.
Get to know Access
Test 3, question 1: Answer
A query retrieves data that satisfies conditions that you set.
Queries retrieve, filter, sort, and assemble data on
command. They can also combine data from several tables
into a single view.
Get to know Access
Test 3, question 2
Which of the following can you do in a report?
(Pick one answer.)
1. Perform calculations on your data.
2. Add headings and formatting to your data.
3. Group your data.
4. All of the above.
Get to know Access
Test 3, question 2: Answer
All of the above.
You can also save a report and run it again with new data,
any time.
Get to know Access
Test 3, question 3
Why is it often more efficient to use a form than
a table? (Pick one answer.)
1. Because a form always presents your data in rows
and columns.
2. Because a form is always printed.
3. Because a form simplifies data entry or viewing.
Get to know Access
Test 3, question 3: Answer
Because a form simplifies data entry or viewing.
A form can provide instructional text, graphics, and controls
to make it easier to enter or view data.
Get to know Access
Quick Reference Card
For a summary of the tasks covered in this course, view the
Quick Reference Card.
Get to know Access