Analyzing Data and Creating Reports

1. Office Access 2003Lab 3 Analyzing Data and Creating Reports

2. Objectives • Filter table records • Create and modify a query. • Move columns. • Query two tables. • Create reports from tables and queries.

3. Objectives continued • Modify a report design. • Select, move, and size controls. • Change paper margins. • Preview and print a report. • Compact and back up a database.

4. Filter Query Join Report Control Concept Preview

5. Outline • Filtering Records • Using Filter by Selection • Using Filter by Form

6. Outline continued • Querying a Database • Using a Query Wizard • Moving Columns • Modifying and Saving a Query • Creating a Query in Design View • Querying Two Tables • Finding Unmatched Records • Adding a Second Table to the Query

7. Outline continued • Creating a Report from a Table • Using the AutoReport Wizard • Using the Report Wizard • Modifying the Report Design • Selecting the Report Design • Moving Controls • Sizing Controls • Previewing the Report Layout

8. Outline continued • Creating a Report from a Query • Previewing and Printing Reports • Changing Page Margins • Printing an Entire Report • Printing a Selected Page of the Report • Compacting and Backing Up the Database

9. Outline • Concept Summary • Lab Review • Lab Exercises

10. Filtering Records • Doing sorts to find information can be time consuming • A faster way to locate information in Access is to apply a filter to the table records

11. Concept 1 Filter • Restriction placed on records • Quickly isolates and displays a subset of records • You specify a criteria • Limiting conditions • Temporary Filter • Filter results can't be saved • Filter criteria is saved with the table

12. Using Filters • Filter by Selection • Displays only records containing a specific value • Effective when only one value is needed • Other records are temporarily hidden • Filter by Form • Performs filters on multiple criteria • Look For tab • Or tab

13. Using Filters • Criteria expression • Typing a value directly in its corresponding field • Select value from drop down list • OR operator • AND operator ( homework)

14. Filter by selection Filter displays only those record meeting the criteria of city of Maldin Filter by Selection

15. Filter by Form

16. Querying A Database • A query is a request for information • View data in different ways • Analyze data • Change existing data • Can be used as a source for forms and reports

17. Concept 2 Query

18. Action Query • Make-Table Query. • Delete Table Query. • Append Table Query. • An update Table Query.

19. SQL Queries • An SQL query is a query you create by using an SQL statement. You can use Structured Query Language (SQL) to query, update, and manage relational databases such as Microsoft Access . • SQL Select • SQL where.

20. 5 methods to create queries Ways to Create a Query

21. Using a Query Wizard

22. Using a Query WizardSteps • Select underlying table and fields • Specify a name for your query • Select to open as is or in Design view

23. Underlying table to be used as the data source Specify fields to include in query output Simple Query

24. Adds field Adds all fields 5 fields to include in query output Removes field Removes all fields Adding Fields to the Query

25. Query Datasheet Query datasheet displays 5 selected fields for all records

26. Moving Columns Last Name column moved

27. Modifying a query • To modify a query, you make changes in the Query Design view window • The Design window has two areas • Upper area includes • Field list • Lower area includes • Design grid • Column selector • Row label • Cell • Show boxes

28. Column selector bar Employee Records table field list Checked Show box indicates the field will be displayed in query results Raw labels Cell Design grid Query Design View Window

29. Runs query Criteria expression enclosed in quotation marks Using an Expression

30. Query Database Display Query database displays all recordswith a city field of Maldin

31. Select table to use as data source Creating a Query in Design View

32. Selecting Fields for Query Select fields from field list to add to grid

33. Data criterion entered <=1/1/00 Using an Expression

34. Employees hired on or before 01/01/2000 Records meeting criteria Records Meeting Criteria

35. Saved Queries Saving Queries

36. Querying Two Tables

37. Concept 3 Join • An association between a field in one table or query and a field in another table or query • Must have at least one common field • Fields must be the same data type • Used to bring information from different tables together • Joins are what make relational databases so powerful

38. Selected table is searched for records Finding Unmatched Records

39. Matching field is selected in the two tables Finding Unmatched Records

40. Finding Unmatched Records Two records are found that are missing from Clubs table

41. Add table to query Adding a Second Table to the Query

42. Join line links common field Join

43. Field from second table added to grid Criteria entered Adding Fields to Join

44. Records are a result of the combination of fields from both tables Run the Query

45. Joins

46. Creating a Report from a Table

47. Concept 4 Report • Printed output generated from tables or queries • All fields or selected fields • Can be based on a table or query • Can customize: • Text formats • Styles • Layouts • Grouping • Summary information

48. Report Types • Columnar report • Simplest type • Lists every field for every record in a single column. • Tabular report • Displays selected fields in a row • One record per row

49. Columnar Report Tabular Report

50. Anatomy of a Report • Report header & footer Header: at the beginning of the report ,it contains information describing the report Footer: at the end of the report , it displays summary information for the report. • Page header & footer Header: appears at the top of every page in a report ( page number, column heading). Footer: appears at the bottom of every page in a report • Group header & footer • Header: appears at the beginning of a group of records . Footer: appears after the last record in a group. Detail sectionmain body