Working from a Blank Report

Usually, the fastest way to set up a report is to use the Report Wizard to create a basic report and then modify the basic report as described in Chapter 16, and previous sections of this chapter. If you're creating a report style that the Wizard can't handle or a report containing a subreport, however, modifying a standard report style created by the Report Wizard could take longer than creating a report by using the default blank report that Access provides.

Using a Report as a Subreport

The report you design in the following sections includes information about total monthly orders for products by category. Comparing the monthly orders to the inventory level of a category allows the report's user to estimate inventory turnover rates. This report serves two purposes a primary report and a subreport within another report. You add the Monthly Orders by Category report as a subreport of the Inventory by Category report in the "Incorporating Subreports" section later in this chapter.

To create a report to use as the Monthly Orders by Category subreport (rpt1997MonthlyCategoryOrders) in the section of this chapter, "Adding and Deleting Sections of Your Report," you need to base the subreport on a query, qry1997MonthlyProductOrdersCT, adapted for this purpose.

A copy of the qry1997MonthlyProductOrdersCT query is included in the Report17.mdb database in the \Seua10\Chaptr17 folder of the accompanying CD-ROM.

To review how to create this crosstab query, see "Designing a Monthly Product Sales Crosstab Query," p. 448.

To modify the query for this subreport, follow these steps:

1. Close any open report(s) and click the Queries shortcut in the Database window.

2. Open the qry1997MonthlyProductOrdersCT query in Design view.

3. In the grid, change the first column's field name from ProductID to CategoryID by opening the Field drop-down list and clicking the CategoryID field name. You need the CategoryID field to link with the CategoryID field in the qryInventory query that the Inventory by Category report uses as its data source.

4. Delete the ProductName column. The modified query appears as shown in Figure 17.8.

   Figure 17.8. The query for inventory analysis uses RequiredDate rather than OrderDate to more accurately reflect the date on which an order became a sale.

   

5. Choose File, Save As and name the modified query qry1997MonthlyCategoryOrdersCT. Click Run to check the query. Your query result set appears as shown in Figure 17.9.

   Figure 17.9. The query includes a Grand Total column that's useful for calculating inventory turns (total yearly sales divided by inventory value).

   

6. Open the New Object drop-down list on the toolbar, and select Report from the list to open the New Report dialog.

7. Access automatically selects qry1997MonthlyCategoryOrdersCT as the query on which to base the report. Select Design View from the list and click OK. Access opens the default blank report shown in Figure 17.10.

   Figure 17.10. This blank report is the starting point for the subreport you add to the Inventory by Category report later in the chapter.

   

Creating the Monthly Sales by Category Report

The crosstab query that acts as the Monthly Sales by Category report's data source is closely related to a report, but the crosstab query doesn't include detail records. Each row of the query consists of subtotals of sales for a category for each month of the year. One row appears below the inventory value subtotal when you link the subreport (child) to the main (master) report, so this report needs only a Detail section. Each detail row, however, requires a header label to print the month. The CategoryID field is included so that you can verify that the data is linked correctly.

To complete the Monthly Sales by Category report (and later a subreport), follow these steps:

1. In the blank report you opened in the preceding section, remove the default Page Header and Page Footer sections by choosing View, Page Header/Footer to clear the toggle check mark. This subreport only requires a Detail section. By default, blank reports have 24x24 grid dots, and Snap to Grid is selected.

2. Drag the right margin of the Detail section to the right so that the report is about 6 3/8 inches wide.

3. Click the toolbar's Sorting and Grouping button to display the dialog, and select CategoryID as the field to use to sort the data with a standard ascending sort. Close the Sorting and Grouping dialog.

4. Click the toolbar's Field List button, if necessary, select CategoryID, and drag its field symbol to the Detail section.

5. Click the CategoryID label and relocate the label to the upper left of the Detail section directly over the CategoryID combo box. (CategoryID appears as a combo box, not a text box, in Report Design view because CategoryID is a lookup field.) Adjust the depth of the label and text boxes so that each equals 0.2 inches (four grid dots), and adjust the width to 1 inch. Edit the label's text to Category.

6. Click and drag the field list's Jan field to 1/8 inch (three dots) to the right of the CategoryID field. Move the label to the top of the section, adjacent to the right border of the field to its left. Move the text box under the label. Adjust the label and text box depth to four dots and the width to 16 dots (3/4 inch). Edit the label's text to delete the colon.

7. Repeat step 6 for the month fields of Feb through Jun, separating the fields by 1/8 inch. The report design now appears as shown in Figure 17.11.

   Figure 17.11. Start the report design by adding the CategoryID field and the Jan through Jun fields of the query to the first row of labels and text boxes.

   

8. Click each month label while holding down the Shift key so that you select all six labels (but only the labels).

9. Click the toolbar's Bold button to add the bold attribute to the labels. Then click the Center button to center the labels above the text boxes.

10. Select the CategoryID text box and the label, and click the Bold button.

11. Select each of the six month text boxes, and click the Right button to right-align the dollar amounts.

12. If the Toolbox isn't visible, click the Toolbox button on the toolbar. Click the Line tool and add a line at the top edge of the labels. Drag the line's right-end handle to the right edge of the Jun text box.

13. With the line you added selected, click the drop-down list of the Line/Border Width button on the toolbar and click the 1-point line thickness button.

14. Repeat steps 12 and 13 for another identical line but add the new line under the labels (and above the text boxes).

15. Drag the Detail section's margins to within two dots of the bottom and right edge of the controls. The report's design appears as shown in Figure 17.12.

    Figure 17.12. Format and align the labels, align the text boxes, and add two one-point lines to dress up the report.

    

16. Click Print Preview to verify the design (see Figure 17.13).

    Figure 17.13. Confirm the first phase of the report's design in Print Preview.

    

17. Press Ctrl+S and type rpt1997MonthlyCategoryOrders as the report's name.

To add the remaining months of the year and the Grand Total field to your report, follow these steps:

1. To accommodate another row of labels and text boxes, increase the depth of the Detail section by dragging the bottom margin down about 1 inch.

2. Press Ctrl+A or choose Edit, Select All to select all the controls in the Details section.

3. Press Ctrl+C to copy the labels, text boxes, and lines to the Clipboard.

4. Press Ctrl+V to paste a copy of the labels and text boxes to the Detail section.

5. Move this copy directly under the original labels and text boxes.

6. Click a blank area of the report to deselect the controls; then select and delete the new CategoryID text box. When you delete this text box, you also delete the associated label.

7. Edit both the labels and text boxes to display Jul through Dec. (Access automatically resizes the labels to fit the new text value and automatically sets the text boxes' Control Source property to match the field name you type into the text box.)

8. Open the field list, if necessary, and add the Total Orders field and label in place of the second CategoryID field and label you deleted in step 6. Change the label caption to Year Total, select the label and text box, and apply the Bold attribute. Adjust the width of the label and text box to 16 grid dots (3/4 inch).

9. Press Ctrl+A to select all the controls, and choose Format, Align, To Grid to correct any minor alignment discrepancies.

10. Drag the bottom margin up to within two dots of the bottom of the text boxes in the second row. Figure 17.14 shows the final report design.

    Figure 17.14. Copying the first row of controls to create a second row, and then editing the labels and text boxes is faster than adding and adjusting another set of six controls.

    

11. Click Print Preview to display the double-row report (see Figure 17.15).

    Figure 17.15. Print Preview displays the report design of Figure 17.14.

    

12. Close the rpt1997MonthlyCategoryOrders report and save the changes.

The technique of copying controls to the Clipboard, pasting copies to reports, and then editing the copies is often faster than creating duplicate controls that differ from one another only in the text of labels and the field names of bound text boxes.