Transcript Level 3 - High Point University

XP
Chapter 3
Analyzing Data For Effective Decision
Making
“The human problems which I deal with every day—concerning
employees as well as customers—are the problems that fascinate
me, that seem important to me.”
—Hortense Odlum
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
1
Level 3 Objectives: Exploring XP
Advanced Queries and Queries Written
in Structured Query Language
•
•
•
•
Calculate and restructure data to improve analysis
Examine and create advanced types of queries
Make decisions in a query using the immediate IF
(IIF) function
Develop queries using SQL
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
2
XP
Analyzing Query Calculations
•
Crosstab queries
 Special type of totals query
 Performs aggregate function calculations on values of
one database field
 Determine exactly how summary data appears in
results
 Calculate and restructure data
• Analyze it more easily
 Work especially well with time-series data
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
3
XP
Creating a Crosstab Query
•
To create use
 Crosstab query wizard
• Often need to create query first
 Or design view
• Start with select query that includes numeric values or
summary calculations
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
4
XP
Crosstab Field Settings
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
5
XP
Modifying Data Using Queries
•
Action queries
 Modify data in table
 Add records to or delete records from table
 Create new table
•
Backup data before using action query
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
6
XP
Access Action Queries
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
7
XP
Process for Archiving Data
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
8
Archiving Data with Make-tableXP
Queries
•
Make-table query
 Creates table from some or all of the fields and records
in existing table or query
 Access does not delete selected fields and records
from existing table
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
9
Adding Records to Tables withXP
Append Queries
•
Append query
 Select records from one or more tables by setting




Chapter 3
criteria
Add those records to end of another table
Selected records also remain in original tables
Table to which records added must already exist
Also use to bring data from another source into
database
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
10
XP
Removing Records from Tables
with Delete Queries
•
Delete query
 Removes information from table
 Based on specified criteria
 All records meeting criteria permanently removed from
table
•
Create select query first
 Convert to delete query
•
Cascading deletes
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
11
Updating Data with an UpdateXP
Query
•
Update query
 Changes values of data in one or more existing tables
 Create select query first
• Change type to update query
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
12
XP
Making Decisions in Queries
•
IF statement
 Tests condition
 Takes one action if condition true
 Takes another action if condition false
•
IIF function
 Make if decision
 Format
• IIF(condition to test, what to do if true, what to do if false)
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
13
Customizing Queries Using
Structured Query Language
•
XP
Access designed as database management system
(DBMS) for
 Small businesses
 Or departments within large businesses
•
Structured query language
 Common query language of most DBMSs
 Use to query, update, and manage relational
databases
•
Create query in design view
 Access translates entries and criteria into SQL
statements
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
14
Customizing Queries Using
Structured Query Language
(continued)
•
•
XP
View statements by switching from Design view to
SQL view
SELECT statement defines
 What data query should retrieve from database
 How it should present data
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
15
XP
Exploring the Components of an
SQL Query
•
Keywords
 Use to construct SQL statements
•
Most developers place each statement on separate
line
 To make SQL code easy to read
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
16
XP
Common SQL Keywords
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
17
XP
Level 3 Summary
•
Action queries
 Make new tables
 Append data
 Delete data
 Update data
•
•
IFF function
SQL
 Use SQL view to edit SQL directly
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
18
XP
Chapter Summary
•
Queries retrieve data from one or more tables
 Action queries update data
 Perform calculations
 Make decisions using IFF function
•
SQL
 Used to interact with relational databases
 Use SQL view to view/edit SQL statements generated
by Access
Chapter 3
Succeeding in Business with Microsoft Office
Access 2003: A Problem-Solving Approach
19