Create A Query

We encourage you to make a copy of your Northwind sample database before proceeding. Navigate to the Northwind.mdb file in the Samples subfolder of your Microsoft Office folder. Copy Northwind.mdb to the Clipboard and paste it in another folder. Rename the file CopyOfNorthwind.mdb, which you can now open and use without changing the original file.

Before you create a report, you usually need to create a query that contains the records you want to display. In the Database window, select Queries and click New. With Design View selected, click OK. In the Show Table dialog box, select Categories, press CTRL on your keyboard, click Products, and then click Add. Close the Show Table dialog box. Maximize the Query Design window.

Notice that the Categories and Products tables have a one-to-many relationship through the CategoryID field. Thus, you can easily add data about products (for example, product name, unit price, etc.) from the Products table and group the products by the category name (for example, beverages, condiments, etc.) from the Categories table.

In the Categories table, double-click CategoryName to add it to the design grid. In the Products field list, double-click ProductName, UnitPrice, and UnitsInStock. To save the changes you just made, choose File and click Save. Type Products SC and click OK. Click the View button at the far left of the toolbar to see records for the four fields. Close the Products SC query.

Create A Report

In the Database window, choose the Queries tab and select Products SC. Open Insert on the main toolbar and choose Report. With Design View selected, click OK. If necessary, maximize the Report window. Open View from the toolbar and select Sorting And Grouping. Click in the first row of the Field/Expression column. Open the drop-down list and choose CategoryName. In the Group Properties section of the Sorting And Grouping dialog box, click in the GroupHeader field and select Yes from the drop-down list. This creates a group header section for the category name. Leave the other settings as-is and close the dialog box.

The field list should be visible; if not, choose View from the toolbar and select Field List. Click CategoryName in the list, drag it to the CategoryName Header, and drop it about 1 inch from the left side of the report and a couple of rows from the top (it doesn’t have to be exact). Click the Category Name label (the box on the left) to select it and press DELETE. Select the CategoryName text box. Click the Bold button on the toolbar and change the Font Size to 14. Right-click the CategoryName Header and choose Properties. On the Format tab of the group header property sheet, set the Height to 0.5 inches.

Select the CategoryName text box. The open property sheet should now read Text Box: CategoryName. On the Format tab, edit the Height to 0.3 inches and the Width to 1.5 inches. Click in any other property. (Figure 1 shows how the CategoryName control should appear.) In the field list, select ProductName, press CTRL on your keyboard, and click UnitPrice and UnitsInStock to highlight all three. Drag the three fields to the Detail section and drop them about 2 inches from the left side of the report and a couple of rows of dots from the top of the report. With the fields still selected, click the Left Align button on the toolbar and then click the Detail bar. On the Format tab of the Detail section property sheet, set the Height property to 1 inch. Choose File, Save, type Products SC, and click OK. Click the View button and use the Next Page button on the navigation bar to look at a few pages.

Refine The Report

Note that the group name (Beverages, Condiments, etc.) appears only once, before the group’s records. You can put the category name on succeeding pages so readers don’t have to flip back to discover the product category. Click View to return to Design view. Click CategoryName Header. On the Format tab of the property sheet, click in the Repeat Section property. Double-click No to change it to Yes.

Suppose you have assigned groups to individual employees and want to give them just the pages for their specific group. To do that, you can start each group on a new page. On the same tab of the same property sheet, click in the Force New Page property. Open the drop-down list and choose Before Section and then click the Save button on the toolbar.

Click View to return to Print Preview. Scroll through a few pages and notice that the group name is at the top of all pages (see Figure 2), and a new page is started whenever there is a new group. Close the report, close the Northwind database, and exit Access.