Transcript Access Chapter 3 PowerPoint

Access Lesson 3
Creating Queries
Microsoft Office 2010
Introductory
1
Objectives

Access Lesson 3

2





Create a query using a Wizard.
Sort and filter data in a datasheet.
Create a query in Design view.
Create relationships in a database.
Create a query based on more than one table.
Use operators in a condition in a query.
Calculate data using a query.
Microsoft Office 2010 Introductory
Creating a Query with the Simple
Query Wizard
Access Lesson 3


A query is a database object that lets you
ask the database about the data it contains.
The result of a query is a datasheet that
includes the records you asked to see.
Specifications in a query are called
conditions.
–
–
3
A condition is also called a criterion.
When the condition has two or more parts to it,
the two conditions are called criteria.
Microsoft Office 2010 Introductory
Creating a Query with the Simple
Query Wizard (continued)
Access Lesson 3

4


A query is based on a table or another query
(or multiple tables/queries)
When you open a query object, you run the
query. Running a query displays a datasheet
with only the records and fields that you
asked to see.
The Simple Query Wizard is an easy way to
create a query.
Microsoft Office 2010 Introductory
Creating a Query with the Simple
Query Wizard (continued)
First Simple Query Wizard dialog box
Access Lesson 3

5
Microsoft Office 2010 Introductory
Creating a Query with the Simple
Query Wizard (continued)
Access Lesson 3

6

A detail query shows every field in each
record.
A summary query lets you summarize
relevant data, such as adding the field values
in a column that stores price data.
Microsoft Office 2010 Introductory
Lets Create A Query

Access Lesson 3






7
Open Up The Company Database
Select the Create Tab
Next Select Query Wizard
Select Fields That Will Be Used To Perform
A Query Of The Data and Questions Below…
Who has a zip code of 79835?
Who makes less than 2000 in salary?
Who are the managers and what is their
Microsoft Office 2010 Introductory
salary?
Sorting Data
Access Lesson 3

8

When you view field values in ascending or
descending order from A to Z or from
smallest to largest, you apply a sort to the
field.
Sorting a field in ascending order arranges
records from A to Z, or from smallest to
largest. Sorting a field in descending order
arranges records from Z to A, or from largest
to smallest.
Microsoft Office 2010 Introductory
Filtering Data
Access Lesson 3

9

A filter temporarily displays records in a
datasheet based on the condition that you
specify.
You can use different types of filters to
display the data you need. When you use
Filter By Selection, you select a field value,
and then click the Selection button in the Sort
& Filter group on the Home tab.
Microsoft Office 2010 Introductory
Lets Filter
Access Lesson 3





10
Using The Company Database(also on my
website)
Filter by selection
Show everyone that works in the advertising
department
Everyone that has a first name that begins
with an “S”
Everyone that was born in 1968
Microsoft Office 2010 Introductory
Filtering Data (continued)
Access Lesson 3

11

You can use Filter By Form to display
records that contain one or more values
based on the values stored in one or more
fields. Click the Advanced button in the Sort
& Filter group on the Home tab.
Click the Toggle Filter button in the Sort &
Filter group on the Home tab to display only
records in the datasheet that match the filter.
Microsoft Office 2010 Introductory
Filtering Data (continued)
Access Lesson 3

12

An easy way to sort and filter data is to use
an AutoFilter.
An AutoFilter is a menu that opens when you
click the arrow on the right side of a field
selector, and contains options for:
–
–
Sorting data and clearing filters.
Using Filter By Selection and Filter By Form.
Microsoft Office 2010 Introductory
Filtering Data (continued)
AutoFilter for the Product Name field (a Text field)
Access Lesson 3

13
Microsoft Office 2010 Introductory
Creating a Query in Design View
Access Lesson 3

14

For a query datasheet, you have more
sorting and filtering options when you create
or modify a query in Design view.
In the Query Design window, you build and
change the query using the design grid.
Microsoft Office 2010 Introductory
Creating a Query in Design View
(continued)
Access Lesson 3

15
The Query window in Design view is divided
into two parts.
–
–

The top shows the field list for the table you
included in the query design.
The bottom contains a design grid that allows you
to specify fields, conditions, and sort orders.
A query can contain one, some, or all of the
fields in the table. You can add the fields in
any order to the design grid.
Microsoft Office 2010 Introductory
Creating a Query in Design View
(continued)
Access Lesson 3

16


You can set a sort order for a field using the
field's Sort box in the design grid.
You can run a query by clicking the Run
button in the Results group on the Query
Tools Design tab. When you run a query, the
results appear in a query datasheet.
To add a condition to a field, click in the
field's Criteria box, and then type the
condition.
Microsoft Office 2010 Introductory
Creating Table Relationships
Access Lesson 3



17
The feature that lets you connect the data in
the tables is a relationship.
To create a relationship between two tables,
you must design the tables so they contain a
common field. A common field is a field that
appears in both tables, has the same data
type, and contains the same values.
A common field is also called a matching
field.
Microsoft Office 2010 Introductory
Creating Table Relationships
(continued)
Access Lesson 3

18
The most common relationship is a one-tomany relationship.
–

One record in the first table can match many
records in the second table.
The common field in the related table is
called a foreign key when it is used in a
relationship.
Microsoft Office 2010 Introductory
Creating Table Relationships
(continued)
Access Lesson 3

19

When you relate tables, Access uses a set of
rules to ensure that there are matching
values in the common field used to form the
relationship. This set of rules is called
referential integrity.
Referential integrity protects the data in the
tables to make sure that data is not
accidentally deleted or changed, resulting in
inconsistent data.
Microsoft Office 2010 Introductory
Creating Table Relationships
(continued)
Relationships window after creating a one-to-many relationship
Access Lesson 3

20
Microsoft Office 2010 Introductory
Creating a Multitable Query
Access Lesson 3

21

Queries based on more than one table are
sometimes called multitable queries.
After you add two related tables to the query
design, a join line shows the relationship.
–
–
The join line connects the common field used to
relate the tables.
It defines the type of relationship by using the "1"
to represent the "one" and the infinity symbol to
represent the "many" side of the relationship.
Microsoft Office 2010 Introductory
Using Operators in a Condition
Access Lesson 3

22



In an exact match condition the records
must contain the specified value.
In a range-of-values condition the record
must match a range of values.
The And operator selects records that match
all of two or more conditions in a query.
The Or operator selects records that match at
least one of two or more conditions in a query.
Microsoft Office 2010 Introductory
Using Operators in a Condition
(continued)
Relational operators
Access Lesson 3

23
Microsoft Office 2010 Introductory
Calculating Data
Access Lesson 3

24
Access provides two ways to calculate data
using a query:
–
–
Total Row: Use the Total row to count the
number of values in a column. The Total row
includes additional functions for values.
Calculated Field: A field with a value calculated
using other fields is called a calculated field. The
calculation is called an expression.
Microsoft Office 2010 Introductory
Access Lesson 3
Summary
25
In this lesson, you learned:
 A query is a database object that lets you ask the
database a question about the data it contains. You
can create a query quickly and easily using the
Simple Query Wizard, which asks you about the data
you want to see and lets you select options in dialog
boxes.
 You can change the way data is sorted in a datasheet
by applying an ascending or a descending sort order
to one of the fields.
Microsoft Office 2010 Introductory
Summary (continued)
Access Lesson 3

26
You can use a filter in a datasheet to temporarily display
records in a datasheet based on a condition that you
specify. Filter By Selection lets you select a field value or
part of a field value in a datasheet and then filter out all
records that do not match the filter. Filter By Form lets
you display records that match a value you select in a
field. An AutoFilter opens when you click the arrow on a
field selector. You can use an AutoFilter to sort and filter
data. You can also move and sort fields in Design view.
To run a query, click the Run button in the Results group
on the Query Tools Design tab.
Microsoft Office 2010 Introductory
Summary (continued)
Access Lesson 3

27

When you need to create a query that uses conditions to
select records, create the query in Query Design view.
Use the Relationships window to create relationships
between tables in a database by joining tables with a field
that contains matching field values. A one-to-many
relationship exists when one record in the primary table
matches zero, one, or many records in the related table.
Referential integrity is the set of rules that Access uses to
protect data in the tables and to make sure that data is
not accidentally deleted or changed.
Microsoft Office 2010 Introductory
Summary (continued)
Access Lesson 3

28

A multitable query is a query that is based on more than one
table.
When you need to use a query to search for records that
match a range of values, use a relational operator in the
query design. When you need to select records that match
all of two or more conditions in a query, use the And
operator by placing the criteria in the same Criteria row in
the design grid. When you need to select records that match
at least one of two or more conditions in a query, use the Or
operator by placing the first condition in the Criteria row and
the second condition in the or row in the design grid.
Microsoft Office 2010 Introductory
Summary (continued)
In Access, you can perform calculations by using the
Total row in a datasheet, or by creating a calculated field
in the design grid in Query Design view.
Access Lesson 3

29
Microsoft Office 2010 Introductory