Core Training for Microsoft Office Access 2003

Working with Reports

Creating and Exploring Reports

1To create a report based on the Products table, in the Database window, click Products, click the Insert menu, and then click Report.
2To launch the Report Wizard, in the New Report dialog box, click Report Wizard, and then click OK.
3Note that the Products table is displayed in the Table/Queries box. To move a field from the Available Fields list to the Selected Fields list, click ProductName, and then click the > button.
4To select another table on which to base the report, click the Tables/Queries arrow, click once above the scroll box in the scroll bar, and then click Table:Categories.
5To insert the next field below the ProductName field, in the Selected Fields list, click ProductName, and then in the Available Fields list, double-click CategoryName, and click Next.
6To accept the default in the How do you want to view your data list to group the records by Products, click Next.
7To establish a grouping level, in the field list, double-click ProductName to move it to the top of the sample report on the right.
8To establish grouping intervals for your group-level fields, click the Grouping Options button.
9To group records by first letter, in the Grouping Intervals dialog box, click the Grouping intervals arrow, click 1st Letter, click OK, and then click Next.
10To specify ascending sort order, click the arrow to the right of the first box, and then click ProductName in the list.
11To view the available summary options, click the Summary Options button.
12To close the Summary Options dialog box and move to the next page of the wizard, click Cancel, and then click Next.
13To select a report layout, click Outline 1. Notice that the Portrait orientation option and the Adjust the field width so all fields fit on a page option are both selected, and then click Next.
14To select a predefined report style, click Compact, and then click Next.
15To add a title to the report, for the purposes of this exercise, press SPACEBAR, and Alphabetical List of Products will be typed for you.
16To finish the report and preview it, notice that the Preview the report option is selected by default, and click Finish.
17To scroll down the first page of the report, click once below the scroll box on the vertical scroll bar.
18To display the entire first page of the report, move the pointer over the report, and when it changes to a magnifying glass with a minus sign in it, click once.
19To view the second page of the report, on the Navigation bar, click the Next Record button, as indicated.
20To view the two pages of the report simultaneously, on the Database toolbar, click the Two Pages button.
21To view multiple pages simultaneously, on the Database toolbar, click the Multiple Pages button, as indicated, point to the 1x1 Pages option, as indicated, and then click the 1x3 Pages option, as indicated.
22To close the report, click its Close button.

Modifying a Report

1To view the report, on the Objects bar, click Reports, click Alphabetical List of Products, and then, on the Database window toolbar, click Design.
2To view the two lines below the report title, note the indicated area below the Page Header, and then drag the top of the Page Header selector down to this indicated area.
3To delete one of the lines below the report title, in the Report Header section, click the top line, and then press DELETE.
4To insert a text box with its label, in the Toolbox, click the Text Box control, as indicated, and then in the Report Header section, click two inches to the right of the report title.
5To delete the label associated with the text box, click the label, and then press DELETE.
6To align the text box with the left edge of the form, drag the text box below the report title, as indicated.
7To view the text box properties, click the View menu, and then click Properties.
8To format and set the current date as the source data, click the Data tab, for the purposes of this exercise, press SPACEBAR, and the text will be typed for you in the Control Source box.
9To close the Text Box: Text23 window, click its Close button.
10To delete the bold lines above the labels, in the ProductName Header section, click the bold line, and then press DELETE.
11To delete a label, in the ProductName Header section, click ProductName by 1st, press DELETE, and then drag the text box to the left edge of the section, as indicated.
12To delete a text box, in the Page Footer section, click the =Now() text box, and then press DELETE.
13To select labels and text boxes in two sections, drag to select the text boxes and labels in the ProductName Header section and in the Detail section, as indicated.
14To move the entire group, drag the group to the indicated area on the left.
15To sort and group data, click the View menu, and then click Sorting and Grouping.
16To display a footer for the group, in the Group Properties area of the Sorting and Grouping window, click the Group Footer box, click the list arrow, and then click Yes.
17To keep the group together on one page, click the Keep Together box, click the list arrow, and then click Whole Group.
18To close the Sorting and Grouping window, click its Close button.
19To insert a short horizontal line in the ProductName Footer section, in the Toolbox, click the Line control, and then click near the top of the ProductName Footer section, as indicated.
20To view the properties of the line, click the View menu, click Properties, and then in the Line: Line25 window, click the Format tab.
21To change the starting position of the line, click the Left box, drag to select the text, press SPACEBAR, and the text will be typed for you.
22To set the width of the line, in the Line: Line25 window, click the Width box, drag to select the text, press SPACEBAR, and the text will be typed for you.
23To close the Line: Line25 window, click its Close button.
24To delete the lines at the top of the Page Footer section, in the Toolbox, click the Select Objects control, in the Page Footer section, click the line, and then press DELETE.
25To move a field and label, drag to select the Units In Stock label and text box in the ProductName Header and the Detail section, and then drag the selection to the indicated area on the right edge of the section.
26To move another field, drag to select the Quantity Per Unit label and text box in the ProductName Header section and in the Detail section, and then drag the selection to the right one inch.
27To preview the report, click the View menu, and then click Print Preview.
28To close the report, click its Close button, and in The Garden Company message box, click Yes.

Creating and Modifying a Report in Design View

1To open a blank report based on the Sales by Category query, click the Insert menu, click Report, and then double-click Design View.
2To add a report header and footer, click the View menu, and then click Report Header/Footer.
3To specify the fields to be used to group the records and add a group heading, click the View menu, and then click Sorting and Grouping.
4In the Sorting and Grouping dialog box, click the Field/Expression down arrow, click CategoryName, and then in the Group Properties area, double-click Group Header to change the setting to Yes.
5Notice the icon that indicates CategoryName is now a group heading, and the new CategoryName Header section in the Design view window. To close the Sorting and Grouping dialog box, click its Close button.
6To make the grid wider, in the Report properties dialog box, click below the scroll box on the scroll bar, double-click in the Grid X box, and then, for the purposes of this exercise, press SPACEBAR, and 10 will be entered for you.
7To increase the size of the report header, point to the line between the Report Header selector and the Page Header selector, and when the pointer becomes a double-arrow, drag it down to the indicated location.
8To add a title to the report, in the Toolbox, click the Label control, and then click the top of the Report Header section, at the 2 inch mark, as indicated.
9To expand the label as text is typed in it, press SPACEBAR, wait while Sales by Category is typed for you, and then press ENTER.
10To set the label's font properties, in the Label: Label0 properties dialog box, click below the scroll box on the scroll bar, on the Format tab, click Font Size, click the Font Size arrow, and then click 16 in the list.
11To expand the label to fit the new text, click the Format menu, point to Size, and then click To Fit.
12To display a date on the report so it appears below the title, click the Report Header section selector, click the Insert menu, and then click Date and Time.
13To set the date format, in the Date and Time dialog box, leave Include Date and the first (long) date format selected, clear the Include Time check box, and then click OK.
14To move the new text box below the report title, click the text box and when the pointer changes to a hand, drag it to the indicated location below the title.
15To center the date, on the Formatting toolbar, click the Center button.
16To insert a field into the report, in the Field List in the Sales... box at the right side of the screen, drag the CategoryName field to the top of the CategoryName Header section, as indicated.
17To delete the partially hidden CategoryName label, click it, and then press DELETE.
18To set the size and font properties for the CategoryName text box, click it, and then in the Text Box: CategoryName properties dialog box, click Font Size, click the Font Size down arrow, and then click 14 in the list.
19To resize the text box, drag its lower right corner to the indicated location.
20To add a label below the text box, on the Toolbox, click the Label button, and then click on the grid mark directly below the left edge of the CategoryName text box.
21To give the label a name and set the font properties, press SPACEBAR, wait while the text is typed in the label, and then press ENTER.
22To size the label, click the Format menu, point to Size, and then click To Fit.
23To insert a page number at the bottom of each page, click the Page Footer section selector, click the Insert menu, and then click Page Numbers.
24To set the number format, position, and alignment, in the Page Numbers dialog box, click Page N of M, click Bottom of Page [Footer], and then click OK.
25To save the report and give it a name, on the Report Design toolbar, click the Save button, press SPACEBAR, wait while Sales by Category is typed for you, and then click OK.
26To see how the report will look when printed, on the Report Design toolbar, click the View button.
27To close the report, click its Close button.

Adding Calculated Controls to Reports

1To open the selected Sales by Category report in Design view, on the Database window toolbar, click Design.
2To add an unbound subreport to your main form, on the Toolbox, click the indicated Subform/Subreport button, and then on the report, click the indicated location below the Product label.
3To base the subreport on an existing query, in the SubReport Wizard, note that the Use existing Tables and Queries option is selected, and click Next.
4To select the query on which to base the subreport, click the Tables/Queries arrow, click once below the scroll box on the scroll bar, and then click Query: Sales by Category in the list.
5To select the fields to include on the subreport, in the Available Fields list, double-click CategoryID, ProductName, and ProductSales to move them to the Selected Fields list, and then click Next.
6To link the CategoryID field on your main form to the CategoryID on the subform, click Next.
7To accept the suggested name, Sales by Category subreport, and insert the new subreport in the main report, click Finish.
8To change the width and height of the subreport, in the Subform/Subreport: Sales by Category subreport properties dialog box, click Width, and then, for the purposes of this exercise, press SPACEBAR, and wait while the settings are entered for you.
9To delete the Report Header and Footer sections of the subreport and the controls in them, click the indicated Report Header selector, and then on the View menu, click Report Header/Footer.
10In the message box asking if you are sure you want to delete the header and footer and all the controls in them, click Yes.
11To delete the subreport Page Header and Footer sections, click the View menu, click Page Header/Footer, and then click Yes to confirm the deletion.
12To delete a text box, in the Detail section of the subreport, click the CategoryID text box, and then press DELETE.
13To change the alignment and width of a text box, click the ProductName text box, in the Text Box: ProductName properties dialog box, click above the scroll box on the scroll bar, click Left, press SPACEBAR, wait while the properties are set for you, and then press ENTER.
14To change the alignment and width of the ProductSales text box, click the indicated ProductSales text box, and in the Text Box: ProductSales properties dialog box, click Left, and then press SPACEBAR to have the properties set for you.
15To delete the Sales by Category subreport label from the main report, click the label, and then press DELETE.
16To add blank footer to the report, click the indicated subreport Report selector twice, and then click the View menu, and click Report Header/Footer.
17To display the new Report Footer section, on the Report properties dialog box, click the Close button, and then click the indicated scroll arrow.
18To add a control in which to calculate the total product sales for each category, in the Toolbox, click the Text Box control, and then click the indicated location in the Report Footer section.
19To reopen the Properties dialog box, on the View menu, click Properties.
20To change the new label's caption and font properties, click the label, and then in the Label: Label9 properties dialog box, click Caption, and then press SPACEBAR, and the text will be entered for you.
21To open the Expression Builder to build an expression, click the unbound text box control, in the Text Box: Text8 properties dialog box, click the Data tab, and click the … button next to the Control Source box.
22To begin building the expression, in the first column, double-click Functions, click Built-In Functions, and then in the third column, drag the scroll box to the indicated location, and then double-click Sum.
23To finish building the expression, click <<exp>>, double-click Reports, double-click All Reports, click below the scroll box on the vertical scroll bar in the list, click the indicated Sales by Category subreport, and then in the second column, double-click ProductSales.
24To close the Expression Builder and enter the calculation in the unbound text box, click OK, and then in the Text Box: Text8 properties dialog box, press ENTER.
25To display the results of the calculation as currency, in the Text Box: Text8 properties dialog box, click the Format tab, click the Format arrow, in the list, click below the scroll box on the vertical scroll bar, and then click Currency.
26To change the font weight for the text box, in the Text Box: Text8 properties dialog box, click once below the scroll box, click Font Weight, click the Font Weight arrow, and then click Bold in the list.
27To have the new calculated control aligned with the ProductSales control in the Details section, in the Text Box: Text8 properties dialog box, click once above the scroll box, click Left, and then press SPACEBAR.
28To have the label aligned with the ProductName control in the Detail section, click the new Total: label, in the Label: Label9 properties dialog box, click Left, and then press SPACEBAR.
29To ensure that the subreport has no border, click the top-left corner of the subreport, as indicated, and then in the Subform/Subreport: Sales by Category subreport properties dialog box, click Border Style, click the Border Style arrow, and then click Transparent in the list.
30To save the report and preview it, on the Report Design toolbar, click the Save button, and then click the View button.