Chapter 6

Advanced Report Techniques

MicrosoftAccess 2013

Advanced Report Techniques 2

• Create and relate additional tables• Create queries for reports• Create reports in Design view• Add fields and text boxes to a report• Format report controls• Group and ungroup report controls• Update multiple report controls

Objectives

Advanced Report Techniques 3

• Add and modify a subreport• Modify section properties• Add a title, page number, and date to a report• Preview, print, and publish a report• Add totals and subtotals to a report• Include a conditional value in a report

Objectives

Advanced Report Techniques 4

Project – Creating Detailed Reports

Advanced Report Techniques 5

• Create and relate additional tables.• Create queries for a report• Create a report• Specify grouping and sorting• Add fields and text boxes to the report• Add a subreport to the report• Add a title, page number, and date to the report• Create a second report

Roadmap

Advanced Report Techniques 6

• Tap or click the Relationships button (DATABASE TOOLS tab | Relationships group) to open the Relationships window

• Tap or click the Show Table button (RELATIONSHIP TOOLS DESIGN tab | Relationships group) to display the Show Table dialog box

• Tap or click each table to add, tapping or clicking the Add button after each table, and then tap or click the Close button

• Drag the primary key from one table to the same field in the related table

• If desired, tap or click the Enforce Referential Integrity check box• Tap or click the Create button to create the relationship• Tap or click the Save button on the Quick Access Toolbar to save the

changes and then tap or click the Close button (RELATIONSHIP TOOLS DESIGN tab | Relationships group)

Relating Tables

Advanced Report Techniques 7

Relating Tables

Advanced Report Techniques 8

• Tap or click the Query Design button (CREATE tab | Queries group) to create a new query

• Add the desired table(s) to the query• Add the desired fields to the design grid• In the field to contain the expression, tap or click

the Builder button (QUERY TOOLS DESIGN tab | Query Setup group) to display the Expression Builder dialog box

Creating an Additional Query for the Repot Using Expression Builder

Advanced Report Techniques 9

Creating an Additional Query for the Repot Using Expression Builder

10

• Tap or click the Report Design button (CREATE tab | Reports group) to create a report in Design view

• Ensure the selector for the entire report, the box in the upper-left corner of the report, is selected

• Tap or click the Property Sheet button (REPORT DESIGN TOOLS Design tab | Tools group) to display a property sheet

• With the All tab (Property Sheet) selected, tap or click the Record Source property box arrow to display the list of available tables and queries

• Tap or click the desired table or query to use as the record source for the report

• Close the property sheet by tapping or clicking the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group)

Advanced Report Techniques

Creating an Initial Report in Design View

Advanced Report Techniques 11

Creating an Initial Report in Design View

Advanced Report Techniques 12

• Tap or click the Group & Sort button (REPORT DESIGN TOOLS DESIGN tab | Grouping & Totals group) to display the Group, Sort, and Total pane

• Tap or click the ‘Add a group’ button to display the list of available fields for grouping

• Tap or click the field to group by• Tap or click the ‘Add a sort’ button to display the

list of available fields for sorting• Tap or click the field to sort by

Grouping and Sorting

Advanced Report Techniques 13

Grouping and Sorting

Advanced Report Techniques 14

• Tap or click the ‘Add Existing Fields’ button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display a field list

• Drag the field to add from the field list to the desired location

• Adjust the positions of the labels to their desired location(s). To move the control or the attached label separately, drag the large handle in the upper-left corner of the control or label. You can align controls using the Align button (REPORT DESIGN TOOLS ARRANGE tab | Sizing & Ordering group) or adjust spacing by using the Size/Space button (REPORT DESIGN TOOLS ARRANGE tab | Sizing & Ordering group)

Adding Fields to the Report in Design View

Advanced Report Techniques 15

Adding Fields to the Report in Design View

Advanced Report Techniques 16

• Tap or click the label to change two times• Erase the current entry and type the new entry

Changing Labels

Advanced Report Techniques 17

• Tap or click the Text Box tool (REPORT DESIGN TOOLS DESIGN tab | Controls group) and move the pointer to the desired position

• Tap or click the desired location to place a text box on the report

• Tap or click in the text box to produce an insertion point• In the text box, type the desired expression• Tap or click in the text box label to select the label and then tap

or click the label a second time to produce an insertion point• Type the desired text box label text• Move the text box and label to the desired location

Adding Text Boxes

Advanced Report Techniques 18

Adding Text Boxes

Advanced Report Techniques 19

• Tap or click the View button arrow (REPORT DESIGN TOOLS DESIGN tab | Views group) to produce the View menu

• Tap or click Print Preview on the View menu to view the report in Print Preview

Viewing the Report in Print Preview

Advanced Report Techniques 20

• Tap or click the control to format, and then tap or click the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display the property sheet

• If necessary, tap or click the All tab• Set the desired properties in the property sheet• Remove the property sheet by tapping or clicking

the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group) a second time

Formatting a Control

Advanced Report Techniques 21

Formatting a Control

Advanced Report Techniques 22

• Select the controls to group• Tap or click the Size/Space button (REPORT

DESIGN TOOLS ARRANGE tab | Sizing & Ordering group) to display the Size/Space menu

• Tap or click Group on the Size/Space button menu to group the controls

Grouping Controls

Advanced Report Techniques 23

Grouping Controls

Advanced Report Techniques 24

• To modify grouped controls, tap or click any one of the grouped controls to select the group, and then modify the properties in the property sheet

• To modify controls that are not grouped, select each control first, and then modify the properties in the property sheet

Modifying Grouped and Non-grouped Controls

Advanced Report Techniques 25

• Tap or click the More button (REPORT DESIGN TOOLS DESIGN tab | Controls group) to display a menu of available tools

• Be sure the ‘Use Control Wizards’ button is selected, tap or click the Subform/Subreport tool, and then move the pointer, which has changed to a plus sign with a subreport, to the desired location

• Tap or click the desired position to place the subreport and display the SubReport Wizard dialog box. Be sure the ‘Use existing Tables and Queries’ option button is selected

Adding a Subreport

Advanced Report Techniques 26

• Tap or click the Next button• Tap or click the Tables/Queries box arrow• Select the desired table or query for the

subreport, and then add the desired fields• Tap or click the Next button and then ensure the

‘Choose from a list’ option button is selected• Tap or click the Next button, type the desired

subreport name, and then tap or click the Finish button

Adding a Subreport

Advanced Report Techniques 27

Adding a Subreport

Advanced Report Techniques 28

• Open the Navigation Pane, scroll to the desired report to display, and press and hold or right-click the report to produce a shortcut menu

• Tap or click Design View on the shortcut menu to open the subreport in Design view

Opening a Subreport in Design View

Advanced Report Techniques 29

• Tap or click the control for which you want to change the Can Grow property

• Tap or click the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display the property sheet

• With the All tab selected, scroll down until the Can Grow property appears, and then tap or click the Can Grow property box arrow to display the list of possible values for the Can Grow property

• Tap or click Yes in the list to allow the selected control to grow as needed

Changing the Can Grow Property

Advanced Report Techniques 30

Changing the Can Grow Property

Advanced Report Techniques 31

• Open the report containing the subreport in Design view

• Tap or click the subreport and then drag the right sizing handle to change the size to the desired size, and then drag the subreport to the desired position

• Scroll down in the main report so that the lower boundary of the Detail section appears, and then drag the lower boundary of the section to the desired location

Resizing the Subreport and the Report in Design View

Advanced Report Techniques 32

Resizing the Subreport and the Report in Design View

Advanced Report Techniques 33

• Tap or click the label for the section to modify, and then tap or click the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display the property sheet

• Make the desired modifications in the property sheet

Modifying Section Properties

Advanced Report Techniques 34

Modifying Section Properties

Advanced Report Techniques 35

• Tap or click the Title button (REPORT DESIGN TOOLS DESIGN tab | Header/Footer group) to add a title

• Tap or click the Page Numbers button (REPORT DESIGN TOOLS DESIGN tab | Header/Footer group) to display the Page Numbers dialog box

• Click the desired format and position• Select the desired alignment, and whether you want

the number on the first page• Tap or click the OK button

Adding a Title, Page Number, and Date

Advanced Report Techniques 36

• Tap or click the ‘Date and Time’ button (REPORT DESIGN TOOLS DESIGN tab | Header/Footer group) to display the Date and Time dialog box

• Tap or click the desired date format, and select whether to include the time

• Tap or click the OK button

Adding a Title, Page Number, and Date

Advanced Report Techniques 37

Adding a Title, Page Number, and Date

Advanced Report Techniques 38

• Press and hold or right-click the title bar for the Report Header to select the header without selecting any of the controls in the header

• Point to ‘Fill/Back Color’ arrow to display a color palette• Tap or click White in the first row, first column of the color

palette• Press and hold or right-click the Detail section to produce a

shortcut menu• Point to the ‘Alternate Fill/Back Color’ arrow to produce a color

palette• Tap or click None on the color palette to remove the alternate

color for the selected section

Removing the Header Background Color and the Alternate Color

Advanced Report Techniques 39

Removing the Header Background Color and the Alternate Color

Advanced Report Techniques 40

• Tap or click the ‘Add Existing Fields’ button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display a field list

• Drag the field to add from the field list to the desired location

Adding Fields in a Report

Advanced Report Techniques 41

• Scroll down so that the lower boundary of the Detail section appears, and then drag the lower boundary of the section to the desired location

Resizing the Detail Section

Advanced Report Techniques 42

• Tap or click the Group & Sort button (REPORT DESIGN TOOLS DESIGN tab | Grouping & Totals group) to display the Group, Sort, and Total pane

• Group by the desired field and set the desired options• Tap or click the Text Box tool (REPORT DESIGN TOOLS

DESIGN tab | Controls group), and then tap or click the desired position for the text box

• Tap or click the text box to produce an insertion point• Type the expression calculation for the sum (for example,

=Sum([Amount Paid]) ) and then press the ENTER key

Adding Totals and Subtotals

Advanced Report Techniques 43

Adding Totals and Subtotals

Advanced Report Techniques 44

• Tap or click the Text Box tool (REPORT DESIGN TOOLS DESIGN tab | Controls group) and tap or click the desired location for the text box

• Tap or click the text box to select it, and then tap or click the Property Sheet button (REPORT DESIGN TOOLS DESIGN tab | Tools group) to display a property sheet

• Tap or click the Control Source property to select it

Assigning a Conditional Value

Advanced Report Techniques 45

• Tap or click the Build button to display the Expression Builder dialog box

• Double-tap or double-click Functions in the first column to display the function subfolders

• Tap or click Built-In Functions in the first column to display the various function categories in the second column

• Scroll down in the second column so that Program Flow appears, and then tap or click Program Flow to display the available program flow functions in the third column

• Double-tap or double-click IIf in the third column to select the IIf function

Assigning a Conditional Value

Advanced Report Techniques 46

• Tap or click the <expression> argument to select it and type the desired expression (condition) (for example, [Amount Paid]>20000 )

• Tap or click the <truepart> argument to select it and type the desired truepart (for example, .04*[Current Due] )

• Tap or click the <falsepart> argument to select it and type the desired falsepart (for example, .02*[Current Due] )

• Tap or click the OK button to specify the expression as the control source for the text box

Assigning a Conditional Value

Advanced Report Techniques 47

Assigning a Conditional Value

Advanced Report Techniques 48

• Create and relate additional tables• Create queries for reports• Create reports in Design view• Add fields and text boxes to a report• Format report controls• Group and ungroup report controls• Update multiple report controls

Chapter Summary

Advanced Report Techniques 49

• Add and modify a subreport• Modify section properties• Add a title, page number, and date to a report• Preview, print, and publish a report• Add totals and subtotals to a report• Include a conditional value in a report

Chapter Summary

Chapter 6 Complete

MicrosoftAccess 2013