Modifying a Basic 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 rptInventoryByCategory 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 "Aligning Controls Horizontally and Vertically" section later in this chapter covers control alignment. To create space for additional controls on the report, follow these steps:

1. Open rptInventoryByCategory in Report Design mode, if it's not already open.

2. Click the Select Report button to select the entire report, and click the Properties button to open the Report properties window.

3. Change the Format page's Caption property value to Inventory by Category.

4. Click the label at the top of the page and make the same change. Your report now appears as shown in Figure 16.12.

   Figure 16.12. Change the Caption property value of the report and the top label to "Inventory by Category".

   

5. 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, 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.)

6. 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; do the same for the CategoryID text box from the CategoryID Header section (see Figure 16.13).

   Figure 16.13. Delete the CategoryID label and text box in the Page Header and Detail sections of the report, and remove unnecessary vertical whitespace.

   

7. For this report, you'll put the CategoryID name in the footer section of the group. Drag the Detail section bar upward to eliminate the space occupied by the CategoryID Header, and drag the Page Header up to reduce the space below the report caption. Your report now appears as shown in Figure 16.13.

8. All 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 (for a total of 12 controls). Now release Shift.

   Tip

   Enclosing the 12 controls by dragging a rectangle around them doesn't work in this case. There's a hidden line that, when selected, prevents dragging the fields to the left.

9. 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 16.14.

   Figure 16.14. Move the group of selected controls to the extreme left of the report.

   

10. 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 Page Header labels, and click the Align Left button on the toolbar.

11. Edit the Product ID label to read ID and edit the Units In Stock label to read only Units. Select all labels in the Page Header and choose Format, Size, To Fit. Resize the widths of the ProductID 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 for the Product Name and Supplier columns and gain additional space on the right side of the report, as shown in Figure 16.15.

    Figure 16.15. After editing the Page Header labels and resizing a few fields, your report design appears as shown here.

    

12. By default, the Report Wizard adds to the CategoryID Footer a calculated field (visible in Figure 16.15). The calculated field 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 in Print Preview mode:

     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 the Category ID Footer's calculated field that starts with ="Summary for" for now; you replace it in the next few steps. As usual, save your changes frequently.

Tip

Not every table that you use in your reports will have lookup fields, 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, to display both the actual CategoryID number and the CategoryName in the CategoryID Footer of the Inventory 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. The expression you use is

 =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 identifier 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.) Remember that the DLookUp function isn't available in ADP reports.

Printing the Lookup Field of a Table

To add a new field to display the CategoryName field in the CategoryID footer, and complete the redesign of the report, do the following:

1. For this report, the Avg field is unnecessary, so delete it and its label.

2. 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.

3. Click and drag the field symbol mouse pointer about a half inch from the left margin of the CategoryID Footer in place of the text box you deleted. 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.

4. Select the label of the CategoryID field that you just placed, press Shift and select the text box, and then use the Font and Size drop-down lists on the Formatting toolbar to set both controls' font to Arial and size to 8 points. Choose Format, Size, To Fit to adjust the size of the controls, and then drag the controls to the top of the Category ID Footer section. Figure 16.16 shows the new bound CategoryID field in place of the calculated field that you deleted in step 11 in the previous "Deleting, Relocating, and Editing Existing Controls" section.

   Figure 16.16. Replace the calculated field in the CategoryID Footer with the CategoryID lookup field.

   

5. Drag the two calculated fields (=Now and ="Page...") 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.

6. Click and drag the =Sum([UnitsInStock])/[UnitsInStock] text box from its present location below the =Sum([UnitsInStock]) text box to a position at the top of the CategoryID Footer, near the Center of the page. Then drag the Standard label to the left of the text box you moved, and change its caption to Percent: (look ahead to Figure 16.17).

   Figure 16.17. At this point, the design of the Inventory by Category report is ready for a test run.

   

7. Drag the =Sum([UnitsInStock]) field up to the bottom of the CategoryID footer and the Sum label to the left of the text box you moved, and add a colon (:) after Sum for consistency. Move the right edge of the text box to align with right edge of the UnitsInStock text box. Move up the Page Footer divider bar to reduce the footer's depth (again, look ahead to Figure 16.17).

   Tip

   To differentiate between calculated field text boxes that show only the first few characters of the expression, temporarily increase their width. Shift+F2 doesn't open the Zoom window for report text boxes, and there's no Zoom choice in the text boxes' context menu.

8. Select the Grand Total label and text box in the Report Footer section and move the text box to align its right edge with the right edge of the text boxes above it. Your final report design appears as shown in Figure 16.17. Press Ctrl+S to save your report design.

If you get a blank page after each page when you print or preview a report, see the suggestion in "Eliminating Empty Pages" in the "Troubleshooting" section near the end of this chapter.

To check the progress of your work, periodically click the Print Preview button to display the report prior to printing. Figure 16.18 shows your Inventory by Category report in Print Preview mode.

Figure 16.18. Print Preview displays the report design of Figure 16.17 at actual size (100% zoom).

Tip

If two-digit product codes appear in the report as 01 instead of their correct values, increase the width of the ProductID text box slightly.