Analyzing Data For Effective Decision Making

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

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

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

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

5. Crosstab Field Settings Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

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

7. Access Action Queries Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

8. Process for Archiving Data Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

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

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

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

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

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

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

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

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

17. Common SQL Keywords Succeeding in Business with Microsoft Office Access 2003: A Problem-Solving Approach

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

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