Transcript 6_Reports

Chapter 5
Reports
IT Key Ideas, Dynamic Database Systems, 2002
Reports are based on queries
and add the following features
•
•
•
•
•
Margins (top, bottom, left, right)
A report heading and footer
Page headers and footers
Group headers and footers
Printing controls, eg. in columns
other title
Main title
Group header
duplicates hidden
data
Group footer/summary information
Page footer
Design
view
other title
Main title
Group header
Field names
Data boxes
Group footer/summary information
Page footer
other information
Headers and Footers
 The information in the report header
and footer are printed on one page only.
For example, the last page can present
what is termed a Grand Summary. This
might be a count of the records in the
report, or the grand total of the prices.
Headers and Footers
 The information in a group header and
footer is printed before or after each
group respectively.
For example, at the bottom of each group
Summary Information can be displayed.
This might be a count of the records in the
group, or the sub-total of the costs within
that group.
Typical database reports include
•
•
•
•
•
•
•
•
a title (and sub-title)
a group sub-heading
column sub-headings
data
repetitive data not being printed
a summary
grand summary
and other information, be this page
numbers, date or the author of the report.
Creating a Report
Example
Print a list of people with red hair in order of age,
displaying the number of people in the output.
IT Key Ideas, Dynamic Database Systems, 2002
Create the Query
The outcome requires the names of people and their
ages, in age order, where their hair colour is red.
The names of the people may be their full names,
separate given names and surnames or possibly
surnames only.
Use the Age calculation saved from previous queries.
Field
Full Name:[Given … Age:Int((Date()-… Hair Colour
Surname
Sort
ascending
ascending
Show
Criteria
3
3
3
3
red
IT Key Ideas, Dynamic Database Systems, 2002
Create report by using wizard
select Create a report by using
wizard and select the query
IT Key Ideas, Dynamic Database Systems, 2002
the wizard prompts
which fields?
all of them
but as Hair Colour will not be displayed,
choose it last
grouping?
none for the current task
sorting?
already sorted by Age, but if not, choose Age
layout?
tabular
style?
personal preference
title?
something like People with red hair
preview or modify.
IT Key Ideas, Dynamic Database Systems, 2002
Skills
 page numbers, names, dates
 displaying summary calculations
grand summary
group summary
 non-repeating of repetitive data
 minimising the number of pages
 controlling printing, including page and
column breaks.
Page numbers
• pages numbers are displayed by the field titled
[Page]
• and the number of pages in the report by the
field titled [Pages]
Text can be added to the numbers by enclosing in
quotes. For example,
=“page “&[Page] or
="page " & [Page] & " of " & [Pages]
NB:
page numbers are only for multi-page reports.
Displaying dates
as in queries, the current date can be
displayed dynamically by using the
calculation =Date() in a text box.
Summaries
Two types, namely
• Grand summary
• Summary (group summary)
A Grand Summary appears in a
• Report Footer or Header
A Summary appears in a
• Group Footer or Header
Calculating the Summaries
The following functions are typically used
•
•
•
•
•
Count
Sum
Avg
Max
Min
Examples of Summaries
=Count([Surname])
=Sum([Appearance Fee])
=Avg([Age])
=Max([Mark])
Examples of Summaries
When placed in the Report Footer
=Count([Surname])
will display the number of records
that reside in the output
Examples of Summaries
When placed in a Group Footer
=Avg([Age])
will display the average age for
each group in the report,
eg. each Hair Colour group.
Avoiding repetitive data
Method 1 - Hiding
select the field showing the repetitive data in the
Details section of the report choose Properties
and set the property Hide Duplicates to Yes
set to Yes
Avoiding repetitive data
Method 2 - Groups
click the Sorting and Grouping button and
choose a field on which to set the Grouping
set the Group Header and/or Footer to Yes
set to Yes
Minimising the number of pages
Hint
Keep the detail section as clean and short
as possible.
Eliminate or minimise the gaps above and
below the text boxes.
Columns
Set these
properties
in Page
Setup
Columns
Hint
when using Group Headers with Columns
In Group Header
Properties set the
property Repeat
Section to yes.
set to Yes
to avoid
this problem
Controlling printing
The printing of data in groups can be controlled
by utilising the properties called
Force New Page and/or New Column.
Sample
output
Example 2
Print a list of the (full) names of people
(alphabetically) without brownish hair colour,
displaying the number of people in each hair
colour group and also overall (using a
minimum number of pages).
IT Key Ideas, Dynamic Database Systems, 2002
Sample
output
page 58
Practical 5.1 People
IT Key Ideas, Dynamic Database Systems, 2002
Producing an output that displays
Female
Male
Abbott
Abram
Almond
Apponyi
…
Abbery
Aitchison
Auerbach
Auld
…
where the column headings are the full word
version of the gender.
Create a new table
In the new table create two fields, according to
the data dictionary shown
Field Name
Data Type
Size
Sex
Gender
Text
Text
1
6
Save the table, eg. Genders.
Enter data into the new table
Sex
F
M
Gender
Female
Male
Creating a lookup list in the many table
The data relationship between the Genders and
Details tables is one to many, as a Gender has
many people, but any person only has one gender.
The Genders table is referred to as the one table
and the Details table is then referred to as the
many table.
In this table it is possible to create a Lookup List
that allows for easy data entry when entering each
new person’s gender.
Selecting a gender by using a lookup list
A lookup list can also be referred to as pop-down list.
Creating the lookup list
Choose Design
view for the
Details table.
At the bottom, next to
Select the Sex field General, select the
Lookup tab.
Change the
Display
Control to
Combo Box.
Click on the Build
button on the Row
Source and this
opens a query.
Add the Genders table to the query and the
two fields to the query
close and update the row source.
Change the column count to 2
as two fields were placed in the QBE,
enter two Column Widths, eg. 1.5, 2.5
enter a total width in List Width, eg. 4
*
*
*
NB: the SQL statement
View the table.
Click anywhere in the Sex field (column)
and click the Lookup arrow.
page 63
Practical 5.3 People
IT Key Ideas, Dynamic Database Systems, 2002
Chapter 5
********************************
IT Key Ideas, Dynamic Database Systems, 2002