Modifying a Basic Report Wizard Report

The Report Wizard tries to create the optimum final report in the first pass. Usually, the wizard comes close enough to a finished product that you spend far less time modifying a wizard-created basic report than creating a report from the default blank template.

In the following sections, you use Access's report design features to make the report more attractive and easier to read.

Deleting, Relocating, and Editing Existing Controls

The first step in modifying the wizard's report is to modify the existing controls on the report. You don't need to align the labels and text boxes precisely during the initial modification; the section "Aligning Controls Horizontally and Vertically" later in this chapter covers control alignment. To modify the wizard's report in order to create space for adding additional controls, follow these steps:

1.	Click the Close button of the Print Preview toolbar to enter Report Design mode; click the Maximize Window button to maximize the design window, if necessary. The Products on Hand by Category report, as created by the Report Wizard, appears as shown in Figure 14.14.
2.	The SupplierID and CompanyName fields are redundant in this report because the SupplierID field is a lookup field. Select the Company Name label in the Page Header section, then hold down the Shift key, and click the CompanyName field in the Detail section. Press Delete to remove the field and label from the report. (Don't worry about aligning the fields and labels yet.) Figure 14.14. The basic report in Design mode (all toolbars have been hidden to make the entire report visible).
3.	This report is more useful if you include the dollar value of both the inventory and number of units on hand. To accommodate one or two additional columns, you must compress the fields' widths. CategoryID occupies a column, but you can display this column's content in the CategoryID footer (or header) without using the extra column space. Select and delete the CategoryID label from the Page Header section, and select and delete the CategoryID text box from the CategoryID Header section. For this report, you'll put the CategoryID name in the footer section of the group, so drag the Detail section bar upward to eliminate the space occupied by the CategoryID Header. Your report appears as shown in Figure 14.15.
4.	All of the Page Header labels, Detail text boxes, and Totals text boxes in the CategoryID Footer and Report Footer sections must move to the left as a group. Click the Product ID label to select it, and then press and hold down Shift. Click the remaining Page Header labels, each of the Detail text boxes, the three summary field text boxes in the CategoryID Footer section, and the Grand Total text box in the Report Footer section. Then release Shift. (To select all of the labels and text boxes, you'll need to scroll the report to the right and left and up and down.)
5.	Position the mouse pointer over the Product ID label at a location where the pointer turns into the graphic of a palm of a hand. Hold down the left mouse button and drag the selected fields to the left margin. Your report appears as shown in Figure 14.16.
6.	You can more easily edit and position the labels if you left-justify them. Click a blank area of the report to deselect the group, select all of the Page Header labels, and click the Align Left button on the toolbar. Do the same for the Grand Total label in the Report Footer section. Figure 14.15. The basic report after deleting the CategoryID label and text box and closing the space for the CategoryID Header.
7.	Edit the Product ID label to read Product, and edit the Units In Stock label to read only Units. Select all of the labels in the Page Header and choose Format, Size, To Fit. Resize the widths of the ProductID, SupplierID, and UnitsInStock text boxes in the Detail section to match the width of the labels in the Page Header. Relocate the labels to provide more space on the right side of the report, as shown in Figure 14.17.
8.	By default, the Report Wizard adds to the CategoryID Footer a calculated field (visible in Figure 14.17) that displays the group's field name (CategoryID) and value to help identify the group footer's summary fields. For example, for CategoryID 1, the calculated field displays the following: "Summary for 'CategoryID' = 1 (12 detail records)." For this report, you want a more explicit description of the product category—more than just the CategoryID number. Delete this calculated field; you'll replace it in the next step. Figure 14.16. Moving selected labels and text boxes to the report's left margin. Figure 14.17. The Products on Hand by Category report after you edit, resize, and relocate existing controls.
9.	You now need to add a bound text box to identify the subtotal in the CategoryID Footer section. Click the Field List button on the toolbar. Select CategoryID from the list in the Field List window.
10.	Click and drag the field symbol mouse pointer to the left margin of the CategoryID Footer. Because the CategoryID field is a lookup field, it displays with a drop-down list button for the field box. When printed or displayed in Print Preview, this field shows the CategoryID name rather than the numeric code. Click the Field List button on the toolbar to close the Field List window.
11.	Select the label of the CategoryID field that you just placed, and use the Font and Size drop-down lists on the Formatting toolbar to set the label's font to Arial and the label's size to 8 points. Next, select the CategoryID text box, click the Bold button on the toolbar to add the bold attribute to the CategoryID text box, and also select the Arial font at a size of 8 points. Figure 14.18 shows the new bound CategoryID field in place of the calculated field that you deleted in step 8. (In the figure, the report has been scrolled upward in the window to make the Report Footer and Page Footer sections visible.) Figure 14.18. The Products On Hand by Category report, after adding the CategoryID field to the CategoryID Footer section.
12.	Drag the two calculated fields in the Page Footer section until they are one grid mark away from the top of the Page Footer section. Drag the Report Footer bar upward to reduce the Page Footer's height, as shown in Figure 14.18.
13.	For this report, the Average field is unnecessary; delete it and its label, and then rearrange the remaining fields and labels. Click and drag the =Sum([UnitsInStock])/[UnitsInStock Grand Total Sum] text box from its present location below the =Sum([UnitsInStock]) text box to a position at the top of the CategoryID Footer, near the page's right edge. Drag the =Sum([UnitsInStock]) field to a position at the top of the CategoryID text box and near the center of the page. Finally, move up the Page Footer divider bar to reduce the footer's depth (see Figure 14.19).
14.	Click the toolbar's Save button to save your report. Figure 14.19. The completely modified Products On Hand by Category report in Design mode.

To check the progress of your work, periodically click the toolbar's Print Preview button to display the report prior to printing. At this point, your Products On Hand by Category report appears in Print Preview mode, as shown in Figure 14.20.

Using the DLookUp() Domain Aggregate Function to Print Product Category Names

As you saw in the preceding section of this chapter, lookup fields in a table are placed on a report as a limited-function combo box. You can't use the combo box to select values, but it causes a value looked up from another table to be displayed instead of the actual field value. For example, if you place the CategoryID field on the report (as you did in the preceding section), Access displays the category name in the report rather than the actual CategoryID number, because the CategoryID field in the Products table has lookup field properties assigned to it. When you place the CategoryID field on the report, Access automatically creates a combo box control with the properties needed to look up the CategoryName field from the Categories table.

Not every table that you use in your reports will have lookup fields, however, nor is it necessarily desirable to create lookup fields for all numeric code fields (such as CategoryID and SupplierID). If you want to display a looked-up value for a field that isn't defined as a lookup field, you use Access's domain aggregate function, DLookUp(), to find values from another table that correspond to a value in one of the report's fields. For example, if you want to display both the actual CategoryID number and the CategoryName in the Group Footer of the Products On Hand by Category report, you can use the DLookUp() function to display the text of the CategoryName field from the Categories table, and a bound text field to display the CategoryID number from the Products table.

To change the CategoryID combo box control to a standard text box and add a new Category Name field that uses the DLookUp() function to the CategoryID Footer section of the Products On Hand by Category report, follow these steps:

1.	In Report Design mode, select the CategoryID combo box, and then choose Format, Change To, Text Box to convert the combo box to a regular text box field. This field will no longer display the looked-up CategoryName field but will display the actual number stored in the CategoryID field.
2.	Edit the CategoryID field label to read ID:, then resize the CategoryID text box so that it is approximately 3/8 of an inch wide. Move both the text and field boxes closer together, near the left edge of the report's CategoryID Footer section (see Figure 14.21).
3.	Click the Toolbox button to display the toolbox if it is not already displayed.
4.	Click the Text Box tool and add a new unbound text box to the right of the CategoryID text box with the approximate dimensions shown in Figure 14.21.
5.	Delete the field label for the new text box control, and give the text box itself the bold text attribute.
6.	Click the new text box, and type the following as the text box's value: =DLookUp("[CategoryName]","Categories","[CategoryID] = Report!CategoryID") & "Category" [CategoryName] is the value that you want to return to the text box. Categories is the table that contains the CategoryName field. [CategoryID] = Report!CategoryID is the criterion that selects the record in the Categories table with a CategoryID value that is equal to the value in your report's CategoryID text box. The Report prefix is necessary to distinguish between the CategoryID field of the Categories table and a control object of the same name. (Report is necessary in this example because Access has automatically named the report's CategoryID text box control as CategoryID.) Figure 14.21. The transformed and resized CategoryID field with the new, unbound text box containing the DLookUp() function.
7.	Select the CategoryID text box and verify that the name CategoryID appears in the Object Name text box (at the left side of the Formatting toolbar). If not, click the Properties button of the toolbar, select the Other tab in the Properties window, and type CategoryID as the value of the control's Name property (see Figure 14.22).
8.	Click the Report View button of the toolbar. Your Products On Hand by Category report appears as shown in Figure 14.23. Figure 14.22. Verifying the name of the CategoryID text box. Figure 14.23. The Products On Hand by Category report with the DLookUp() field added.

Adding Other Calculated Controls to the Report

Calculated controls, such as the DLookUp() control that you added in the preceding section, are quite useful in reports. You use calculated controls to determine extended values, such as quantity times unit price or quantity times cost. Now you have enough space at the right of the report to add a column for the UnitPrice field and a column for the extended inventory value, which is UnitPrice multiplied by UnitsInStock. To add these controls, follow the steps described in the following subsections.

Changing the Report's Record Source. You created the Products On Hand by Category report by selecting fields directly from the Products and Suppliers table in the Report Wizard. As a result, the Record Source property for the report, as a whole, is a SQL statement that selects only those fields that you chose initially in the Report Wizard. Although it's possible to add fields to the report by creating unbound text box controls and using the Expression Builder to create an expression to retrieve the desired value, it's much easier to create a query to select the fields desired for the report and substitute the new query as the report's data source.

To create a query for use with the Products on Hand by Category report, follow these steps:

1.	Click the toolbar's Database Window button to display the Database window. Click the Queries tab and then click New to create a new query.
2.	In the New Query dialog, select Design View and then click OK.
3.	Double-click the Products table in the Show Table dialog to add the table to the query. Then click Close to dismiss the Show Table dialog.
4.	Drag the * field to the first column of the query grid to add to the query all of the Products table's fields.
5.	Drag the Discontinued field to the query grid's second column.
6.	Clear the Show check box for the Discontinued field, and then type =False in the Discontinued field's first Criteria row (see Figure 14.24).
7.	Click the Close Window button to close the Query window. A prompt asks whether you want to save changes to the query design. Click Yes.
8.	In the Save As dialog's Query Name text box, type qryProductOnHand. Then click OK to save the query.

The query that you have just created contains all of the fields from the Products table and excludes discontinued products from the record set. (In other words, the query includes only those records whose Discontinued field contains the False or No value.)

To change the report's Record Source property, follow these steps:

1.	Open the Products on Hand by Category report in Design mode.
2.	Choose Edit, Select Report.
3.	Click the toolbar's Properties button to open the report's Properties window. Then click the Data tab to display the report's various data properties.
4.	Click the Record Source text box, and then use the drop-down list to select the qryProductOnHand query as the report's new Record Source property.
5.	Click the toolbar's Save button to save the changes to the report.

Adding the Calculated Fields. Now that you've changed the report's record source, you have easy access to the UnitPrice field that you need for adding the additional calculated fields to the report. To add the UnitPrice field and the Value calculated field to the report, follow these steps:

1.	Display the Products on Hand by Category report in Design mode, if necessary. Then click the toolbar's Toolbox button to display the Access toolbox if it isn't already displayed.
2.	Click the Label tool in the toolbox and place the label to the right of the Units label in the Page Header section. Type Price as the label.
3.	Add another label to the right of Price and type Value.
4.	Click the toolbar's Field List button to display the Field List window. Select UnitPrice and drag the field symbol to a position under the Price label in the Detail section. Drop the text box, and then delete the UnitPrice field's label in the report's Detail section.
5.	To create the calculated Value text box, click the Text Box button in the toolbox and add the text box to the right of the Unit Price text box. Tip Entering expressions is easier if you display the Properties window and enter the expression as the Control Source property. Press Shift+F2 to open the Zoom box so you can see the entire expression as you enter it.
6.	Type =[UnitsInStock]* [UnitPrice] as the expression for the Value text box. Delete the field label for this text box in the report's Detail section.
7.	Drag the Page Footer section bar downward to increase the height of the report's CategoryID Footer section, and rearrange the text labels and fields as shown in Figure 14.25. Edit the Sum label to read Total: and the Percentage label to read Percentage:. Figure 14.25. Rearranging the CategoryID Footer section to make room for additional fields.
8.	Repeat steps 5 and 6 to create the Value Subtotal text box in the CategoryID Footer section, but type =Sum([UnitsInStock]*[UnitPrice]) as the subtotal expression. Click the toolbar's Bold button to set the Font Weight property to Bold. In the Properties window, click the Other tab and then set this text box's Name property as txtTotalValue.
9.	Repeat step 8 to create the Value grand total box in the Report Footer section. In the Other page of the Properties window, set this text box's Name property as txtGrandTotalValue.
10.	Add another unbound text box underneath the txtTotalValue text box. Type =[txtTotalValue]/[txtGrandTotalValue] as the value of the Control Source property and set the Format property's value to Percent. The report design appears as shown in Figure 14.26. Tip If the Parameter dialog appears, you misspelled one or more field names in the expressions. Click Cancel and check the properties that you added in steps 6 through 10. Figure 14.26. Adding the Price, Value, Total Value, Total Value Pcnt, and Grand Total Value fields to the report.
11.	Click the toolbar's Report View button to check the result of your additions. The report appears as in Figure 14.27. Use the vertical scroll bar, if necessary, to display the category subtotal. The next section describes how you can correct any misaligned values and the spacing of the Detail section's rows.
12.	Click the Bottom of Report page selector button to display the grand totals for the report (see Figure 14.28). The record selector buttons become page selector buttons when you display reports in Run mode. Figure 14.27. Page 1 of the report, with calculated product values and value subtotals. Figure 14.28. The last page of the report, with grand totals for Units and Value.

Aligning and Formatting Controls, and Adjusting Line Spacing

On reports, the exact alignment of label and text box controls is more important than alignment on forms because in the printed report, any misalignment is obvious. Formatting the controls further improves the report's appearance and readability.

The spacing of the report's rows in the Detail section is controlled by the section's depth. Likewise, you can control the white space above and below the headers and footers by adjusting the depth of their sections and the vertical position of the controls within the sections. To create a professional-looking report, you must adjust the controls' alignment and formatting as well as the sections' line spacing.

Aligning Controls Horizontally and Vertically. You align controls by first selecting the rows to align and then aligning the columns. Access provides several control-sizing and alignment options to make the process easier. To size and align the controls that you created, follow these steps:

1.	Click the Report View toolbar's Close button to return to Design mode.
2.	You can simultaneously adjust the height of all text boxes to fit the font used for their contents. Choose Edit, Select All to select all of the controls in the report.
3.	Choose Fo rmat, Size, To Fit to adjust the height of the selected controls. Access adjusts all of the controls to the proper height. To deselect all of the controls, click a blank area of the report.
4.	Select all labels in the Page Header sections. Choose Format, Align, Top. This process aligns the tops of each selected label with the uppermost selected label. Click a blank area of the report to deselect the labels.
5.	Select all text boxes in the Detail section, and repeat step 4 for the text boxes.
6.	Select the labels and text boxes in the CategoryID Footer and Report Footer sections and repeat step 4.
7.	Select all controls in the Units column. Choose F ormat, Align, Right so that Access aligns the column to the right edge of the text farthest to the right of the column. Next, click the toolbar's Align Right button to right-align the contents of the labels and text boxes. (The first part of this step aligns the controls themselves to the right-most control, and the second part right-aligns the text or data displayed by the selected controls.)
8.	Select all controls in the Price column and repeat step 7.
9.	Select all controls in the Values column (except the Page Footer text box) and repeat step 7.
10.	Click the toolbar's Report View button to display the report with the improved alignment of rows and columns.

Formatting Controls. As you can see in Figure 14.27, you must revise the formatting of several controls. Although ProductID values are right-aligned, centering or left-justification is more appropriate for values used as codes rather than numbers to total. The repeated dollar signs in the Unit Price field detract from the report's readability, and the Value column's left-justification is inappropriate.

To change the Format property of these fields, follow these steps:

1.	Click the toolbar's Close button to return to Design mode.
2.	Select the ProductID text box in the Detail section and click the toolbar's Center button.
3.	Select and then center the CategoryID text box in the CategoryID Footer section.
4.	Double-click the Unit Price text box to open its Properties window, then click the Format tab of the Properties window.
5.	In the Format text box, type #,#00.00. This procedure eliminates the dollar sign but preserves the monetary formatting.
6.	Repeat steps 4 and 5 for the Values text box. The Detail section doesn't require dollar signs. Tip If you select Currency formatting instead of typing $#,#00.00 to add a dollar sign to the value, your totals do not align. Currency formatting offsets the number to the left to provide space for the parentheses that accountants use to specify negative monetary values.
7.	Select the Values subtotal in the CategoryID Footer. Click the Properties window's Format tab and type $#,#00.00 in the Format field. Accountants use dollar signs to identify subtotals and totals in ledgers.
8.	Select the Values grand total in the Report Footer and type $#,#00.00 as the Format property of the Values grand total.
9.	Select the Total Value Percent text box in the CategoryID Footer. Choose Percent from the Format property's drop-down list to display the text box's contents as a percentage.
10.	The Values grand total in the Report Footer is the report's most important element, so click the toolbar's Line/Border Color button and then click the black box to give this field a black border. Next, click the Line/Border Width button and then the 2-point border button. This procedure increases the thickness of the border around the grand total.
11.	Click the toolbar's Report View button to check your formatting modifications. Click the Bottom of Report page selector button to display the last page of the report (see Figure 14.29).

Adjusting Line Spacing. In the Page Header section, shown in Figure 14.27, the controls are placed further apart than is necessary, and the depth of the controls in the Report Header section is out of proportion with the size of the text. The line spacing of the remainder of the report's sections is satisfactory, but you can also change this spacing. Minimizing line spacing allows you to print a report on fewer sheets of paper.

To change the spacing of the report's Page Header and Detail sections, follow these steps:

1.	Click the toolbar's Close button to return to Design mode.
2.	Select all of the labels in the Page Header and move the group as close to the top of the section as possible.
3.	Click the bottom line of the Page Header and move the line as close to the bottom of the text boxes as possible. (To select the line, you may have to move the CategoryID Header section downward temporarily.)
4.	Click a blank area of the report and then move the CategoryID Header section to the bottom of the labels. You cannot reduce a section's depth to less than the Height property of the label that has the maximum height in the section.
5.	Select all of the text boxes in the Detail section, and move those boxes as a group to the top of the section. Move the CategoryID footer up to the bottom of the text boxes.
6.	Move the line and label in the Report Header section upward to minimize the amount of white space in the Report Header.
7.	Click the toolbar's Report View button to check the Page Header depth and line spacing of the Detail section. The spacing shown in Figure 14.30 is close to the minimum that you can achieve. You cannot reduce a section's line spacing to less than that required by the tallest text box or label by reducing the section's Height property in the Properties box because Access rejects the entry and substitutes the prior value.
8.	Click the toolbar's Zoom button to display the report in full-page view. Clicking the mouse when the pointer is the magnifying glass symbol has the same effect as clicking the Zoom button. Alternate clicks toggle between full-size and full-page views.
9.	Choose File, Save to save your changes. Figure 14.30. The report in Report view after you adjust the depth of the Report Header, Page Header, and Detail sections.