Analyzing Data For Effective Decision Making

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