Exploring Microsoft Access 2003 Chapter 3 Information From the

Exploring Microsoft Access 2003 Chapter 3 Information From the Database: Reports and Queries

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

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

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

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

Report View Report Header Page Header Detail Section

Report Design View Report Header Page Header Detail Section Page footer

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

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

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

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

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

Select Query Design View table criteria Run button

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

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

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

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

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.

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.

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

Relational algebra - Join Employees ssn essn Dependents Essn fname minit ssn Franklin T 333445555 Jennifer S 987654321 John B 123456789 dependent name . 333445555 Alice 333445555 Theodore 333445555 Joy 987654321 Abner 123456789 Michael 123456789 Alice 123456789 Elizabeth

Employees ssn essn Dependents fname minit ssn essn Franklin T Franklin T 333445555 Theodore Franklin T 333445555 Joy Jennifer S 987654321 Abner John B 123456789 Michael John B 123456789 Alice John B 123456789 Elizabeth 333445555 333445555 dependent name . Alice

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

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

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

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

Chapter 3 Summary (1 of 2) Data refers to facts about a record Information: data arranged in useful format Report prints and displays information from a database Reports created through Report Wizard Reports: based on table or query

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

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

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