Microsoft Access 2002

1. Microsoft Access 2002 Tutorial 6 – Creating Custom Reports

2. Design and create a custom report • You can easily create custom reports based on a table or query. • 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. • Every report must have a Detail section • The usual order of development is to first develop the table(s) in a database, then develop queries, and finally develop forms and reports. • You should always base a report on a query! • Then you can add additional fields by adding them to the query

3. Report sections and their contents This figure shows what is contained in each report section.

4. An Access report with all sections shown Page Header. Report Header. Group Headers. Detail Section. Group Footer. Detail Sections. Report Footer. Page Footer.

5. Design a report before creating it Before creating a report, you should always create a design. This will help you decide what you want in the report and where you want each piece of data placed in the report. This figure contains an example of a report design. Note that the X's are used as place holders representing the number of positions each field will use in the report.

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

7. The Sorting and Grouping dialog box If you also want grouping, in the Sorting and Grouping dialog box, you can specify what field should also be grouped. You do this by changing the Group Header property to Yes. When sorting, you need to decide whether you want to sort in ascending or descending order.

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

9. A subreport in Design view When you add a subreport to a report, this is how it will appear in Report Design view. These fields are on the main report. The subreport label will appear when the report is viewed in datasheet view.

10. A subreport in Print Preview This is how the report will look when it is previewed and printed.

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

12. Add a calculated field to a footer section Click the Text Box tool, then click and drag where you want the calculated field to go. Enter the calculation expression in the property sheet for the control. Add a label to identify the field.

13. View a report with duplicated hidden and calculated fields Calculated totals.