Transcript Reports, Forms, and Combo – Project 4 Boxes

Reports, Forms, and Combo
Boxes – Project 4
Why Use a Report/Form?
A report differs from just printing records directly from a
table in several ways. Reports provide the capability of:
 Grouping – separate collections of records that share
a common characteristic
 Customization – you can customize your report with
formatting or by adding controls such as totals/subtotals
 Using Multiple Tables – you can use more than one
table to create a report. You can use a sub-report within
a report.
2
Project 4 Perspective
Dr. Gernaey and his colleagues at Ashton James College
hope to realize additional benefits from the database by
designing two custom reports that meet their needs:
1st Report layout
 Report by client type, and within each type …
 Include: subtotal of amount paid
subtotal of current due
 Include: grand total of amount paid
grand total of current due
2nd Report layout
 Report by trainer number, and within each number …
 Include: subtotal of amount paid
subtotal of current due
 Include: grand total of amount paid
grand total of current due
3
Project 4 Perspective
Ashton James College also wants to improve the data
entry process by using a custom form:
Form layout:




Title
Fields arranged in two columns
Include calculated field with total: amount paid + current due
Include combo box for: client type
current trainers
4
Introduction (AC 194)
Refer to Figure 4-1 and 4-2, page AC 195
 Grouping and Subtotals
 Figure 4-1
 In Figure 4-1, the records have been grouped by client type
 There are 3 separate groups: EDU, MAN, SER
 Subtotals of amount paid and current due are calculated for
each client type grouping
 Grand totals of amount paid and current due are shown at
the end
 Figure 4-2
 In Figure 4-2, what have the records been grouped by?
 Once again subtotals and grand totals of amount paid and
current due are shown
5
Introduction (AC 194)
Refer to Figures 4-3 (a) & (b), page AC 196
 Combo Boxes
 Allow users to select entries from a list. An arrow appears in
the field. Clicking the arrow causes a list to appear.
 Figure 4-3 (a)
 There is a combo box for Client Type
 A calculated field has been added called “Total Amount” –
sums Amount Paid + Current Due
 Figure 4-3 (b)
 What field has a combo box?
 A calculated field has been added called “Total Amount” –
sums Amount Paid + Current Due
6
Report Creation (AC 198)
 We used the Report Wizard in Project 1(AC 42) to
create the Client Amount Report
 It’s often fast to use Report Wizard to begin the design
of your report and modify it using the Report Window
 We will now create the 1st AJC report using Report
Wizard to start and modifying in the Report Window
7
Using Design View (AC 202)
 Within a Report window there are three possible views:
 Design view – use to modify the design (layout) of the report
 Print Preview – use to see the report with all data from tables
 Layout Preview – use to view the report with a sample of data
 The most useful views are Design view and Print Preview
 Switch from Design view to Print Preview by clicking the
Print Preview button on the Report Design toolbar
8
Using Design View (AC 202)
 A toolbox is available in Design view that allows you
to create special objects for the report. This toolbox
may obscure a portion of your report.
 Since you will use the toolbox frequently, dock the
toolbox (move to a desired location on screen) and
leave it.
 In Design view you can add fields to your report from
a field list which includes all the fields in the query or
table on which the report is based.
9
Report Sections (AC 204)
 Each object on the report is a control
 Every report is divided into one of the following sections:
 Report Header section/Report Footer section
 Prints once at the beginning (header) / end (footer) of the report
 Controls in this section are labels
 Page Header section/Page Footer section
 Prints once at the top (header) / bottom (footer) of each page
 Controls in this section are labels
 Detail section
 Prints once for each record in the table / query
 Controls in this section display the contents of the
corresponding fields
10
Sorting and Grouping (AC 204)
Grouping – creating separate collections of records
sharing some common characteristic
In grouping, reports include two additional sections:
Group header and Group footer
 The Group header is printed before the records in a
particular group are printed; the Group footer printed
after the group.
 The Group Footer includes totals (or subtotals )of
calculated sums.
11
Sorting and Grouping (AC 205)
 Some people believe that the statistics, or data, in a report are
grouped more often to support the writer’s agenda than to shed light
on, or clarify, the data.
 Andrew Lang, Scottish author, once wrote about a popular public
figure,
“He uses statistics as a drunken man
uses lamp posts – for support,
not for illumination.”
 Here are a few humorous, if not truthful quotes:
“ There are three kinds of commonly recognized untruths:
Lies, damn lies and statistics.”
- Mark Twain
“Think about how stupid the average person is;
now realize half of them are dumber than that.”
- George Carlin
12
Sorting and Grouping (AC 205)
 How can grouping make a report more valuable?
 A report in a hospital that shows Inpatient Days for
2005 grouped by Nursing Unit and Month.
 Could grouping make a report misleading? How?:
 Yes. One might decide to group on a particular Day
(an unusually “good” day) and not report that the data
is from that day only.
 Can anything be done to ensure that grouping does not
compromise a report’s accuracy?
 From inside  QA testing, Finance review.
 From outside  Don’t believe everything you read,
back up the data from other sources
13
Changing Properties (AC 207)
 Property Sheet – a list of properties for each control
that can be changed.
 Refer to Figure 4-14 (AC 202) Client Account Summary
Report: Notice that the city was truncated
 There are 3 approaches to fixing this data problem:
 Move the controls to allow more space in between. Then drag
the handles to expand them.
 Use the Font Size property to select a smaller font size.
 Use the Can Grow property. By changing the value of this
property from No to Yes, the data can spread over two lines,
thus allowing all the data to print.
14
Adding totals and subtotals (AC 209)
 To add totals or other statistics, add a Text box – a
control that displays data
 You can use any of the aggregate functions in the text
box expression
 Where you perform a calculation in the report makes a
difference:
 If the calculation is done in the Group Footer section, Access
only will perform the calculation for records in that group
 If the calculation is done in the Report Footer section, Access
will perform the calculation for all records
15
Aligning Controls (AC 211)
 Sometimes several controls may need to be aligned in a
particular way (i.e. right edges even with each other)
 There are 2 ways to select multiple controls:
 1st – individually select controls and align
(click one and SHIFT + Click others)
 2nd - use a ruler. Click a position on the horizontal ruler and it
selects all the controls for which a portion of the control is under.
Or Click a position on the vertical ruler and it selects all the
controls to the right of that position on the ruler.
16
Formatting Controls (AC 214)
The format of controls can be changed by:
 Using the Property Sheet of the control
 Using the Formatting toolbar
17
Creating a Report Involving Multiple Tables
(AC 217)
 There are several ways to create a report that utilizes
multiple tables:
1. Use the Report Wizard to select fields from multiple tables
2. Create a custom report selecting fields from necessary tables
3. Create a query with a join between 2+ tables and then design
a report utilizing the query
 Define the grouping and sorting options for the report
 Indicate the summary options necessary for the report
 Sum, Avg, Min, Max
18
Report Design Considerations (AC 227)
 Reports should be user-friendly and “pleasing to the eye.”
 Users make judgments about a database based on output. If output
is unprofessional, users may question accuracy of underlying data.
4 Guidelines for designing and creating reports
1. The purpose of a report is to provide specific information.
… Does the report convey this information effectively?
… Are the meanings of the rows/columns in the report clear?
… Are the column captions easily understood?
2. Allow sufficient white space between groups.
3. Do not overuse fonts and sizes – can look cluttered and amateurish
4. Be consistent on the style of your reports.
19
Creating and Using Custom Forms (AC 227)
Custom forms:
 Allow fields to be arranged in a more natural way.
 You can use color and boxes to highlight certain areas
and improve the visual appeal of the form.
 You can create forms from tables or from queries.
20
Modifying the Form Design (AC 229)
 You can start creating your form in Form Wizard and
Modify the Form Design.
 As with reports, items on a form are called controls and
they are manipulated the same as on reports
 There are 3 types of controls:
 Bound controls – display data that comes from the database.
Such as the client number or name
 Unbound controls – not associated with data from the
database. Such as a form’s title.
 Calculated controls – used to display data that is calculated
from other data in the database. Such as Total Amount,
calculated from amount paid + current due.
 A calculated control is a form of an unbound control
21
Combo Boxes (AC 234)
 Advantages to using a combo box on a form:
 A combo box allows users to select a value from a drop-down
list OR type a value
 Values in the drop-down list can be entered when the combo
box is created or the combo box can select values from a table
 Combo boxes assist with the data entry process and help
ensure the accuracy of the data
 A list box is similar to a combo box except that you cannot type
value - you only can select them from a list. Combo boxes are
more flexible.
22
Form Design Considerations (AC 246)
Forms should be user-friendly and “pleasing to the eye.”
3 guidelines for designing and creating custom forms:
1. Remember that users may look at the form several
hours at a time. Cluttered forms are hard on the eyes.
2. Place fields in logical groupings. Related fields should
be close to one another on the form.
3. If data entered comes from a paper form, mirror the
paper form as closely as possible.
23
Reports, Forms, and Combo
Boxes – Project 4
Any Questions?