Microsoft Office Access 2003

1. Microsoft Office Access 2003 Tutorial 7 – Creating Custom Reports New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

3. Report sections and their contents New Perspectives on Microsoft Office Access 2003 Tutorial 7

4. An Access report with all sections shown New Perspectives on Microsoft Office Access 2003 Tutorial 7

5. Design a report before creating it New Perspectives on Microsoft Office Access 2003 Tutorial 7

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 New Perspectives on Microsoft Office Access 2003 Tutorial 7

7. An IIf statement example New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

9. The Sorting and Grouping dialog box New Perspectives on Microsoft Office Access 2003 Tutorial 7

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 New Perspectives on Microsoft Office Access 2003 Tutorial 7

11. Bound controls on a report New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

14. A subreport in Design view New Perspectives on Microsoft Office Access 2003 Tutorial 7

15. A subreport in Print Preview New Perspectives on Microsoft Office Access 2003 Tutorial 7

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 New Perspectives on Microsoft Office Access 2003 Tutorial 7

17. Add a line to a report in Design view New Perspectives on Microsoft Office Access 2003 Tutorial 7

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 don’t 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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

20. Add a calculated field to a footer section New Perspectives on Microsoft Office Access 2003 Tutorial 7

21. View a report with duplicated hidden and calculated fields New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

23. Use the Conditional Formatting dialog box New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

25. Add a domain aggregate function New Perspectives on Microsoft Office Access 2003 Tutorial 7

26. Additional Domain Aggregate Functions New Perspectives on Microsoft Office Access 2003 Tutorial 7

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 New Perspectives on Microsoft Office Access 2003 Tutorial 7

28. Add a date and page number to a report in Design view New Perspectives on Microsoft Office Access 2003 Tutorial 7

29. A report with date and page number in Print Preview New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

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. New Perspectives on Microsoft Office Access 2003 Tutorial 7

32. Choosing a label type New Perspectives on Microsoft Office Access 2003 Tutorial 7

33. Format your labels New Perspectives on Microsoft Office Access 2003 Tutorial 7