Transcript Chapter 7 - ShareStudies.com

Chapter 7
Query-by-Example
Transparencies
© Pearson Education Limited 1995, 2005
Chapter 7 - Objectives
 The
main features of Query-By-Example (QBE).
 The
types of queries provided by the Microsoft
Access DBMS QBE facility.
 How
to use QBE to build queries to select fields
and records.
 How
to use QBE to target single or multiple tables.
2
© Pearson Education Limited 1995, 2005
Chapter 7 - Objectives
 How
to perform calculations using QBE.
 How
to use advanced QBE facilities including
parameter, find matched, find unmatched,
crosstab, and autolookup queries.
 How
to use QBE action queries to change
content of tables.
3
© Pearson Education Limited 1995, 2005
Query-by-Example (QBE)

Visual approach for accessing information in a
database through use of query templates.

Example values are entered into template to
represent what access to database is to achieve,
such as the answer to a query.

Originally developed by IBM in 1970s and has
proved so popular that QBE (or similar) is now
provided by most DBMSs.

When user constructs a QBE - in background,
DBMS creates an equivalent SQL statement.
4
© Pearson Education Limited 1995, 2005
Query-by-Example (QBE)

Allows user to:
- Ask questions about data in one or more tables.
- Specify the fields we want in the answer.
- Select records according to some criteria.
- Perform calculations on the data in tables.
- Insert and delete records.
- Modify values of fields.
- Create new fields and tables.
5
© Pearson Education Limited 1995, 2005
Introduction to Microsoft Access
6
© Pearson Education Limited 1995, 2005
Summary of Microsoft Access Query Types
7
© Pearson Education Limited 1995, 2005
Introduction to Microsoft Access Queries
8
© Pearson Education Limited 1995, 2005
Building Select Queries using QBE Specifying Criteria
9
© Pearson Education Limited 1995, 2005
Building Select Queries using QBE Specifying Criteria
10
© Pearson Education Limited 1995, 2005
Building Select Queries using QBE Specifying Criteria
11
© Pearson Education Limited 1995, 2005
Creating Multi-table Queries
12
© Pearson Education Limited 1995, 2005
Calculating Totals
13
© Pearson Education Limited 1995, 2005
Calculating Totals
14
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Parameter Query
15
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Crosstab Query
16
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Crosstab Query
17
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Find Matched Query
18
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Find Unmatched Query
19
© Pearson Education Limited 1995, 2005
Using Advanced Queries - Autolookup Query
20
© Pearson Education Limited 1995, 2005
Changing Content of Tables - Make-Table
Action Query
21
© Pearson Education Limited 1995, 2005
Changing Content of Tables - Delete Action
Query
22
© Pearson Education Limited 1995, 2005
Changing Content of Tables - Update
Action Query
23
© Pearson Education Limited 1995, 2005
Changing Content of Tables - Append
Action Query
24
© Pearson Education Limited 1995, 2005
Changing Content of Tables - Append
Action Query
25
© Pearson Education Limited 1995, 2005