analyzing data for effective decision making
DESCRIPTION
Analyzing Data For Effective Decision Making. Chapter 3. “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. - PowerPoint PPT PresentationTRANSCRIPT
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
1
XP
Analyzing Data For Effective Decision Making
Chapter 3
“The human problems which I deal with every day—concerningemployees as well as customers—are the problems that fascinateme, that seem important to me.”
—Hortense Odlum
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
2
XPLevel 3 Objectives: Exploring 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
3
XPAnalyzing 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
4
XPCreating 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
5
XPCrosstab Field Settings
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
6
XPModifying 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
7
XPAccess Action Queries
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
8
XPProcess for Archiving Data
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
9
XPArchiving Data with Make-table 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
10
XPAdding Records to Tables with Append Queries
• Append query Select records from one or more tables by setting
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
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
11
XPRemoving 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
12
XPUpdating Data with an Update 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
13
XPMaking 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
14
XPCustomizing Queries Using Structured Query Language
• 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
15
XPCustomizing Queries Using Structured Query Language
(continued)
• 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
16
XPExploring 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
17
XPCommon SQL Keywords
Chapter 3 Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach
18
XPLevel 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
19
XPChapter 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