TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Open CMF Vets from UFuture – Data Files – CMF Vets Mod 6 Start File.
Save as CMF Vets Module 6.

Project — Creating Detailed Reports

• CMF Vets managers want a master list of owners and the veterinarian assigned to each owner.
This list should be available as an Access report and will have the name Owners and Account
Information Master List. For each owner, the report will include full details for all the owners
assigned to the veterinarian. In addition to offering its veterinarian services, CMF Vets offers
workshops designed to educate owners on various aspects of pet products, health trends, and
new health procedures. Data on workshop participation is stored in the database. For
veterinarians who are participating in workshops, the report should list the specific
workshops being offered to the owners and the pets assigned to the owners.

• The Owners and Account Information Master List report is shown in Figure 1. The report is
organized by Owner ID, with the data for each Owner ID beginning on a new page. For each
veterinarian, the report lists the Veterinarian ID number; the report then lists data for each
owner served by that veterinarian. The owner data includes the Owner ID, owner first name,
owner last name, veterinarian, appointment dates, treatment numbers, and cost of those
treatments. For each workshop the owner is taking, the report lists the workshop code,
description, total hours the workshop requires, hours already spent, and hours remaining.

Figure 1

• To attract new clients and reward current clients, some companies offer discounts. CMF Vets
managers are considering offering a discount on the treatment costs over $20 to their current
clients. The exact amount of the discount depends on how much the treatment costs. If
treatment cost is more than $20, the discount will be 20 percent of the treatment cost amount.
If the treatment cost is $20 or less, then no discount will be given. To assist in determining
the discount, CMF Vets managers need a report like the one shown in Figure 2. The report
groups clients by appointments. It includes a subtotal of the treatment costs for the
appointment. In addition, the report includes a discounted cost for the treatment, if the
treatment cost is more than $20. Finally, it shows the cost of the treatment less the discount
amount.

1|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 2

Additional Tables
1. Before creating the reports, you need to create two additional tables for the CMF Vets
database. The first table, Workshop, is shown in Table 1a. As described in Table 1a, each
workshop has a code and a description. The table also includes the total hours for which the
workshop is usually offered and its increments; that is, the standard time blocks in which the
workshop is usually offered. Table 1b contains the specific workshops that the veterinarians
at CMF Vets offer to their clients. The first row, for example, indicates that workshop W01 is
called Healthy Canine Living. It is typically offered in two-hour increments for a total of four
hours.

Table 1a

Table 1b

2|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

2. The second table, Workshop Offerings, is described in Table 2a and contains an owner ID, a
workshop code, the total number of hours that the workshop is scheduled for the owner, and
the number of hours already spent by the owner in the workshop. The primary key of the
Workshop Offerings table is a combination of the Owner ID and Workshop Code fields.

Table 2a

3. Table 2b gives the data for the Workshop Offerings table. For example, the first record shows
that Owner ID O-1 currently has scheduled workshop W03 (Basic Canine Training). The
workshop is scheduled for four hours, and the owner has so far spent two hours in class.

Table 2b

AC 6-2a To Create the New Tables

1. Click the Create tab.

2. Click the Table Design button (Create tab | Tables group) to create a table in Design view.
3. Enter the information for the fields in the Workshop table as indicated in Table 1a, making
Workshop Code the primary key, and specifying the indicated field sizes.
4. Save the table using the name Workshop and close the table.
5. Display the Create tab and then click the Table Design button (Create tab | Tables group) to
create a second table in Design view.
6. Enter the information for the fields in the Workshop Offerings table as indicated in Table 2a.
7. Click the row selector for the Owner ID field.
8. Hold down SHIFT and then click the row selector for the Workshop Code field so that both
fields are selected.
9. Click the Primary Key button (Table Tools Design tab | Tools group) to select the combination
of the two fields as the primary key (Figure 3).
10. Save the table using the name Workshop Offerings and close the table.

3|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 3

AC 6-2b To Import the Data

1. With the CMF Vets database open, click the External Data tab, click the New Data Source
button arrow (External Data tab | Import & Link group), point to From File, and then click
Text File to display the Get External Data – Text File dialog box.
2. Click the Browse button (Get External Data - Text File dialog box) and then navigate to the
location containing the text file.
3. Click the Workshop file, then click Open.
4. If necessary, click the ‘Append a copy of the records to the table’ option button, and then select
the Workshop table from the list. Click OK. With the Delimited option button selected, click
Next.
5. With the Tab option button selected, click the ‘First Row Contains Field Names’ check box,
click Next, and then click Finish.
6. Click the Close button to close the Get External Data - Text File dialog box without saving the
import steps.
7. Use the technique shown in Steps 1, 2, 3, 4, 5, and 6 to import the Workshop [Link] file
into the Workshop Offerings table. Be sure the Comma option button is selected and there is
a check mark in the ‘First Row Contains Field Names’ check box.

AC 6-2d To Relate the New Tables

1. If necessary, close any open datasheet on the screen by clicking its Close button, and then
display the Database Tools tab.
2. Click the Relationships button (Database Tools tab | Relationships group), shown in Figure
3, to open the Relationships window (Figure 4).

4|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 4

3. Click the Show Table button (Relationship Tools Design tab | Relationships group) to display
the Show Table dialog box (Figure 5).

Figure 5

4. Click the Workshop Offerings table, click the Add button (Show Table dialog box), click the
Workshop table, and then click the Add button again to add the tables to the Relationships
window.
5. Click Close to close the Show Table dialog box.
6. Drag the Owner ID field in the Owners table to the Owner ID field in the Workshop Offerings
table to display the Edit Relationships dialog box. Click the ‘Enforce Referential Integrity’

5|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

check box (Edit Relationships dialog box) and then click the Create button to create the
relationship.
7. Drag the Workshop Code field from the Workshop table to the Workshop Code field in the
Workshop Offerings table. Click the ‘Enforce Referential Integrity’ check box (Edit
Relationships dialog box) and then click the Create button to create the relationship (Figure
6).
8. Save the changes and then click the Close button (Relationship Tools Design tab |
Relationships group).

Figure 6

Creating Reports in Design View

AC 6-3a To Create a Query for the Report

CMF Vets management requirements for the reports specify that it would be convenient to use
two queries. These queries do not yet exist, so you will need to create them. The first query relates
owners and their account information. The owner account information consists of appointments,
treatments scheduled, and expected treatment costs for the owner’s pets. The second query
relates workshops and workshop offerings. The following steps create the Owners and Account
Information query.

1. If necessary, close the Navigation Pane, display the Create tab, and then click the Query Design
button (Create tab | Queries group) to create a new query.
2. Click the Owners table, click the Add button (Show Table dialog box), click the Appointments
table, click the Add button, click the Treatment Cost table, click the Add button, close the Show
Table dialog box by clicking its Close button, and then resize the field lists to display as many
fields as possible.
3. If necessary, create a relationship between the Owners table and the Appointments table by
selecting the Owner ID field in the Owners table and dragging it to the Owner field in the
Appointments table.
4. Double-click the Owner ID, Owner First Name, and Owner Last Name fields from the Owners
table to display them in the design grid.

6|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

5. Double-click the Appointment Date and Treatment Number fields from the Appointments
table to add the fields to the design grid.
6. Select Sort in Ascending Order on the Appointment Date field.
7. Double-click the Cost field from the Treatment Cost table to add the field to the design grid.
8. Double-click the Veterinarian field from the Appointments table to add the field to the design
grid.
9. Run the query to view the results and scroll through the fields to make sure you have included
all the necessary fields. If you have omitted a field, return to Design view and add it (Figure
7).

Figure 7

10. Click the Save button on the Quick Access Toolbar to save the query, type Owners and
Account Information as the name of the query, and then click OK.
11. Close the query.

AC 6-3b To Create an Additional Query for the Report Using Expression Builder

1. Display the Create tab and then click the Query Design button (Create tab | Queries group) to
create a new query.
2. Click the Workshop table, click the Add button (Show Table dialog box), click the Workshop
Offerings table, click the Add button, and then click Close to close the Show Table dialog box.
3. Double-click the Owner ID and Workshop Code fields from the Workshop Offerings table to
add the fields to the design grid.
4. Double-click the Workshop Description field from the Workshop table.
5. Double-click the Total Hours and Hours Spent fields from the Workshop Offerings table to
add the fields to the design grid.
6. Click in the top cell in the Field row of the first open column in the design grid to select it.
7. Click the Builder button (Query Tools Design tab | Query Setup group) to display the
Expression Builder dialog box (Figure 8).
8. Double-click CMF Vets in the Expression Elements section to display the categories of objects
within the CMF Vets database, and then double-click Tables to display a list of tables.
9. Click the Workshop Offerings table to select it.
10. Double-click the Total Hours field to add it to the expression.
11. Type a minus sign (−) to add it to the expression.
12. Double-click the Hours Spent field to add it to the expression (Figure 9).
13. Click OK (Expression Builder dialog box) to close the dialog box and add the expression you
entered to the design grid.
14. If necessary, select the field in the grid containing the expression, and then click the Property
Sheet button (Query Tools Design tab | Show/Hide group) to display the property sheet for
the new field.

7|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

15. Ensure that the General tab is selected, click the Caption property box and type Hours
Remaining as the caption (Figure 10).
16. Close the property sheet and then view the query (Figure 11). (Your results might be in a
different order.)

Figure 8

Figure 9

Figure 10

8|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 11

17. Increase the width of the fields in the Design Grid to view the entire field name, and then
verify that your query results match those in the figure. If not, return to Design view and make
the necessary corrections.
18. Click the Save button on the Quick Access Toolbar, type Workshop Offerings and
Workshops as the name of the query, and then click the OK button to save the query.
19. Close the query.

AC 6-3c To Create an Initial Report in Design View

1. Display the Create tab.

2. Click the Report Design button (Create tab | Reports group) to create a report in Design view.
3. Ensure the selector for the entire report, the box in the upper-left corner of the report,
contains a small black square, which indicates that the report is selected.
4. Click the Property Sheet button (Report Design Tools Design tab | Tools group) to display a
property sheet.
5. Drag the left border, if necessary, to increase the width of the property sheet.
6. With the All tab (Property Sheet) selected, click the Record Source property box arrow to
display the list of available tables and queries (Figure 12).

Figure 12

7. Click the Owners and Account Information query to select the query as the record source for
the report.
8. Close the property sheet by clicking the Property Sheet button (Report Design Tools Design
tab | Tools group).

9|Page
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-3d To Group and Sort

1. Click the Group & Sort button (Report Design Tools Design tab | Grouping & Totals
group) to display the Group, Sort, and Total pane (Figure 13).

Figure 13

2. Click the ‘Add a group’ button to display the list of available fields for grouping (Figure
14).

Figure 14

3. Click the Owner ID field to group by owner ID number (Figure 15).

4. Click the Sort by button to display the list of available fields for sorting (Figure 16). If
the Sort by button does not appear for you, click the 'Add a sort' button.
5.

10 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 15

Figure 16

6. Click the Appointment Date field to sort appointments by date of the appointment
(Figure 17).
7. Save the report, using Owners and Account Information Master List as the report
name.

11 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 17

AC 6-3f To Add Fields to the Report in Design View

1. Remove the ‘Group, Sort, and Total’ pane by clicking the Group & Sort button, which is shown
in Figure 13 (Report Design Tools Design tab | Grouping & Totals group).
2. Click the ‘Add Existing Fields’ button (Report Design Tools Design tab | Tools group) to
display a field list.
3. Drag the Owner ID field to the approximate position shown in Figure 18. (The pointer shape
appears as an arrow with a plus sign as you drag a field.)

Figure 18

4. Release the mouse button to place the field and then align the Owner ID field as shown (Figure
19).

12 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

5. Place the remaining fields in the positions shown in Figure 19.

Figure 19

6. Adjust the positions of the labels to those shown in the figure. If any field is not in the correct
position, drag it to its correct location. 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).

Figure 20

AC 6-3g To Change Labels

1. Click the label for the Owner ID field to select the label.
2. Click the label for the O_ID field a second time to produce an insertion point.
3. Use BACKSPACE or DELETE to erase the current entry in the label, and then type Owner ID
Number as the new entry.
4. Click outside the label to deselect the label and then click the label a second time to select it.

13 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

5. Move the control to the position indicated in Figure 6–21 by dragging the move handle in the
upper-left corner of the label.

Figure 21

AC 6-3h Using Other Tools in the Controls Group

Previously, you used the Subform/Subreport tool within the Controls group on the Design tab to
place special controls on a form. The Controls group has additional tools available that can be
used with forms and reports. A description of the additional tools appears in Table 3.

14 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Table 3

15 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-3i To Add Text Boxes

1. Click the Text Box tool (Report Design Tools Design tab | Controls group) and move the
pointer to the approximate position shown in Figure 22.
2. Click the position shown in Figure 22 to place a text box on the report (Figure 23).
3. Click in the text box to produce an insertion point (Figure 24).
4. In the text box, type =[Owner First Name] & ‘ ‘ & [Owner Last Name] to display
the first name of the owner, followed by a space, and then the last name of the
owner.

Figure 22

Figure 23

16 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 24

5. Click in the text box label to select the label and then click the label a second time to
produce an insertion point (Figure 25).

Figure 25

6. Use backspace or delete to erase the current entry in the label and then type Name as
the new entry.

17 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

7. Click outside the label to deselect it and then drag the label to the position shown in
the figure by dragging the Move handle in the upper-left corner of the label (Figure
26).
8. Save the report.

Figure 26

AC 6-3j To View the Report in Print Preview

1. Click the View button arrow (Report Design Tools Design tab | Views group) to produce the
View menu.
2. Click Print Preview on the View menu to view the report in Print Preview (Figure 6–27).
3. Click the ‘Close Print Preview’ button (Print Preview tab | Close Preview group) to return to
Design view.

Figure 27

18 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

1. If the field list is open, remove it by clicking the ‘Add Existing Fields’ button (Report Design
Tools Design tab | Tools group).
2. Click the control containing Treatment Number to select it, and then click the Property Sheet
button (Report Design Tools Design tab | Tools group) to display the property sheet.
3. If necessary, click the All tab (Figure 6–28).

Figure 28

4. If necessary, Click the Text Align property box, click the arrow that appears, and then click
Center to select Center as the control’s alignment.
5. Remove the property sheet by clicking the Property Sheet button (Report Design Tools Design
tab | Tools group) a second time.
6. Preview the report using Print Preview to see the effect of the property changes.
7. Click the ‘Close Print Preview’ button (Print Preview tab | Close Preview group) to return to
Design view.
8. If necessary, Click the Text Align property box, click the arrow that appears, and then click
Center to select Center as the control’s alignment.
9. Remove the property sheet by clicking the Property Sheet button (Report Design Tools Design
tab | Tools group) a second time.
10. Preview the report using Print Preview to see the effect of the property changes.
11. Click the ‘Close Print Preview’ button (Print Preview tab | Close Preview group) to return to
Design view.

AC 6-3l To Group Controls

1. Click the Owner First Name control to select it.

2. Release SHIFT.
3. Display the Report Design Tools Arrange tab.
4. Click the Size/Space button (Report Design Tools Arrange tab | Sizing & Ordering group) to
display the Size/Space menu (Figure 29).
5. Click Group on the Size/Space button menu to group the controls.

19 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

To Ungroup Controls
If you no longer need to simultaneously modify all the controls you have placed
in a group, you can ungroup the controls. To do so, you would use the following
steps.

1. Click any of the controls in a group to select the entire group.

2. Display the Report Design Tools Arrange tab.
3. Click the Size/Space button (Report Design Tools Arrange tab | Sizing & Ordering
group) to display the Size/Space button menu.
4. Click the Ungroup button on the Size/Space button menu to ungroup the controls.

Figure 29

AC 6-3m To Modify Grouped Controls

1. If necessary, click any one of the grouped controls to select the group.
2. Display the Report Design Tools Format tab.
3. Click the Bold button (Report Design Tools Format tab | Font group) to bold all the controls
in the group (Figure 30).
4. Click the Property Sheet button (Report Design Tools Design tab | Tools group) to display
the property sheet for the grouped controls.
5. With the All tab (Property Sheet) selected, ensure the Border Style property is set to Solid. If
it is not, click the Border Style property box to display an arrow, click the arrow to display
the list of available border styles, and click Solid.
6. Click the Border Width property box to display an arrow and then click the arrow to display
the list of available border widths (Figure 31).
7. Click 1 pt to select the border width.
8. Click the Font Size property box to display an arrow and then click the arrow to display the
list of available font sizes.
9. Click 10 or type 10 to change the font size to 10.
10. Close the property sheet.
11. Save the changes.

20 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 30

Figure 31

AC 6-3n To Modify Multiple Controls That Are Not Grouped

1. Click the label for the Owner First Name control to select it.
2. While holding the SHIFT key down, click the labels for all the other controls in the Detail
section to select them.
3. Release the SHIFT key.
4. Display the Report Design Tools Format tab.
5. Click the Italic button (Report Design Tools Format tab | Font group) to italicize the labels
(Figure 6–32).

21 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 32

6. Click in the vertical ruler to the left of the Owner ID control to select all the controls in the
section.
7. Use the buttons on the Report Design Tools Arrange tab to align the controls on the top, if
necessary.
8. Display the Report Design Tools Format tab, if necessary, and then click the Bold button
(Report Design Tools Format tab | Font group) to bold all the selected controls (Figure 6–33).

Figure 33

9. Click outside the selected controls to deselect them. Click the control containing the
expression for the owner’s name to select it.
10. Drag the right sizing handle of the selected control to the approximate position shown
in Figure 34.
11. View the report in Print Preview and then make any necessary adjustments.
12. Save the report.

22 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 34

AC 6-3p To Add a Subreport

1. Switch to Design view and then display the Report Design Tools Design tab.
2. Click the More button, which is shown in Figure 35a (Report Design Tools Design tab |
Controls group), to display a gallery of available tools (Figure 35b).

Figure 35a

23 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 35b

3. Be sure the ‘Use Control Wizards’ button is selected, click the Subform/Subreport tool, and
then move the pointer, which has changed to a plus sign with a subreport, to the approximate
position shown in Figure 36.

Figure 36

4. Click the position shown in Figure 36 to place the subreport and display the SubReport
Wizard dialog box. Be sure the ‘Use existing Tables and Queries’ option button is selected
(Figure 37).

24 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 37

5. Click the Next button.

6. Click the Tables/Queries box arrow to display a list of the available tables and queries.
7. Scroll down until Query: Workshop Offerings and Workshops is visible, click Query:
Workshop Offerings and Workshops to specify it as the data source, and then click the ‘Add
All Fields’ button to select all the fields in the query and add them to the subreport (Figure
38).

Figure 38

8. Click the Next button and then ensure the ‘Choose from a list.’ option button is selected
(Figure 39).

25 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 39

9. Click the Next button, change the subreport name to Workshop Offerings by Owner ID, and
then click the Finish button to add the subreport to the Owner and Account Information
Master List report (Figure 40).

Figure 40

10. Click outside the subreport to deselect the subreport.

11. Save your changes.
12. Close the report.

26 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-3q To Open the Subreport in Design View

1. Open the Navigation Pane, scroll down so that the Workshop Offerings by Owner ID report
appears, and then right-click the Workshop Offerings by Owner ID report to produce a
shortcut menu.
2. Click Design View on the shortcut menu to open the subreport in Design view (Figure 41). If
necessary, increase the size of the subreport by selecting it and dragging the border edges to
the side.

Figure 41

AC 6-3t To Change the Can Grow Property

1. Click the View button arrow and then click Print Preview to preview the report (Figure 42).
If an error message appears, indicating the report is too wide, click OK.

Figure 42

2. Click the ‘Close Print Preview’ button (Print Preview tab | Close Preview group) to return to
Design view.
3. If necessary, click outside all of the selected controls to deselect the controls.

27 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

4. Click the Workshop Description control in the Detail section to select it.
5. Click the Property Sheet button (Report Design Tools Design tab | Tools group) to display the
property sheet.
6. With the All tab selected, scroll down until the Can Grow property appears, and then click the
Can Grow property box arrow to display the list of possible values for the Can Grow property
(Figure 43).

Figure 43

7. Click Yes in the list to allow the Workshop Description control to grow as needed.
8. Close the property sheet.

AC 6-3u To Change the Appearance of the Controls in the Subreport

1. Drag the right boundary of the subreport to the approximate position shown in Figure 44.

Figure 44

2. Display the Report Design Tools Format tab.

28 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

3. Click the ruler to the left of the controls in the Detail section to select the controls, and then
click the Bold button (Report Design Tools Format tab | Font group) to bold the controls.
4. Click the title bar for the Report Header to select the header without selecting any of the
controls in the header.
5. Click the Background Color button arrow (Report Design Tools Format tab | Font group) to
display a color palette (Figure 45).

Figure 45

6. Click White, Background 1 in the first row, first column of the Standard Colors to change the
background color to white (Figure 46).

Figure 46

7. Save the changes, and then close the subreport.

29 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-3v To Resize the Subreport and the Report in Design View

1. Open the Navigation Pane.

2. Open the Owners and Account Information Master List in Design view.
3. Close the Navigation Pane.
4. Click the subreport and drag the right sizing handle to change the size to the approximate size
shown in Figure 47, and then drag the subreport to the approximate position shown in the
figure.

Figure 47

5. Scroll down in the main report so that the lower boundary of the Detail section appears, and
then drag the lower boundary of the detail section up to a position about one inch below the
subreport.
6. Switch to Report view; your report should resemble Figure 48. If necessary, return to Design
view and adjust the boundaries.

Figure 48

30 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

7. If necessary, scroll back up to the top of the report, click the label for the subreport (the label
that reads Workshop Offerings by Owner ID), and then press delete to delete the label.
8. Resize the report by dragging its right border to the location shown in Figure 49.

Figure 49

AC 6-3x To Modify Section Properties

1. Click the Owner ID Header bar to select the header, and then click the Property Sheet button
(Report Design Tools Design tab | Tools group) to display the property sheet.
2. With the All tab selected, click the Repeat Section property box, click the arrow that appears,
and then click Yes to cause the contents of the group header to appear at the top of each page
of the report.
3. Click the Force New Page property box, and then click the arrow that appears to display the
menu of possible values (Figure 50).

Figure 50

31 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

4. Click Before Section to cause a new group to begin at the top of the next page.
5. Close the property sheet.

AC 6-3y To Add a Title, Page Number, and Date

1. Display the Report Design Tools Design tab, if necessary, and then click the Title button
(Report Design Tools Design tab | Header/Footer group) to add a title.
2. Click the Page Numbers button (Report Design Tools Design tab | Header/Footer group) to
display the Page Numbers dialog box.
3. Be sure the Page N and ‘Top of Page [Header]’ option buttons are selected.
4. If necessary, click the Alignment arrow and select Left (Page Numbers dialog box) (Figure 51).

Figure 51

5. Click OK (Page Numbers dialog box) to add the page number to the Report Header section.
6. Click the ‘Date and Time’ button (Report Design Tools Design tab | Header/Footer group) to
display the Date and Time dialog box.
7. Click the option button for the third date format and click the Include Time check box to
remove the check mark (Figure 52).

Figure 52

32 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

8. Click the OK button (Date and Time dialog box) to add the date to the Report Header.
9. Display the Home tab.
10. If the Date control is no longer selected, click the Date control to select it (Figure 53).

Figure 53

11. With the control containing the date selected, click the Cut button (Home tab | Clipboard
group) to cut the date, click the title bar for the Page Header to select the page header, and
then click the Paste button (Home tab | Clipboard group) to paste the Date control at the
beginning of the page header.
12. Drag the Date control, which is currently sitting on top of the Page Number control, to the
position shown in Figure 54.

Figure 54

33 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-3z To Remove the Header Background Color and the Alternate Color

1. Right-click the title bar for the Report Header to select the header without selecting any of the
controls in the header and produce a menu.
2. Point to the ‘Fill/Back Color’ arrow to display a color palette (Figure 55).

Figure 55

3. Click White, Background 1 in the first row, first column of the color palette to change the
background color for the header.
4. Right-click a blank area of the Detail section to select the details section and produce a menu.
5. Point to the ‘Alternate Fill/Back Color’ arrow to display a color palette (Figure 56).

Figure 56

6. Click None on the color palette to remove the alternate color for the Detail section.
7. Save and then close the report. Open the subreport in Design view.

34 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

8. Remove the header background color and the alternate color from the subreport, just as you
removed them from the main report.
9. Save and then close the subreport (Figure 57).

Figure 57

AC 6-3aa Headers and Footers

Access gives you some options for including or omitting headers and footers in
your reports. They go together, so if you have a report header, you will also have
a report footer. If you do not want one of the sections to appear, you can shrink its
size so there is no room for any content, or you can remove the header or footer
from your report altogether. If you later decide you want to include them, you once
again can add them. You have similar options with page headers and page footers.

To Remove a Report Header and Footer

To remove a report header and footer, you would use the following steps.

1. With the report open in Design view, right-click any open area of the report to produce a
shortcut menu.
2. Click the ‘Report Header/Footer’ command on the shortcut menu to remove the report
header and footer.
3. If the Microsoft Access dialog box appears, asking if it is acceptable to delete any controls
in the section, click Yes.

To Remove a Page Header and Footer

To remove a page header and footer, you would use the following steps.

1. With the report open in Design view, right-click any open area of the report to produce a
shortcut menu.
2. Click the ‘Page Header/Footer’ command on the shortcut menu to remove the page
header and footer.
3. If the Microsoft Access dialog box appears, asking if it is acceptable to delete any controls
in the section, click Yes.

To Insert a Report Header and Footer

To insert a report header and footer, you would use the following steps.

35 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

1. With the report open in Design view, right-click any open area of the report to produce a
shortcut menu.
2. Click the ‘Report Header/Footer’ command on the shortcut menu to insert a report
header and footer.

To Insert a Page Header and Footer

To insert a page header and footer, you would use the following steps.

1. With the report open in Design view, right-click any open area of the report to produce a
shortcut menu.
2. Click the ‘Page Header/Footer’ command on the shortcut menu to insert a page header
and footer.

To Include an Image in a Report

You can include a picture (image) in a report. You can also use a picture (image)
as the background on a report. To include an image in a report, you would use the
following steps.

1. Open the report in Design view or Layout view.

2. Click the Insert Image button (Report Design Tools Design tab | Controls group), and then
click the Browse command.
3. Select the desired image.
4. Click the desired location to add the image to the report.

To Use an Image as Background for a Report

To include an image as a background for a report, you would use the following
steps.

1. Open the report in Design view or Layout view.

2. Click anywhere in the report, click the Background Image button (Report Design Tools
Format tab | Background group), and then click the Browse command.
3. Select the desired image for the background.

To Publish a Report
You can make a report available as an external document by publishing the report
as either a PDF or XPS file. If you wanted to do so, you would use the following
steps.

1. Select the report to be published in the Navigation Pane.

2. Display the External Data tab.
3. Click the PDF or XPS button (External Data tab | Export group) to display the Publish as
PDF or XPS dialog box.
4. Select the appropriate Save as type (either PDF or XPS).
5. Select either ‘Standard (publishing online and printing)’ or ‘Minimum size (publishing
online).’
6. If you want to publish only a range of pages, click the Options button and select the desired
range.
7. Click the Publish button to publish the report in the desired format.
8. If you want to save the export steps, click the ‘Save export steps’ check box, then click the
Save Export button. If not, click the Close button.

36 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Creating a Second Report

CMF Vets managers would also like a report that groups accounts by Owner ID and Treatment
Costs. The report should include subtotals and a discount amount based on the cost of the
treatments. Finally, it should show the cost of the treatment less the discount. The discount
amount is based on the treatment cost. Accounts that have treatment costs more than $20 will
receive a 20 percent discount, and accounts that have treatment costs $20 or less will not receive
a discount.

AC 6-4a To Create a Second Report

1. If necessary, close the Navigation Pane.

2. Display the Create tab and then click the Report Design button (Create tab | Reports group)
to create a report in Design view.
3. Ensure the selector for the entire report, which is the box in the upper-left corner of the
report, contains a small black square indicating it is selected, and then click the Property Sheet
button (Report Design Tools Design tab | Tools group) to display a property sheet.
4. With the All tab selected, click the Record Source property box arrow, and then click the
Owners and Account Information query to select the query as the record source for the report.
5. Close the property sheet.
6. Click the Group & Sort button (Report Design Tools Design tab | Grouping & Totals group) to
display the ‘Group, Sort, and Total’ pane.
7. Click the ‘Add a group’ button to display the list of available fields for grouping, and then click
the Owner ID field to group by Owner ID. When you add a group, a header will appear above
each Owner ID.
8. Click the ‘Add a sort’ button to display the list of available fields for sorting, and then click the
Appointment Date field to sort by appointment date.
9. Remove the ‘Group, Sort, and Total’ pane by clicking the Group & Sort button (Report Design
Tools Design tab | Grouping & Totals group).
10. Click the Save button on the Quick Access Toolbar, type Discount Report as the report name,
and click OK to save the report.

AC 6-4b To Remove the Color from the Report Header

1. Click the View button arrow and then click Design View to return to Design view.
2. Right-click the report header to produce a menu.
3. Point to the ‘Fill/Back Color’ arrow on the shortcut menu to display a color palette.
4. Click White, Background 1 in the first row, first column to change the background color to
white.

AC 6-4c To Add and Move Fields in a Report

1. Click the ‘Add Existing Fields’ button (Report Design Tools Design tab | Tools group) to
display a field list (Figure 58).

37 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 58

2. Drag the Owner ID field to the approximate position shown in Figure 59.

Figure 59

3. Click the label for the Owner ID control to select it (Figure 60).

38 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 60

4. Display the Home tab.

5. Click the Cut button (Home tab | Clipboard group) to cut the label.
6. Click the Page Header bar to select the page header (Figure 61).

Figure 61

7. Click the Paste button (Home tab | Clipboard group) to paste the label in the Page Header
section (Figure 62).

39 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 62

8. Click in the label to produce an insertion point, use backspace or delete to erase the current
entry in the label, and then type Owner ID Number as the new entry.
9. Click in the label in front of the word, Number, to produce an insertion point.
10. Press shift+enter to move the word, Number, to a second line. Move and resize the Owner ID
label and control as necessary to match (Figure 63).

Figure 63

AC 6-4d To Add the Remaining Fields

1. Select the Owner First Name, Owner Last Name, Appointment Date, Treatment Number, and
Cost fields as shown in Figure 64.

40 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 64

2. Drag the Owner First Name, Owner Last Name, Appointment Date, Treatment Number, and
Cost fields into the Detail section and close the field list as shown in Figure 65.

Figure 65

3. One at a time, move and resize the Owner First Name and Owner Last Name Appointment
Date, Treatment Number, and Cost control labels to the Page Header section as shown
in Figure 66.

41 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 66

4. One at a time, move and resize the Owner First Name and Owner Last Name controls to the
approximate positions in the Owner ID Header section.
5. Arrange the remaining controls in the Detail section as shown in Figure 67.

Figure 67

AC 6-4e Totals and Subtotals

42 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

1. Use the Group and Sort pane to add an Owner ID footer.

2. Click the Text Box tool (Report Design Tools Design tab | Controls group), and then point to
the position shown in Figure 68.

Figure 68

3. Click the position shown in Figure 68 to place a text box (Figure 69).

Figure 69

4. Click the text box to produce an insertion point.

5. Type =Sum([Cost]) in the control to enter the expression calculation, and then press enter.
6. Format the calculation to currency by clicking the Report Design Tools Format tab and
clicking the ‘Apply Currency Format’ button.

43 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

7. Click the text box label to select it.

8. Click the label a second time to produce an insertion point.
9. Use the delete or backspace key to delete the Text12 label (your number might be different).
10. Type Subtotal as the label. Click outside the label to deselect it and then drag the label to the
position shown in Figure 70.

Figure 70

AC 6-4f To Add Subtotals and Other Calculations

1. Click the Text Box tool (Report Design Tools Design tab | Controls group), and then click the
position in the Owner ID Footer section, as shown in Figure 71.

Figure 71

2. Click the text box to produce an insertion point.

44 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

3. Type =IIf([cost]>20,[cost]*0.2,0) in the control to enter the expression

calculation as shown in Figure 71, and then press ENTER.
4. Click the text box label to select it.
5. Click the label a second time to produce an insertion point.
6. Use DELETE or BACKSPACE to delete the Text18 label (your number might be different).
7. Type Discount as the label.
8. Format the calculation to currency by clicking the Report Design Tools Format tab and
clicking the ‘Apply Currency Format’ button.
9. Switch between Design view and Layout view to review the results by clicking the (Report
Design Tools) Design tab and selecting the View arrow.

AC 6-4g To Add a Header and Footer to the Discount Report

1. Right-click any open area of the report to display a shortcut menu (Figure 72).

Figure 72

2. Click ‘Report Header/Footer’ to display the Report Header and Footer sections.
3. Click the Subtotal control in the Owner ID Footer section to select the control.
4. Display the Home tab.
5. Click the Copy button (Home tab | Clipboard group) to copy the selected controls to the
Clipboard.
6. Click the Report Footer bar to select the footer, and then click the Paste button (Home tab |
Clipboard group) to paste a copy of the control into the report footer (Figure 73).
7. Click the label in the Report Footer section to select the label, and then click a second time to
produce an insertion point.
8. Use the backspace or delete key to erase the current contents, type Total to change the label,
and then move the label to the position shown in Figure 74.
9. Save your changes.

45 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

Figure 73

Figure 74

AC 6-4h To Change the Can Grow Property

1. Select the Owner Last Name control.

2. Display the property sheet and scroll down until the Can Grow property appears.
3. Click the Can Grow property box and then click the Can Grow property box arrow to display
the menu of available values for the Can Grow property.
4. Click Yes to change the value for the Can Grow property.
5. Close the property sheet.

AC 6-4i To View the Report

1. Click the View button arrow on the Home tab to display the View button menu.
2. Click Report View on the View button menu to view the report in Report view.

46 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

3. Scroll down to the bottom of the report so that the total appears on the screen.

AC 6-4j To Use Expression Builder to Add a Calculation

1. With the Discount Report in Design view, click the Subtotal control in the Owner ID Footer to
select it.
2. Click the Property Sheet button (Report Design Tools Design tab | Tools group), and then click
the Other tab.
3. Click in the Name box, delete any existing text, if necessary, and then type Subtotal for the
Name (Figure 75).

Figure 75

4. If necessary, drag the property sheet to the right, and then click the IIf calculation control to
select it.
5. Click in the Name box in the Property Sheet Other tab and delete any existing text, if necessary.
6. Type Discount for the Name (Figure 76) and then close the Property Sheet dialog box.

Figure 76

47 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

7. Click the Text Box tool (Report Design Tools Design tab | Controls group), and then click in
the approximate position in the Detail section to place the control for the calculation (Figure
77).

Figure 77

8. Click the text box control and then click the Property Sheet button.
9. Click the All tab, click the Control Source property, and then click the Build button to display
the Expression Builder dialog box.
10. Click Discount Report in the Expression Elements category in the first column.
11. Type an equal sign ( = ) in the Expression Builder box.
12. Scroll down in the second column so that Subtotal appears, and then double-click the Subtotal
control in the second column.
13. Type a minus sign (−), scroll down in the second column so that Discount appears, and then
double-click the Discount control to add it to the expression (Figure 78).

Figure 78

48 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

14. Click OK to close the Expression Builder dialog box and then close the Property Sheet dialog
box.
15. Click the label for the expression field and then click the label for the expression field a second
time to produce an insertion point.
16. Use backspace or delete to erase the current entry in the label, and then type Cost After
Discount as the new entry.
17. Click the Expression Calculation control, click the Format tab, and then click the ‘Apply
Currency Format’ button to apply the currency format to the calculated expression.
18. Click the Report View button to view the report (Figure 79).
19. Save your changes.

Figure 79

AC 6-4k To Add a Title

1. Switch to Design view. Display the Report Design Tools Design tab, if necessary, and then
click the Title button (Report Design Tools Design tab | Header/Footer group) to add a title.
2. Type Discount Report for Treatment Costs as the title.
3. Click outside the title to deselect the title.
4. Click the title again and then click the (Report Design Tools) Format tab.
5. Click the Bold button to format the title.
6. View the report in Print Preview (Figure 80).
7. Close Print Preview.

Figure 80

49 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS
 TUTORIAL MODULE 6 – ADVANCED REPORT TECHNIQUES

AC 6-4l To Change the Border Style

1. Select all controls in the report besides the Title in the Report Header. You can click the first
one, and then hold SHIFT while clicking all the others. Alternatively, you can click in the ruler
to the left of the Page Header section and then hold SHIFT while clicking to the left of all the
other remaining sections.
2. Display the Report Design Tools Design tab.
3. Click the Property Sheet button (Report Design Tools Design tab | Tools group) to display the
property sheet.
4. Click the Border Style property box and then click the Border Style property box arrow to
display the menu of available border styles.
5. Click Transparent to change the border style. Close the property sheet.

AC 6-4m To Remove the Alternate Color

1. Right-click the Detail section to produce a shortcut menu.

2. Point to the Alternate Fill/Back Color arrow to produce a color palette.
3. Click None on the color palette to specify that there is to be no alternate color for the selected section.
4. Using the same techniques, remove the alternate color from all other sections. (For some sections, the
command may be dimmed.)

50 | P a g e
ASM510 – OFFICE SOFTWARE APPLICATIONS