Query and Report

1
Chapter 3

• Query and Report

2
Agenda

• Report types
• Report contents
• Report creation
• Report design view
• Query and dynaset
• Function and grouping
• Action query

3
Objectives (continued)

• Use design grid to create and modify a select
  query
• Explain multiple criteria rows in a query
• Use concatenation to combine fields
• Use an immediate If statement
• Define an action query
• Create a crosstab query

4
Report Types

• Columnar (vertical) report
• Simplest type
• Lists every field for every record in a single
  column (one record per page)
• Tabular report
• Displays selected fields in a row
• One record per row

5
Report Contents

• Report header footer
• Page header footer
• Group header footer
• Detail section

6
Report Creation

• Report Wizard
• Specify the table or query
• Select one or more fields
• Select layout (columnar/ tabular)
• Select a style
• Modify the resulting report

7
Report Design View

• 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)

8
Query

• The resulting records from a question
• Design grid graphical tool for developing the
  query
• Dynaset records satisfying the query criteria

9
Dynaset

• Looks and acts like a table
• A dynamic subset of a table
• Allows for data entry, record updating, and
  record deletion
• Changes update the underlying table

10
Query Creation

• Select query
• Query window views
• Datasheet view
• Design view

11
Query Selection Criteria

• AND condition
• OR condition
• Relational operators
• Between function
• NOT function
• Wild card

12
Functions and Calculated Controls

• Concatenate
• Joins together fields
• Example
• Name LastName,FirstName
• IF (IIF) function
• Condition, what happens if true, what happens if
  false

13
Grouping Records

• Grouping allows calculations in the report for
• Count function
• Sum function
• Min function
• Max function
• Avg function
• Crosstab query displays aggregated values

14
Action Query

• 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

15
Points to Remember

• Report types
• Report contents
• Report creation
• Report design view
• Query and dynaset
• Function and grouping
• Action query

16
Homework Assignment

• Practice exercises 6 and 7