Exploring Microsoft Access 2003

1. Exploring MicrosoftAccess 2003 Chapter 3 Information From the Database: Reports and Queries

2. Objectives (1 of 2) • Describe types of reports • Describe views in Report window • Describe similarities between forms and reports with respect to controls • List sections in a report and explain purpose • Differentiate between a query and a table

3. Objectives (2 of 2) • Use design grid to create and modify a query • Explain multiple criteria rows and implement AND and OR conditions • Define an action query • Create a crosstab query

4. Overview • Information is data arranged in a useful format • Convert data to information with queries and reports • Understand concepts for reports and queries built on knowledge of tables and forms

5. Reports and Their Anatomy • Columnar report vs. Tabular report • Report header & footer • Page header & footer • Group header & footer • Detail section • Report Wizard

6. Report View Report Header Page Header Detail Section

7. Report Design View Report Header Page Header Detail Section Page footer

8. The Report Wizard Hands-On Exercise 1 • Open Database • Report Wizard • Preview Report • Modify Control • Add Unbound Control • Change Sort Order • View Report • Report Properties

9. Creating a Query • Query • Design grid -Field, Table, Sort, Show, & Criteria • Dynaset

10. Creating a Query • Select query • Query window views - Datasheet view - Design view - SQL view

11. Creating a Query--Selection Criteria • AND condition • OR condition • Relational operators • Between function • NOT function • Wild card

12. Creating a Select Query Hands-On Exercise 2 • Open Database • Add Students Table • Create Query • Specify Criteria • Run the Query • Modify Query • Create a Report • View the Report

13. Select Query Design View table criteria Run button

14. Select Query Dynaset The results of the query are displayed as a dynaset

15. Grouping Records • Use report anatomy to group records • Grouping allows calculations like: - Count function - Sum function - Min function - Max function - Average function

16. Grouping Records Hands-On Exercise 3 • Create the Query • Add a Calculated Control • Run, Modify, and Rerun the Query • The Report Wizard • Sorting and Grouping • Create the Group & Report Footers • View the Report

17. Crosstab & ActionQueries • Hands-On Exercise 4 • Create Make-Table Query • Create Delete Table Query • Create Append Table Query • Create Update Query • Check Progress • Create Crosstab Query Action Queries

18. Action Queries: • Make-table query: • Creates a table to hold information on graduating students. • This information comes from the Students table. • Graduating students are those students who have completed • 120 credits or more. • Delete query: • Removes graduating students from the Students table. • Append query: • Adds newly transferred students to the Students table. The • Transfers table holds information about new students. • These students are added to the Students table using an • Append query.

19. Update query: • Sets the Financial Aid attribute to Yes for qualified • Students who have a GPA of 3 or more are entitled to • financial assistance. • Crosstab Queries: • Creates a crosstab query to generate a 'table' showing • Major by average GPA. This information is presented • nicely by a crosstab query.

20. Join operation: Select Fname, Lname, Dependent.Name From Employees, Dependents Where Employees.SSN = Dependents.ESSN

21. Relational algebra - Join • Employees ssn=essn Dependents fname minit … ssn Franklin T … 333445555 Jennifer S … 987654321 John B … 123456789 Essn dependent_name ... 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 Abner 123456789 Michael 123456789 Alice 123456789 Elizabeth

22. Employees ssn=essn Dependents fname minit ssn essn Franklin T 333445555 333445555 Alice Franklin T … 333445555 Theodore Franklin T … 333445555 Joy Jennifer S … 987654321 Abner John B … 123456789 Michael John B … 123456789 Alice John B … 123456789 Elizabeth dependent_name ...

23. Outer Joins • join - only matching tuples are in the result • left outer join - all tuples of R are in the result regardless ... • right outer join - all tuples of S are in the result regardless ... • full outer join - all tuples of R and S are in the result regardless ... R S R S R S R S

24. Left Outer Joins r1 r2 A A B2 B1 B1 a2 a1 a1 a3 a3 b1 b4 b3 a2 C c1 b3 b3 b1 c4 b1 c3 b2 b2 r1 r2 B1=B2 C c1 null c3

25. r1 r2 A B2 A B1 B1 a1 a3 a2 b1 a3 b3 a1 b4 b1 c4 b2 b3 C b1 b3 c1 c3 r1 r2 Right Outer Joins B1=B2 C c1 c3 null c4 b4

26. Full Outer Joins r1 r2 A B2 A B1 B1 a2 a1 a3 a2 b3 b4 a1 b1 b3 b2 c1 b2 c3 b1 c4 b1 C r1 r2 B1=B2 C c1 null c3 a3 b3 null b4 c4

27. Chapter 3 Summary (1 of 2) • Data refers to facts about a record • Information: data arranged in usefulformat • Report prints and displays informationfrom a database • Reports created through Report Wizard • Reports: based on table or query

28. Chapter 3 Summary (2 of 2) • Query selects information to display • Grouping records in a field forsummaries • Action queries modify records • Crosstab queries display aggregated • rather than individual records

29. Practice with Access 1. Our Students Database 2. Employee Database 3. The United States Database 4. Bookstore Database 5. Super Bowl Database 6. Database Properties 7. Action Queries 8. The Switchboard Manager

30. Case Studies • The United States of America • The Super Bowl • Mail Merge • Compacting vs. Compressing