Database Management System

1
Reports, Forms, and Combo Boxes Project 4
2
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.

3
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

4
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

5
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

6
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

7
Report Creation (AC 198)

• We used the Report Wizard in Project 1(AC 42) to
  create the Client Amount Report
• Its 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

8
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

9
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.

10
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

11
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.

12
Sorting and Grouping (AC 205)

• Some people believe that the statistics, or data,
  in a report are grouped more often to support the
  writers 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

13
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 reports accuracy?
• From inside ? QA testing, Finance review.
• From outside ? Dont believe everything you read,
  back up the data from other sources

14
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.

15
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

16
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.

17
Formatting Controls (AC 214)

• The format of controls can be changed by
• Using the Property Sheet of the control
• Using the Formatting toolbar

18
Creating a Report Involving Multiple Tables (AC
217)

• There are several ways to create a report that
  utilizes multiple tables
• Use the Report Wizard to select fields from
  multiple tables
• Create a custom report selecting fields from
  necessary tables
• 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

19
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
• 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?
• Allow sufficient white space between groups.
• Do not overuse fonts and sizes can look
  cluttered and amateurish
• Be consistent on the style of your reports.

20
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.

21
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 forms 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

22
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.

23
Form Design Considerations (AC 246)

• Forms should be user-friendly and pleasing to
  the eye.
• 3 guidelines for designing and creating custom
  forms
• Remember that users may look at the form several
  hours at a time. Cluttered forms are hard on the
  eyes.
• Place fields in logical groupings. Related
  fields should be close to one another on the
  form.
• If data entered comes from a paper form, mirror
  the paper form as closely as possible.

24
Reports, Forms, and Combo Boxes Project 4
Any Questions?