analyzing data for effective decision making n.
Download
Skip this Video
Loading SlideShow in 5 Seconds..
Analyzing Data For Effective Decision Making PowerPoint Presentation
Download Presentation
Analyzing Data For Effective Decision Making

Loading in 2 Seconds...

play fullscreen
1 / 78

Analyzing Data For Effective Decision Making - PowerPoint PPT Presentation


  • 123 Views
  • Uploaded on

Chapter 3. Analyzing Data For Effective Decision Making. Chapter Introduction. Organize and Retrieve information from an Access 2010 database Create complex queries Explore advanced queries Explore queries written in Structured Query Language. Tools Covered In This Chapter.

loader
I am the owner, or an agent authorized to act on behalf of the owner, of the copyrighted work described.
capcha
Download Presentation

PowerPoint Slideshow about 'Analyzing Data For Effective Decision Making' - kaida


An Image/Link below is provided (as is) to download presentation

Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author.While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server.


- - - - - - - - - - - - - - - - - - - - - - - - - - E N D - - - - - - - - - - - - - - - - - - - - - - - - - -
Presentation Transcript
chapter introduction
Chapter Introduction

Organize and Retrieve information from an Access 2010 database

Create complex queries

Explore advanced queries

Explore queries written in Structured Query Language

Succeeding in Business with Microsoft Access 2010

tools covered in this chapter
Tools Covered In This Chapter

Action queries (update, append, delete, crosstab, and make-table)

Aggregate functions (Avg, Max, Min, Sum)

Calculated field

Comparison and logical operators

Filter by Form and Filter by Selection

Find duplicates query

Find unmatched records query

Succeeding in Business with Microsoft Access 2010

tools covered in this chapter1
Tools Covered In This Chapter

Immediate IF (IIF) function

Parameter query

Select query

Simple Query Wizard

SQL commands

Top Values query

Wildcard characters

Succeeding in Business with Microsoft Access 2010

level 1 objectives organizing and retrieving information from a database
Level 1 Objectives: Organizing and Retrieving Information from a Database

Filter and sort data to make it more meaningful

Create simple queries to answer business questions

Develop queries using comparison criteria and wildcards

Verify and print query results

Succeeding in Business with Microsoft Access 2010

filtering and sorting data
Filtering and Sorting Data
  • Filter
    • Restricts data in single table to create temporary subset of records
    • See only certain records in table based on specified criteria
  • Sorting records
    • Organizing in particular order or sequence
    • Sort records regardless of whether table filtered

Succeeding in Business with Microsoft Access 2010

filtering by selection
Filtering by Selection
  • Tools
    • Filter by Selection
      • Select particular field in datasheet
      • Display only data that matches contents of field
      • Specify only one criterion for filter
    • Filter by Form

Succeeding in Business with Microsoft Access 2010

using filter by selection to display a temporary subset of records
Using Filter by Selection to Display a Temporary Subset of Records

Succeeding in Business with Microsoft Access 2010

filtering by form
Filtering by Form
  • Specify two or more criteria
  • Filter for comparative data
    • Use comparison operators
  • AND criteria
    • Selects records that contain all specified values
  • OR criteria
    • Selects records that contain any of specified values

Succeeding in Business with Microsoft Access 2010

sorting data to increase information content
Sorting Data to Increase Information Content
  • Organizes data and increases information value
  • Access sorts records based on primary key values
    • Use sorting to change order
  • To sort
    • Select sort field
  • Sort on multiple fields
    • Move fields in datasheet view so that they are adjacent

Succeeding in Business with Microsoft Access 2010

sorting data to increase information content1
Sorting Data To Increase Information Content
  • Primary sort field
    • Access sorts records by this field first

Succeeding in Business with Microsoft Access 2010

filtering using the filter arrow
Filtering Using the Filter Arrow

Succeeding in Business with Microsoft Access 2010

using queries to answer business questions
Using Queries to Answer Business Questions
  • Query
    • Database object
    • Stores criteria for selecting records from one or more tables based
    • Save query
      • Use it again
    • More powerful than filter
      • Display only some fields in table
      • Create fields that perform calculations

Succeeding in Business with Microsoft Access 2010

using queries to answer business questions continued
Using Queries to Answer Business Questions (continued)
  • Capabilities of Access queries:
    • Display selected fields and records from table
    • Sort records on one or multiple fields
    • Perform calculations
    • Generate data for forms reports and other queries
    • Update data in database
    • Find and display data from two or more tables
    • Create new tables
    • Delete records in table based on one or more criteria

Succeeding in Business with Microsoft Access 2010

using queries to answer business questions continued1
Using Queries to Answer Business Questions (continued)
  • Select query
    • Ask question based on one or more tables in database
    • Result displayed in datasheet
      • Called recordset

Succeeding in Business with Microsoft Access 2010

using the simple query wizard to create a query
Using the Simple Query Wizard to Create a Query
  • Simple query wizard
    • Presents list of tables and queries in database
      • And fields that they contain
    • Select fields from one or more tables
    • Wizard creates and displays results

Succeeding in Business with Microsoft Access 2010

including summary statistics in a query for data analysis
Including Summary Statistics in a Query for Data Analysis
  • Summary query
    • Groups records
    • Calculate sum, average, minimum, or maximum value in each selected field
    • Count records in table or query
  • Click summary options button

Succeeding in Business with Microsoft Access 2010

creating a query in design view
Creating a Query in Design View
  • Select query window in Design view has two sections
    • Area for field lists at top of window
    • Design grid below it
  • Add tables for query to top part of window
    • Appear as field lists
  • Query by example (QBE)
    • Typing search value as a criterion

Succeeding in Business with Microsoft Access 2010

query design view tools
Query Design View Tools

Succeeding in Business with Microsoft Access 2010

creating queries with multiple criteria
Creating Queries with Multiple Criteria
  • Most queries involve more than one criterion
  • Represent AND criteria
    • Entering conditions in same criteria row in query design grid
  • Specify OR criteria
    • Use “or” row of query design grid

Succeeding in Business with Microsoft Access 2010

setting criteria for the query in design view
Setting Criteria for the Query in Design View

Succeeding in Business with Microsoft Access 2010

specifying sort order in queries
Specifying Sort Order in Queries
  • Query results appear in same order as data from underlying tables
    • Unless specify sort order when designing query
  • Sort order determined from left to right

Succeeding in Business with Microsoft Access 2010

running a query
Running a Query
  • Click Run button in the Results group
    • Access displays datasheet of records
  • Save query
    • Save only design
    • Not values from tables displayed in results

Succeeding in Business with Microsoft Access 2010

enhancing query criteria to improve data analysis
Enhancing Query Criteria to Improve Data Analysis
  • Expand criteria by using
    • Wildcards
    • Comparison operators

Succeeding in Business with Microsoft Access 2010

using wildcards for inexact query matches
Using Wildcards for Inexact Query Matches
  • Wildcard character
    • Placeholder
    • Stands for one or more characters
  • Comment fields
    • Use same keywords throughout memos
    • Easily retrieve records later
    • Use wildcards when specifying keyword as a query criterion
      • To select records that contain characters before and after keyword

Succeeding in Business with Microsoft Access 2010

wildcard characters used in queries
Wildcard Characters Used in Queries

Succeeding in Business with Microsoft Access 2010

using wildcards for inexact query matches continued
Using Wildcards for Inexact Query Matches (continued)
  • Access inserts
    • Word “LIKE” for criteria with wildcards quotation marks around text
      • LIKE “*Spanish*”
    • Pound signs around dates
      • #12/*/2013#

Succeeding in Business with Microsoft Access 2010

using comparison operators to refine query criteria
Using Comparison Operators to Refine Query Criteria
  • Comparison operators
    • Compare value in field with range of values in criterion
  • Clear the grid
    • Start with same field list but blank grid

Succeeding in Business with Microsoft Access 2010

comparison operators
Comparison Operators

Succeeding in Business with Microsoft Access 2010

verifying and printing query results
Verifying and Printing Query Results
  • Verify query results before
    • Distributing query to others
    • Using it as basis for decisions
  • Use business knowledge
    • Determine whether results adequately answer question
  • Print query datasheet

Succeeding in Business with Microsoft Access 2010

modifying query datasheets
Modifying Query Datasheets
  • Improve appearance of query or table datasheet
  • Resize column widths in any datasheet
    • Double-click line between field names to resize columns to best fit

Succeeding in Business with Microsoft Access 2010

formatting options for query and table datasheets
Formatting Options for Query and Table Datasheets

Succeeding in Business with Microsoft Access 2010

level 1 summary
Level 1 Summary
  • Use filtering and sorting to change data display
  • Develop queries using
    • Simple query wizard
    • Design view
  • Use comparison operators and wildcards to make queries more flexible
  • Verify query results using business knowledge

Succeeding in Business with Microsoft Access 2010

level 2 objectives creating more complex queries
Level 2 Objectives:Creating More Complex Queries

Design special types of queries

Design queries that compare data from more than one table

Refine table relationships by specifying the join type

Perform calculations in queries

Succeeding in Business with Microsoft Access 2010

designing special types of queries
Designing Special Types of Queries

Succeeding in Business with Microsoft Access 2010

using queries to find duplicate records
Using Queries to Find Duplicate Records
  • Duplicates Query Wizard
    • Searches for duplicate values in fields
    • Improve business operations
    • Designed to identify records that contain same information in particular field

Succeeding in Business with Microsoft Access 2010

using queries to find unmatched records
Using Queries to Find Unmatched Records
  • Find Unmatched Query Wizard
    • Compares records in two specified tables or recordsets
    • Finds all records in one table or query that have no related records in second table or query
    • Requires that two tables being compared have common field

Succeeding in Business with Microsoft Access 2010

limiting the records in the query results
Limiting the Records in the Query Results
  • Limiting results to only a few records often aids analysis
  • Top and Bottom Values
    • Sorts and then filters records
    • Display specified number of records that contain top or bottom values

Succeeding in Business with Microsoft Access 2010

top values query design and results
Top Values Query Design and Results

Succeeding in Business with Microsoft Access 2010

designing a parameter query
Designing a Parameter Query
  • Parameter values
    • Phrase usually in form of a question or instruction
    • Enclosed in square brackets
    • Serves as prompt to user to enter value
    • Example
      • [Enter a job ID:]

Succeeding in Business with Microsoft Access 2010

parameter query to allow user input when the query is run
Parameter Query to Allow User Input when the Query Is Run

Succeeding in Business with Microsoft Access 2010

analyzing data from more than one table
Analyzing Data from More than One Table
  • Data from more than one table required to answer question
    • Combine records from two or more tables
    • Display only information needed
  • Work in design view to specify criteria for selecting records from multiple tables
  • Lines between tables link primary key to foreign key field
    • Primary key designated with 1
    • Foreign key designated with ∞

Succeeding in Business with Microsoft Access 2010

analyzing data from more than one table continued
Analyzing Data from More than One Table (continued)
  • Join tables
    • Linking of tables using primary and foreign keys
    • Established relationship
    • Or
      • Each table shares field with same or compatible data type
      • One join field primary key
  • If tables do not include fields that can be joined
    • Add one or more extra tables or queries
    • Link tables that contain the data

Succeeding in Business with Microsoft Access 2010

analyzing data from more than one table continued1
Analyzing Data from More than One Table (continued)
  • Use queries as source of underlying data for another query in place of one or more tables
  • Create queries based on more than one object
    • Should not use any table or query that does not have common field with at least one of the other tables or queries
    • Otherwise Access displays every combination of records between two tables

Succeeding in Business with Microsoft Access 2010

refining relationships with appropriate join types
Refining Relationships with Appropriate Join Types
  • Inner join
    • Displays all records in one table that have corresponding values in common field in another table
    • Records must match before being displayed in query results
  • Outer join
    • Display all records of one table
    • Regardless of whether corresponding record stored in related table

Succeeding in Business with Microsoft Access 2010

refining relationships with appropriate join types continued
Refining Relationships with Appropriate Join Types (continued)
  • Outer join types
    • Left
    • Right
  • Use join properties dialog box
    • To change join type
    • By default tables related using inner joins

Succeeding in Business with Microsoft Access 2010

using logical operators to specify multiple conditions
Using Logical Operators to Specify Multiple Conditions
  • Logical operators
    • Test values that can only be true or false
  • Place conditions in separate fields in same criteria row of design grid
    • All conditions in row must be met to select record
  • NOT logical operator
    • Excludes values that don’t meet criterion

Succeeding in Business with Microsoft Access 2010

logical operators
Logical Operators

Succeeding in Business with Microsoft Access 2010

performing calculations with queries
Performing Calculations with Queries
  • Any information derived from fields in table or query should be calculated in query
    • Rather than included as data in table
  • Calculation types
    • Predefined
      • Compute amounts for groups of records or for all records combined in query
    • Custom
      • Performs numeric date and text computations on each record
      • Using data from one or more fields

Succeeding in Business with Microsoft Access 2010

calculating statistical information
Calculating Statistical Information
  • Aggregate functions
    • Arithmetic and statistical operations
    • Apply to records that meet query’s selection criteria

Succeeding in Business with Microsoft Access 2010

using aggregate functions in a query
Using Aggregate Functions in a Query

Succeeding in Business with Microsoft Access 2010

creating calculated fields in a query
Creating Calculated Fields in a Query
  • Expression
    • Arithmetic formula used to make calculation
    • Use standard arithmetic operators
    • Use parenthesis for complex expressions
  • Calculated field
    • Add to query design grid
    • Type expression
  • Expression builder
    • Build complex expressions

Succeeding in Business with Microsoft Access 2010

creating calculated fields in a query continued
Creating Calculated Fields in a Query (continued)
  • Function
    • Perform standard calculation
    • Return value
  • Date()
    • Provides today’s date
  • Field properties
    • Change format and number of decimal places for calculated field

Succeeding in Business with Microsoft Access 2010

field properties
Field Properties

Succeeding in Business with Microsoft Access 2010

concatenating in queries
Concatenating in Queries
  • Concatenation
    • Combining contents of two or more fields
    • Operator
      • &
    • Example
      • Name [EmpFirst]& " " & [EmpLast]

Succeeding in Business with Microsoft Access 2010

level 2 summary
Level 2 Summary
  • Use queries to select data from more than one table
  • Join types
    • Inner
    • Right outer
    • Left outer
  • Calculated field types
    • Predefined
    • Custom

Succeeding in Business with Microsoft Access 2010

level 3 objectives exploring advanced queries and queries written in structured query language
Level 3 Objectives: Exploring Advanced Queries and Queries Written in Structured Query Language

Modify data using queries

Calculate and restructure data to improve analysis

Make decisions in a query using the immediate IF (IIF) function

Develop queries using SQL

Succeeding in Business with Microsoft Access 2010

modifying data using queries
Modifying Data Using Queries
  • 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

Succeeding in Business with Microsoft Access 2010

creating a crosstab query
Creating a Crosstab Query
  • To create use
    • Use a wizard
      • Often need to create query first
    • Or design view
      • Start with select query that includes numeric values or summary calculations

Succeeding in Business with Microsoft Access 2010

crosstab field settings
Crosstab Field Settings

Succeeding in Business with Microsoft Access 2010

calculating and restructuring data using queries
Calculating and Restructuring 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

Succeeding in Business with Microsoft Access 2010

access action queries
Access Action Queries

Succeeding in Business with Microsoft Access 2010

process for archiving data
Process for Archiving Data

Succeeding in Business with Microsoft Access 2010

archiving data with make table queries
Archiving 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

Succeeding in Business with Microsoft Access 2010

adding records to tables with append queries
Adding 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

Succeeding in Business with Microsoft Access 2010

removing records from tables with delete queries
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

Succeeding in Business with Microsoft Access 2010

updating data with an update query
Updating 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

Succeeding in Business with Microsoft Access 2010

making decisions in queries
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)

Succeeding in Business with Microsoft Access 2010

customizing queries using structured query language
Customizing 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

Succeeding in Business with Microsoft Access 2010

customizing queries using structured query language continued
Customizing 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

Succeeding in Business with Microsoft Access 2010

exploring the components of an sql query
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

Succeeding in Business with Microsoft Access 2010

common sql keywords
Common SQL Keywords

Succeeding in Business with Microsoft Access 2010

level 3 summary
Level 3 Summary
  • Action queries
    • Make new tables
    • Append data
    • Delete data
    • Update data
  • IFF function
  • SQL
    • Use SQL view to edit SQL directly

Succeeding in Business with Microsoft Access 2010

chapter summary
Chapter Summary
  • Queries retrieve data from one or more tables
    • Action queries update data
    • Perform calculations
    • Make decisions using IIF function
  • SQL
    • Used to interact with relational databases
    • Use SQL view to view/edit SQL statements generated by Access

Succeeding in Business with Microsoft Access 2010