Transcript kjjhghgff

Access Lesson 3
Creating Queries
Microsoft Office 2007:
Introductory
1
Pasewark & Pasewark
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 table based on more than one
table.
Use operators in a condition in a query.
Calculate data using a query.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Vocabulary

Access – Lesson 3








3
And operator
Ascending
AutoFilter
Calculated field
Common field
Condition
Criteria
Criterion
Descending
Pasewark & Pasewark









Detail query
Exact match condition
Expression
Filter
Filter By Form
Filter By Selection
Foreign key
Join line
Matching field
Microsoft Office 2007: Introductory
Vocabulary (continued)

Access – Lesson 3

4





Multitable query
One-to-many
relationship
Or operator
Primary table
Query
Range-of-values
condition
Referential integrity
Pasewark & Pasewark








Related table
Relationship
Run
Simple Query Wizard
Sort
Subdatasheet
Summary query
Total row
Microsoft Office 2007: Introductory
Creating a Query with the Simple
Query Wizard
Access – Lesson 3

5


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.
A condition (also called a criterion) is a way of telling
the query which data you are interested in seeing.
When the condition has two or more parts, such as
customers who have ordered a specific part and live
in a certain zip code, the two conditions are called
criteria.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating a Query with the Simple
Query Wizard (continued)
Access – Lesson 3

6


A query is based on a table, and some queries are
based on more than one table. When you open a
query object, you run the query.
An easy way to create a query is to use the Simple
Query Wizard, which asks you what data you want to
see by letting you select options in dialog boxes.
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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Sorting Data
Access – Lesson 3

7

When you view a table or query datasheet,
the records might not appear in the order that
you need.
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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Filtering Data

Access – Lesson 3

8


A filter temporarily displays records in a datasheet based on the
condition that you specify.
When you use Filter By Selection, you select a field value (or
part of a field value) in a datasheet, and then click the Selection
button in the Sort & Filter group on the Home tab.
You can use Filter By Form when you need to display records
that contain one or more values based on the values stored in
one or more fields.
An easy way to sort and filter data using these same options 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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating a Query in Design View
Access – Lesson 3

9


For a table datasheet, using a sort or a filter is your
only method to change the way data is displayed in a
table or query datasheet.
For a query datasheet, however, you have more
sorting and filtering options if you create a query in
Design view. In the Query Design window, you build
and change the query using the query design grid.
Moving and Sorting Fields in Design View: You can
select a sort order for a field by setting the sort order
in the field’s Sort box in the design grid.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating a Query in Design View
(continued)
Access – Lesson 3

10

Adding a Condition to a Field: If the question is
“Which orders contain an order for Product ID
1701?” then you need to add a condition to the query
design before you run it. To add a condition to a field,
click in the field’s Criteria box, and then type the
condition.
Running a Query: You can run the 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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating Table Relationships
Access – Lesson 3

11


When a database contains more than one table, the
feature of the database management system 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 matching
field.
A matching field is a field that appears in both tables,
has the same data type, and contains the same
values. A matching field is also called a common
field because it is common in both tables involved in
the relationship.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating Table Relationships
(continued)
Access – Lesson 3

12


You can create different types of relationships
depending on the data used in the tables you are
relating.
The most common relationship is a one-to-many
relationship. Other types include one-to-one and
many-to-many.
In a one-to-many relationship, one record in the first
table (called the primary table) can match many
(actually, zero, one or many) records in the second
table (called the related table).
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating Table Relationships
(continued)
Access – Lesson 3

13

The common field in the related table is called a
foreign key when it is used in a relationship. In the
primary table, the common field is usually the table’s
primary key.
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, both at
the time you create the relationship and as you enter
data in the tables after you create the relationship.
This set of rules is called referential integrity.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating Table Relationships
(continued)
Access – Lesson 3

14

Referential integrity protects the data in the tables to
make sure that data is not accidentally deleted or
changed, resulting in inconsistent data.
Viewing Related Records: After creating a one-tomany relationship between two tables, you can view
the data in the related table by opening the
datasheet for the primary table. Clicking the expand
indicator opens a subdatasheet, which contains the
related records in the related table. You can use the
subdatasheet to make changes to the related
records.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Creating a Multitable Query

Access – Lesson 3

15

Queries that are 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 between the tables.
The join line connects the common field used to
relate the tables. It also defines the type of
relationship by using the “1” to represent the “one”
side of the relationship and the infinity symbol to
represent the “many” side of the relationship.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Using Operators in a Condition
Access – Lesson 3

16



If a query selects records for products that are out of stock, this
is called an exact match condition because the records must
contain the value 0 in the Units In Stock field to be displayed in
the query datasheet.
Another type of condition causes a record to be displayed in the
query datasheet when the record matches a range of values.
This is called a range-of-values condition.
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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Calculating Data

Access – Lesson 3

17

You can use a query to perform calculations on the data
selected by a query. Access provides two ways to calculate data
in a query: using the Total row and creating a calculated field.
Total Row: You can use the Total row to get a quick count of the
number of values in a column. When the field contains numeric
data, such as numbers or currency values, the Total row also
includes functions that calculate the total of the values in a
column or the average, minimum, or maximum value in a
column.
Calculated Field: When a field displays a value that is
calculated using other fields in the query, it is called a calculated
field. The calculation itself, such as [Current Date] – [Birth Date],
is called an expression.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary
Access – Lesson 3

18


A query is a database object that lets you ask the
database about the data it contains. You can create a
query quickly using the Simple Query Wizard.
Change the way data is sorted in a datasheet by
applying an ascending or a descending sort order to
one of the fields.
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
in a datasheet and then filter out records that do not
match the filter. Filter By Form lets you display records
that match a value you select in a field.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary (continued)

Access – Lesson 3

19


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.
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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary (continued)
Access – Lesson 3

20


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.
A multitable query is a query that is based on two or
more tables.
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.
Pasewark & Pasewark
Microsoft Office 2007: Introductory
Summary (continued)
Access – Lesson 3

21


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 criteria in different Criteria rows
in the design grid.
In Access, you can perform calculations by using the
Total row in a datasheet or by creating a calculated
field in the design grid.
Pasewark & Pasewark
Microsoft Office 2007: Introductory