Exploring Microsoft Access
Chapter 3
Information From the
Database: Reports and
Queries
By
Robert T. Grauer
Maryann Barber
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
Exploring Microsoft Office - Microsoft Access Chapter 3 2
Objectives (2 of 2)
Use design grid to create and modify
a select query
Explain multiple criteria rows to
implement ‘AND’ & ‘OR’ conditions in
a query
Define an action query
Create a crosstab query
Exploring Microsoft Office - Microsoft Access Chapter 3 3
Overview
Information is data arranged in a useful
format
Convert data to information with
queries and reports
Understand concepts for reports and
queries building on knowledge of tables
and forms
Discover similarities among objects
Exploring Microsoft Office - Microsoft Access Chapter 3 4
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
Exploring Microsoft Office - Microsoft Access Chapter 3 5
Anatomy of a Report
Report header & footer
Page header & footer
Group header & footer
Detail section
Exploring Microsoft Office - Microsoft Access Chapter 3 6
Report Wizard
Easiest way to create a report
Specify the table or query
Select one or more fields
Select layout (columnar/ tabular)
Select a style
Modify the resulting report
Exploring Microsoft Office - Microsoft Access Chapter 3 7
Report Print Preview
Report
Header
Page Header
Detail Section
Exploring Microsoft Office - Microsoft Access Chapter 3 8
Report Design View (1 of 2)
Bound Controls
Data source is a field in the table/query
Unbound Controls
Has no data source
Used to display titles, labels, lines, rectangles,
and graphics
Calculated Controls
Data source is an expression (avg, sum)
Exploring Microsoft Office - Microsoft Access Chapter 3 9
Report Design View (2 of 2)
Report
Header
Page Header
Detail Section
Page footer
Toolbox
Exploring Microsoft Office - Microsoft Access Chapter 3 10
Introduction to Queries
Query provides the resulting
records from a question
Design grid: graphical tool used
to develop queries
Dynaset: The records that
satisfy the query criteria
Exploring Microsoft Office - Microsoft Access Chapter 3 11
Dynaset
Looks and acts like a table
Is a dynamic subset of a table
Allows for entry, updating and
deletion of records
Changes update the underlying
table
Exploring Microsoft Office - Microsoft Access Chapter 3 12
Creating a Query
Select query
Query window views
Datasheet view
Design view
SQL view
Exploring Microsoft Office - Microsoft Access Chapter 3 13
Query Selection Criteria
AND condition
OR condition
Relational operators
Between function
NOT function
Wild card
Exploring Microsoft Office - Microsoft Access Chapter 3 14
Select Query Design View
Table
Criteria
Run Button
Exploring Microsoft Office - Microsoft Access Chapter 3 15
Select Query Dynaset
The results of the query are displayed as a dynaset
Exploring Microsoft Office - Microsoft Access Chapter 3 16
Grouping Records
Use report anatomy to group records
Grouping allows calculations like:
Count function
Sum function
Min function
Max function
Avg function
Exploring Microsoft Office - Microsoft Access Chapter 3 17
Crosstab Queries
Average GPA/Major by Gender
Listing of
all Majors
Exploring Microsoft Office - Microsoft Access Chapter 3 18
Action Queries
Enables table updates. Four types:
Update: Changes multiple records
Append: Adds records from one table to
another
Delete: deletes records that meet a criteria
Make-table: Creates a new table from
existing records
Exploring Microsoft Office - Microsoft Access Chapter 3 19
Chapter 3 Summary (1 of 2)
Data refers to facts about a record
Information is data arranged in a
useful format
Report prints and displays
information from a database
Report Wizard easily creates reports
Reports: based on table or query
Exploring Microsoft Office - Microsoft Access Chapter 3 20
Chapter 3 Summary (2 of 2)
Query displays records that meet a
criteria
Grouping records by a field for
summaries
Action queries modify records
Crosstab queries display
aggregated rather than individual
records
Exploring Microsoft Office - Microsoft Access Chapter 3 21