1 / 76

Analyzing Data For Effective Decision Making

Analyzing Data For Effective Decision Making. Chapter Introduction. Filter data in Microsoft Office Access 2003 database Retrieve and examine only records you need Sort data Rearrange records in specified order Queries Provide quick answers to business questions.

kineta
Download Presentation

Analyzing Data For Effective Decision Making

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. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript


  1. Analyzing Data For Effective Decision Making Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  2. Chapter Introduction • Filter data in Microsoft Office Access 2003 database • Retrieve and examine only records you need • Sort data • Rearrange records in specified order • Queries • Provide quick answers to business questions Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  3. 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 Office Access 2003: A Problem-Solving Approach

  4. 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 Office Access 2003: A Problem-Solving Approach

  5. Using Filter by Selection to Display a Temporary Subset of Records Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  6. 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 specified values Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  7. 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 Office Access 2003: A Problem-Solving Approach

  8. Sorting Data To Increase Information Content • Primary sort field • Access sorts records by this field first Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  9. Sorting Types of Data Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  10. Using Queries to Answer Business Questions • Query • Database object • Stores criteria for selecting records from one or more tables • 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 Office Access 2003: A Problem-Solving Approach

  11. 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 Office Access 2003: A Problem-Solving Approach

  12. 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 Office Access 2003: A Problem-Solving Approach

  13. 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 Office Access 2003: A Problem-Solving Approach

  14. Advantages and Limitations of the Simple Query Wizard Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  15. 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 Office Access 2003: A Problem-Solving Approach

  16. Creating a Summary Query with the Simple Query Wizard Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  17. 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 Office Access 2003: A Problem-Solving Approach

  18. Query Design View Tools Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  19. 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 Office Access 2003: A Problem-Solving Approach

  20. Setting Criteria for the Query in Design View Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  21. 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 • Multiple columns must be adjacent to sort on more than one field in datasheet view Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  22. Running a Query • Click Run button on query design toolbar • Access displays datasheet of records • Save query • Save only design • Not values from tables displayed in results Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  23. Using Wildcards for Inexact Query Matches • Wildcard character • Placeholder • Stands for one or more characters • Memo 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 Office Access 2003: A Problem-Solving Approach

  24. Wildcard Characters Used in Queries Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  25. 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/*/2008# Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  26. Comparison Operators Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  27. Comparison Operators (continued) Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  28. 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 Office Access 2003: A Problem-Solving Approach

  29. 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 Office Access 2003: A Problem-Solving Approach

  30. Formatting Options for Query and Table Datasheets Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  31. 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 Office Access 2003: A Problem-Solving Approach

  32. Level 2 Objectives:Creating More Complex Queries • Design queries that compare data from more than one table • Refine table relationships by specifying the join type • Perform calculations in queries • Customize queries and their results Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  33. Evaluating Data Using Special Types of Queries Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  34. 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 Office Access 2003: A Problem-Solving Approach

  35. 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 Office Access 2003: A Problem-Solving Approach

  36. Limiting the Records in the Query Results • Limiting results to only a few records often aids analysis • Top Values query • Sorts and then filters records • Display specified number of records that contain top or bottom values • Top Values list box on query design toolbar Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  37. Top Values Query Design and Results Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  38. Using Parameter Values in Queries • 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 Office Access 2003: A Problem-Solving Approach

  39. Parameter Query to Allow User Input when the Query Is Run Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  40. 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 Office Access 2003: A Problem-Solving Approach

  41. 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 Office Access 2003: A Problem-Solving Approach

  42. 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 Office Access 2003: A Problem-Solving Approach

  43. 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 Office Access 2003: A Problem-Solving Approach

  44. Inner Join Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  45. Left Outer Join Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  46. Right Outer Join Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  47. 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 Office Access 2003: A Problem-Solving Approach

  48. 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 Office Access 2003: A Problem-Solving Approach

  49. Logical Operators Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

  50. 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 Office Access 2003: A Problem-Solving Approach

More Related