Creating Reports

Access reports are much more robust than reports you can create for data in Excel worksheets. In fact, many data analysts like the Access report layouts and features so much that they create Access database files that do nothing more than link to Excel worksheet data and then create reports based on the data. The reports can be saved in the Access database or exported as HTML, XML, or other file formats.

Access reports do the following:

• Sort and group records for better visual data categorization.

• Display subtotals and grand totals.

• Provide report sections such as headers and footers.

• Provide additional information such as date and time.

• When based on filters or queries, display only the relevant subset of a larger group of records.

• Customize the windows that are used to display and navigate through reports.

To create and view a report, you should first create a table or query that contains the data that you want to display in the report. Then click Report on the Insert menu. Click Report Wizard, and then click OK. In the Tables/Queries list, select the table or query that contains the data you want to display in the report. After selecting each field you want in the report, specify how to display, sort, and organize the report’s data. You can then provide a report title, click Finish, and the report is displayed.

Putting It Together

In this exercise, you will create an Access report that displays the results from a linked Excel worksheet. You will also practice grouping and sorting the report’s details.

1. If the Northwind.mdb file is not already open, start Access and open the Northwind.mdb file in the Chap05 folder.

2. On the File menu, point to Get External Data and then click Link Tables.

3. In the Files Of Type list, select Microsoft Excel.

4. Select the Hotel.xls file in the Chap03 folder, and then click Link.

5. Click the Next button twice, click Finish, and then click OK.

6. On the Insert menu, click Report.

7. Click Report Wizard, and then click OK.

8. In the Tables/Queries list, select Table:Sheet1.

9. In the Available Fields list, double-click the Gold Customer Name, Nights Booked, and Total Room Service fields, and then click Next.

10. Double-click Gold Customer Name to group the records by this field. Click Next.

11. Select Total Room Service as the field to sort by, and then click Ascending.

    Clicking Ascending changes the button’s label to Descending and the sort order to a descending order. The records will be sorted so that the customer having the highest total room service will be at the beginning of the report.

12. Click Summary Options, select the Sum option for both Nights Booked and Total Room Service, select the Summary Only option, click OK, and then click Next.

13. Click the Stepped and Portrait options, and then click Next.

14. Click the Bold option, and then click Next.

15. In the What Title Do You Want For Your Report box, type Customer Room Service Summary, click the Modify The Report’s Design option, and then click Finish.

16. On the View menu, select Sorting And Grouping.

17. Click the row selector for the Gold Customer Name row, and drag the row below the Total Room Service row.

    Making this change organizes the report’s data so that details will be sorted in descending order by the sum of the Total Room Service field values. If the Gold Customer Name field was at the top of the list, the report details would be sorted in ascending order by the Gold Customer Name field values instead.

18. Close the Sorting And Grouping dialog box, and then, on the View menu, click Print Preview. Compare your results to Figure 5-14.

    
    Figure 5-14: Report of total room service charges by customer, sorted by highest totals first.