Transcript Microsoft Office XP: Introductory Course

INTRODUCTORY MICROSOFT
ACCESS
Lesson 4 – Finding and Ordering Data
Microsoft Office 2003:
Introductory
1
Pasewark & Pasewark
Access – Lesson 4
Objectives







2
Find data in a database.
Query a database.
Use filters.
Sort a database.
Index a database.
Establish relationships in a database.
Create a query from related tables.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Terms Used in This Lesson







3
And operator
Ascending sort
Descending sort
Filter
Indexing
Multitable query
One-to-many
relationship
Microsoft Office 2003: Introductory







Or operator
Primary key
Query
Referential integrity
Relationship
Search criteria
Subdatasheet
Pasewark & Pasewark
Access – Lesson 4
Using Find
4


The Find command is the easiest way to
locate data in a database.
Open the Edit menu
and choose Find, or
click the Find button
on the toolbar. The
Find and Replace
dialog box will open.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Creating a Query in Design View
5



A query lets you combine criteria to perform
complex searches and allows you to display
selected fields.
To create a query, open the database and
click Queries on the Objects bar.
Click New to create a new query. The New
Query dialog box appears.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Creating a Query in Design
View (cont.)
6



Choose the Design View option.
Choose the table, and select the fields to be
used in the query.
Enter the search criteria, and save when
finished.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Using the Simple Query Wizard




7
The Simple Query Wizard asks you
questions and then creates a query based on
your answers.
Choose Queries on the Objects bar and then
click New.
In the New Query dialog box, choose Simple
Query Wizard.
Follow the screens to create the query.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Using Filters



A filter is similar to a query; however, it
displays all fields and cannot be saved.
To create a filter, a table must be open.
Open the Records menu, choose Filter, and
select one of the Filter types:
–
–
–
–
8
Filter by Form
Filter By Selection
Filter Excluding Selection
Advanced/Filter Sort
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Sorting a Database
9


To sort a table, open the table and
place the insertion point in the field
by which you want to sort.
Click either the Sort Ascending or
Sort Descending button.
–
–
Ascending Sort – arranges records
from A to Z or smallest to largest.
Descending Sort – arranges
records from Z to A or largest to
smallest.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Setting a Primary Key





10
A primary key is a special field that assigns
a unique identifier to each record.
Primary keys must be set before creating
table relationships.
To designate a field as the primary key, go to
Design view.
Choose the field by clicking the row selector.
Click the Primary Key button on the toolbar.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Establishing Relationships
Within a Database
11



You can create queries, forms, and reports to
display information from several tables at
once.
Create a relationship between tables that
contain a common field.
Common fields must have the same data
type, although they can have different field
names.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Establishing Relationships
Within a Database (cont.)
12

Enforce referential integrity between tables
when creating a relationship to ensure a valid
relationship and prevent invalid data from
being entered.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Creating a Multitable Query





13
A multitable query displays the shared
information from related tables at once.
To create a new query, open the database,
choose Queries on the Objects bar, and click
the New button.
In the New Query dialog box, choose the
Design view option.
Add the related tables to the query window.
Choose the fields and enter the search criteria.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Summary



14
The Find command is the easiest way to locate
data in the database. The Find command searches
the database for specified information.
Queries allow more complex searches. A query
allows you to search records using multiple and
complex criteria and allows you to display selected
fields.
A filter is similar to a query; however, it displays all
fields and cannot be saved.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Summary (cont.)
15


Sorting is an important part of working with a
database since you will often need records to
appear in a specific order.
A primary key is a special field that assigns a
unique identifier to each record. Primary keys
must be set before creating table
relationships.
Microsoft Office 2003: Introductory
Pasewark & Pasewark
Access – Lesson 4
Summary (cont.)
16


By defining relationships between the
different tables within a database, you can
create queries, forms, and reports to display
information from several tables at once.
Matching data in key fields sets up a
relationship.
A multitable query displays the shared
information from related tables in a database.
Microsoft Office 2003: Introductory
Pasewark & Pasewark