New Perspectives on Microsoft Office Access 2003 Tutorial 7

1
Microsoft Office Access 2003

• Tutorial 7 Creating Custom Reports

2
Design and create a custom report

• You can easily create custom reports based on a
  table or query.
• There are seven sections that can be added to a
  report
• Report Headers and Footers, Page Headers and
  Footers, Group Headers and Footers, and a detail
  section
• You can change any of these sections for your
  report.
• At a minimum, you will always include a detail
  section in your report.
• The usual order of development is to first
  develop the table(s) in a database, then develop
  queries, and finally develop forms and reports.

3
Report sections and their contents
4
An Access report with all sections shown
5
Design a report before creating it
6
Assign a conditional value to a calculated field

• There will be times when you will want to perform
  one operation under a certain condition and
  perform a different operation under another
  condition.
• In Access you can do this using the IIf function.
  
• The IIf function specifies a condition
• If the condition is true, the operation is
  performed
• If the condition is false, a different operation
  is performed

7
An IIf statement example
8
Sort and group data in a report

• Access makes it easy for you to sort your data in
  the order you want it.
• You can also group data, which will result in
  data being sorted within the groups.
• If you specify that you want a group, you have
  the opportunity to provide a Group Header and/or
  Group Footer.
• The Group Header will usually indicate the name
  of the group and the footer typically contains a
  subtotal for the group.

9
The Sorting and Grouping dialog box
10
Add, move, resize, and align controls in a report

• You can add controls to a report in the same
  manner as you add controls to a form.
• Bound controls get their data from a field in a
  table or query.
• To add a bound control to a report, click the
  desired field in the field list and drag it to
  the report design window.
• You can then move these controls around according
  to your design.
• Move controls by selecting them and then dragging
  them to the desired position
• Resize a control by dragging one of its selection
  handles to the desired position
• Align several controls by selecting the controls
  and then right-clicking on one of the controls
  and selecting the align command

11
Bound controls on a report
12
Modify control properties

• Each control has a set of properties associated
  with it.
• In the case of the label control, the Caption
  property contains the text that will be displayed
  in the label.
• To change any of the properties for a control you
  must first display the property sheet.
• You can display the property sheet for a control
  by right-clicking on the control.

13
Add a subreport to a main report

• To add a subreport to a report, you use the
  Subform/Subreport wizard to create the subreport.
  
• In order to launch the wizard, you must be sure
  that the Controls Wizards tool is selected in the
  toolbox.
• The subreport will usually consist of data from a
  related table. If the report is based on a query
  that uses both tables, you can pull the data from
  the related table into the subreport.

14
A subreport in Design view
15
A subreport in Print Preview
16
Add lines to a report

• To provide a visual separation in the report, you
  may want to add a line to the report.
• To do this
• Select the drawing tool from the toolbox and
  position the pointer where you want the line to
  begin
• Hold down the mouse button and drag the pointer
  to where the line should end, and release the
  mouse button
• To ensure a straight vertical or horizontal line,
  hold down the Shift key while you drag

17
Add a line to a report in Design view
18
Hide duplicate values in a report

• When you group items in a report, you may not
  want to repeat the duplicate values within the
  group.
• For example, if the report is grouped by
  department, you dont need to have Accounting
  printed on each record in that department
• You can hide these duplicate values by setting
  the Hide Duplicates property to Yes.

19
Add calculated controls to a report

• If you want to have a subtotal for the groups in
  a report, you should add the Sum function to the
  Group Footer section.
• If you also want an overall total, you should
  place the Sum function in the Report Footer
  section.
• The Sum function will be placed inside a text box
  control that you have added to your report in the
  appropriate position.

20
Add a calculated field to a footer section
21
View a report with duplicated hidden and
calculated fields
22
Define conditional formatting rules

• Sometimes you will want to call attention to a
  value under certain conditions.
• You can specify under what conditions formatting
  would be applied to a control.
• You can define up to three formats for a control.
  
• Any value other than those specified in the
  conditions will be formatted in the default
  format.

23
Use the Conditional Formatting dialog box
24
Use domain aggregate functions

• Domain aggregate functions are used to provide
  statistical information about a set of records,
  or recordset.
• An example of an aggregate function is the Sum
  function
• The Sum function adds the values in a set of
  records and places the Sum in a bound control
• To apply an aggregate function to a table or
  query, you must use domain aggregate functions.
• In this case you would use the DSum function
• You need to specify the name of the set of
  records (the query or table) and which values are
  to be summed
• This is only one example of a domain aggregate
  function. There are several others.

25
Add a domain aggregate function
26
Additional Domain Aggregate Functions
27
Add the date, page numbers, and title to a report

• Place the Date function into a text box to have
  the current date printed on a report
• The current date will appear in the report
  wherever you place the control containing the
  Date function
• You may also want to add page numbers to the
  report
• Insert page numbers into your report and specify
  several options as to how the page numbers should
  appear
• You can add a report title by adding a label
  control to the Report Header section
• Whatever text you place in the label control will
  be printed on the report

28
Add a date and page number to a report in Design
view
29
A report with date and page number in Print
Preview
30
Create and modify mailing labels

• If you want to create mailing labels (or labels
  of any kind), you can use the Label Wizard.
• The Label Wizard will walk you through the
  selection of the type of label you want and label
  layout.
• You can use a table or a query as the basis for
  the labels.

31
Open the Label Wizard

• Click Reports in the Access Object bar.
• Click the New button in the Database window.
• Click Label Wizard, then click the list arrow to
  display a list of tables and queries. Select one,
  and click the OK button to open the Label Wizard
  dialog box.
• You can select the label type that you want to
  print in the first Label Wizard dialog box.

32
Choosing a label type
33
Format your labels