Microsoft Office Access 2003 Training

1
Microsoft Office Access 2003 Training
Dutchess County BOCES Model Schools presents

• Get to know Access

2
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.
3
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).
4
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.

5
Lesson 1

• Benefits of using a database

6
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.
7
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.

• Can even make data entry more efficient and
  accurate.

Access unlocks the full value of your data.
8
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
9
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
10
Making friends with relations
Access creates relational databases. Data is
stored in separate tables by subject or task, but
the data is related.
In other words, information in one set of data is
associated with the applicable information in the
other set of data.
Relationships link data from individual tables to
increase its usefulness.
11
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.

• Consider the scenarios in which people will be
  entering data, looking up data, or reporting
  data. A little forethought can go a long way.

Relationships link data from individual tables to
increase its usefulness.
12
How a database is structured

• Access databases consist of objects, such as the
  following four important ones

• Tables store data in rows and columns.
• Queries retrieve and process data.

Objects the most important part of a database
13
How a database is structured

• Access databases consist of objects, such as the
  following four important ones

• Forms control data entry and data views.

• Reports summarize and print your data.

Objects the most important part of a database
14
Suggestions for practice

• Remember a database. Think about recent
  situations in which youve seen a database in
  use. Jot down your thoughts.
• 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.
• Imagine database activities. If youre planning
  to create a database, write down two or more
  situations in which youre likely to use the data.

15
Test 1, question 1

• Which of the following is not a database object?
  (Pick one answer.)

• Table.
• Report.
• Query.
• Worksheet.

16
Test 1, question 1 Answer

• Worksheet.

Although you can import worksheets from
Microsoft Excel into your database, worksheets
are not database objects.
17
Test 1, question 2

• Which of the following describes a relational
  database? (Pick one answer.)

• It provides a relationship between integers.
• It consists of separate tables of related data.
• It retrieves data related to its queries.

18
Test 1, question 2 Answer

• It consists of separate tables of related data.

The separate tables are associated with each
other through relationships.
19
Test 1, question 3

• What are the main building blocks of a database?
  (Pick one answer.)

• Lists.
• Queries.
• Tables.

20
Test 1, question 3 Answer

• Tables.

Every Access database contains one or more tables
that store your data.
21
Lesson 2

• Table that data

22
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.
23
Tables, the building blocks of databases

• Tables store data, so theyre essential building
  blocks of any 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.
Separate tables make up a database.
Each table contains rows called records and
columns called fields.
24
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.
25
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.
26
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.
27
Fields of data

• Field name. If an existing field name isnt
  descriptive enough, you can rename the field.

• Data type. A fields 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.

Settings include the field name, data type,
primary key, and field properties.
28
Fields of data

• Primary key. The primary key is a unique
  identifier for each record in your table.

• 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.

Settings include the field name, data type,
primary key, and field properties.
29
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 uniquein
other words, you wouldnt have two identical
records about Nancy Davolio in the same database.
30
Each record is unique

• But what if you have two employees named Nancy
  Davolio?

To distinguish one record from another, tables
can contain a primary key field. The primary key
is an identifier thats unique to each record.
Employee details should form one unique record.
31
Each record is unique

• Access can assign a numeric primary key that
  increases by 1 each time you add a record to a
  table.

This number continues to be associated with this
record, even if you add and delete other records
entered before this record in your database.
Employee details should form one unique record.
32
Its all relative
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.
Primary and foreign keys
33
Its all relative
A primary key separates similar information and
makes each record unique. It also brings
information together.
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.
Primary and foreign keys
34
Its all relative
When tables relate, the primary key of one table
becomes a foreign key of the other table.
For example, in this picture

• Employee ID appears in the Employees table as a
  primary key
• and in the Orders table as a foreign key.

Primary and foreign keys
35
Suggestions for practice

• Open a table.
• Look inside a table.

Online practice (requires Access 2003)
36
Test 2, question 1

• Data should be organized into tables based on
  (Pick one answer.)

• Field properties.
• Subjects associated with the data.
• Character lengths.

37
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.
38
Test 2, question 2

• A row in a table is also known as (Pick one
  answer.)

• A field.
• A record.
• A data type.

39
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.
40
Test 2, question 3

• A primary key is (Pick one answer.)

• A unique identifier that can be used to relate
  tables.
• The key that precedes the secondary key.
• The password that validates a database.

41
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.
42
Lesson 3

• Analyzing, viewing, and reporting data

43
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.
44
Queries

• Do you have questions that you want to answer
  with your data?

Queries can answer those questions by assembling
stored data from your database, or by performing
calculations with the data to provide further
information.
Results of a query
45
Queries
To answer questions, queries retrieve, filter,
sort, and assemble data on command.
Another important power of queries is to combine
the data from several tables into a single view.
Results of a query
46
Queries

• 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.

Results of a query
47
Queries

• 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.

Results of a query
A query can also remove data.
Note Use caution when executing queries that
change data, and consider backing up your data
first.
48
Forms

• Forms make data friendlier by enabling people to
  enter or view data in your database easily.

Forms

• Control and simplify data input.
• Make data from a table or a query easier to
  understand by presenting it in visually appealing
  designs.

Elements of a form
49
Forms

• Forms make data friendlier by enabling people to
  enter or view data in your database easily.

Forms

• Provide drop-down lists, instructions,
  navigational controls, and graphics to help users
  work with your data.

Elements of a form
50
Forms

• Common elements of forms

• Graphic elements, such as lines and rectangles,
  are stored in the form's design.

• Data comes from the fields in the underlying
  table or query.

Elements of a form
51
Forms

• Common elements of forms

• A calculation comes from an expression, which is
  stored in the form's design.

• Descriptive text is stored in the form's design.

Elements of a form
52
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 documents.
Reports convert data into formatted documents.
53
Printed reports
Reports come in various shapes and sizes, but
they are all designed to present your data in
print.
Reports provide methods to format the printed
appearance of your data in ways that are most
effective for your purpose.
Reports convert data into formatted documents.
54
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.

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.
Reports convert data into formatted documents.
55
Suggestions for practice

• View a query.
• Explore a form.
• Examine a report.

Online practice (requires Access 2003)
56
Test 3, question 1

• Which of the following best describes a query?
  (Pick one answer.)

• A query enables people to enter or view data in
  your database easily.
• A query summarizes and prints data.
• A query retrieves data that satisfies conditions
  that you set.

57
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.
58
Test 3, question 2

• Which of the following can you do in a report?
  (Pick one answer.)

• Perform calculations on your data.
• Add headings and formatting to your data.
• Group your data.
• All of the above.

59
Test 3, question 2 Answer

• All of the above.

You can also save a report and run it again with
new data, any time.
60
Test 3, question 3

• Why is it often more efficient to use a form than
  a table? (Pick one answer.)

• Because a form always presents your data in rows
  and columns.
• Because a form is always printed.
• Because a form simplifies data entry or viewing.

61
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.
62
Quick Reference Card

• For a summary of the tasks covered in this
  course, view the Quick Reference Card.

63
USING THIS TEMPLATE

• See the notes pane or view the full notes page
  (View menu) for detailed help on this template.