Practical Learning: Creating a Report

1. Open the Bethesda Car Rental1 database and, on the Database window, click Reports
2. To prepare data for a new report, on the main menu, click Insert -> Query
3. In the New Query dialog box, click Design View and click OK
4. In the Tables property page of the Show Table dialog box, double-click CarCategories and Cars then click Close
5. From the CarCategories list, double-click Category
6. From the Cars list, double-click TagNumber, Make, Model, and CarYear
7. Save the query as CarsByCategory
    
   
8. View the query's SQL statement then preview and close the query
9. To start a new report, in the Reports section of the Database window,
   if you are using MS Access 97, click New; in the New Report dialog box, click Report Wizard and click OK
   if you are using MS Access >= 2000, double-click Create Report By Using Wizard
10. In the first page of the wizard, in the Tables/Queries combo box, select Query: CarsByCategory
11. Click the select all button and click Next
     
    
12. In the second page, click By Cars and click Next
13. In the third page, make sure there is no blue line in the right list. If you see any, click the remove one button
     
    
     
    Click Next
14. Accept the defaults of the fourth page and click Next
     
    
15. In the fifth page, click the Tabular radio button in the Layout section and click Next
     
    
16. In the sixth page, click the Corporate Style and click Next
17. Set the Title of the report to Cars by Category and click Finish
18. Print the report
19. After viewing the report, close it

Report's Static Characteristics

Without being a physical piece of paper, a report is primarily a window with the regular characteristics of such an object:

• Like a form, a report starts on top with the title bar that is divided in three sections
   
  
• The system icon is equipped with its system menu that can be used to minimize, maximize, restore, move, resize, or close the report
   
  
• The main area of the title bar can displays a menu if you right-click it
   
  
• The right side of the report is equipped with the Windows system buttons

Unlike the form, there is no way you can change the title bar, the system menu, or the system buttons of a report. Even if you can change them (programmatically, probably using the Win32 API), you should refrain from it because the primary purpose of a report is to print information and the users know how to use it.

The Report's Width

A report's width is the distance from its left to its right borders. While a form's width is used to decide the amount of space made available to display the form's controls, the width of a report plays a slightly different role. This is because there are two main types of displays the user uses to print a report: Portrait or Landscape.

If you create a report using the Report Wizard, in the 4th page of the wizard, the Layout section can be used to suggest the orientation intended:

If you select the Columnar option, the controls would be positioned to the left side of the report. This is usually suited for a Portrait orientation. If you select Tabular, the controls would be aligned to the top sides of the Page Header and the Detail sections. If there are many controls, the orientation used should be Landscape.

To specify the width of a report, while in Design View, you can position the mouse to its right border and drag in the direction of your choice.

When creating a report, you can prepare it for Portrait or Landscape display based on the width you set it to, although the users are still free to use any display they want. Normally, if you want the report to be printed in Portrait or Landscape, you should give it a width of less than 6^1/2. To prepare a report for Landscape printing, you can set its width to less than 11. In this case, you should also let the user know that the report is intended for Landscape. Otherwise, if the user prints in Portrait, the right side of the report would appear on a separate page.

Report Sections

Regular Reports' Sections

A form mostly uses a Form Header and a Form Footer sections. A form can also be equipped with invisible page sections. On the other hand, a report can be equipped with as many sections as you judge necessary. This means that the concept and design of a report provides additional options than the form.

If you create a report directly in Design View, it would be equipped with a Page Header, a Detail, and a Page Footer sections. If you want to add a Report Header and a Report Footer sections, you can right-click anywhere in the report in Design View and click Report Header/Footer.

If you create a report using the Report Wizard, it would be directly equipped with a Report Header and a Report Footer sections

Practical Learning: Creating Regular Sections

1. To start a new report, on the main menu, click Insert -> Report
2. In the New Report dialog box, click Report Wizard
3. In the combo box, select CarCategories and click OK
4. In the first page of the wizard, make sure Table: CarCategories is selected in the Tables/Queries combo box.
   In the page, click the select all button
5. In the Selected Fields list, double-click CarCategoryID and Notes to remove them
    
   
6. Click Next
7. In the second page, make sure there is no blue line in the right list
    
   
   
   Click Next
8. In the third page, accept all defaults and click Next
9. In the fourth page, in the Layout section, make sure the Tabular radio button is selected and click Next
10. In the fifth page, make sure the Corporate Style is selected and click Next
11. Set the report's Title to Rental Rates and click Finish
12. Preview the report before closing it

Custom Reports' Sections

The traditional sections of a report, Report Header, Page Header, Detail, Page Footer, and Report Footer, are easy to add during design. Microsoft Access allows you to add other sections of your choice if you judge necessary. Such a customized section can be used to create a category of the data being displayed. For example, if you are working on a database for a sales intensive business, you can create a section that would display the name of a salesperson and then sales that that person performed.

A custom section of a report is called a group. Therefore, to create a new section, you in fact create a Group Level. If you create a report using the Report Wizard, to get one or more additional sections, in the second page of the wizard, you can select a field in the left list and add it to the right list.

If you have already created a report, to add a new section, you can right-click it and click Sorting And Grouping. You can also click the Sorting And Grouping button on the Report Design toolbar. You can also click View -> Sorting And Grouping from the main menu. Any of these actions would open the Sorting and Grouping window.

To create a new section, under the Field/Expression column, you can click a field to display its combo box. Then select a grouping of your choice. Selecting a field only allows you to decide the group level you want to create. To actually display the group, in the lower section of the Sorting and Grouping window, you can set the Group Header property to Yes. Additionally, if you want the section to have its equivalent closing under the Detail section, you can set the Group Footer property to Yes.

Practical Learning: Creating Custom Sections

1. To start a new report, on the main menu, click Insert -> Report
2. In the New Report dialog box, click Report Wizard
3. In the combo box, select Cars and click OK
4. In the first page of the wizard, make sure Table: Cars is selected in the Tables/Queries combo box. In the Available Fields list, double-click TagNumber, Make, Model, CarYear, and CarCategoryID
5. Click Next
6. In the second page, if a category is selected as represented in blue in the right list, click the remove field button to remove it.
   In the left list, double-click CarYear
    
   
7. Click Next
8. In the third page, don't change anything and click Next
9. In the fourth page, click the Align Left 1 radio button
    
   
10. Click Next
11. In the fifth page, make sure the Corporate Style is selected and click Next
12. Change the Title of the report to Cars by Year and click Finish
     
    
13. Print the report
14. After viewing the report, close it
15. To modify an existing report, in the Reports section of the Database window, double-click the Cars by Category report
16. After viewing it, switch it to Design View
17. On the Report Design toolbar, click the Sorting and Grouping button
18. As the first combo box under Field/Expression has focus, select Category and accept to have its Sort Order in Ascending
19. In the Group Properties section of the window, set the Group Header to Yes
     
    
20. Close the Sort and Grouping window
21. In the left side of the Detail section, drag the Category text box and position it under the Category Header bar
     
    
22. Preview the report and print it
23. After viewing the report, save and close it

Properties of Reports' Sections

Microsoft Reports are among its most valuable features. Although a report is primarily a means of printing data, it can be created as a magazine, a newspaper, a brochure, or even a book. This is done by configuring its sections appropriately.

Except for the Detail, each section of a report can be created as a combination of a beginning and an end. To make their behavior effective, the sections of a report are arranged so that some of them can be included inside of others. Based on this, each section can be configured for a specific task.

If you are creating a regular report that displays one record per page, you may not need a Report Header and a Report Footer sections. If you are creating a brochure or a document that appears as a catalogue or a book, you may need a Report Header and a Report Footer sections. The sections would appear as follows:

• The Report Header section is the first page of the report. It can be created and configured like the cover of a magazine or brochure. If you are creating a regular report, you can use the Report Header to display a label that would appear in the beginning of the printed document even if it doesn't appear on its own page. For example, a Report Header section can be used to create a title for the report
• The Page Header appears on top of each piece of paper printed with the following exception: if the report contains a Report Header section and the Report Header section is configured as a whole page, the Page Header would not appear on the first page printed
• The Detail section is used to display regular data of the report. It is presented on each page except on the first if the report includes a Report Header and on the last if the report includes a Report Footer sections
• The Page Footer section is like the section that displays page numbers or footnotes of a book. It appears on each page
• The Report Footer section is the last page of the document. It is like the back page of a book or a magazine

If you are creating a magazine, a brochure, a multi-page newsletter, or a book, each section has a property called Force New Page except for the Page Header and the Page Footer sections. We mentioned that these two sections display on each page. Therefore, the Force New Page property allows you to ask Microsoft Access to start a new page after that particular section. For example, if you are creating a brochure or a catalogue, you can place the controls that hold information about the products being sold in the Detail section. In this case, you may not need to force a new page because the report would automatically continue the list of products on the subsequent page. On the other hand, if you are creating a payroll for employees, you mostly would like each check to display on its own page. In the same way, if you are creating grade reports for students, you would need to display information about each student on its own page. In this case, you should use the Force New Page property accordingly.

The Force New Page property has the following values:

• None: The report would be kept "as is", no concern for the new page. This is the detault and used on most single-page reports
• Before Section: There would be a new page just before this section. This means that this section would not be "attached" to the previous section. Also, this section can be "attached" to the next section, but not to the previous
• After Section: This section would be "closed" first. In other words, the next section would start on its own page. Also, this section be be attached to the previous section but not to the next
• Before & After: This section would be "isolated". In other words, it would be attached neither to the previous nor to the next sections
• Be

Practical Learning: Configuring Sections

1. To start a new report, on the main menu, click Insert -> Report
2. In the New Report dialog box, click Report Wizard and click OK
3. In the first page of the wizard, in the Tables/Queries combo box, select Tables: Cars
4. Click the select all button
5. In the Selected Fields list, double-click CarID to remove it from the list (you can keep it but we will not really need it)
    
   
6. Click Next
7. In the second page, click the remove one button to make sure the CarCategoryID group is not created
    
   
8. Click Next
9. In the third page, accept the defaults and click Next
10. In the fourth page, in the Layout section, click the Columnar radio button and click Next
11. In the fifth page, make sure the Corporate Style is selected and click Next
12. Set the report Title to Cars Catalogue and click Finish
13. After viewing the report, switch it to Design View
14. Expand the Report Header and the Report Footer sections for more than 2 divisions each to make them usable
15. To add a new group level, right-click the title bar of the report and click Sorting and Grouping
16. In the first combo box under Field/Expression, select CarCategoryID
17. In the Group Properties section of the window, set the Group Header to Yes then close the Sort and Grouping window
18. Since we will not actually put anything in the CarCategoryID Header section, reduce its height completely
19. To create a cover page for the catalogue, double-click the Report Header bar. In the Properties window, click the Format tab and set the Force New Page property to After Section
20. To create a back page for the catalogue, double-click the Report Footer bar. In the Properties window, click the Format tab and set the Force New Page property to Before Section
21. To make sure that each category starts on its own page, on the report, double-click the CarCategoryID Header bar. In the Properties window, click the Format tab and set the Force New Page property to Before and After
22. For the design of the report, drag the CarCategoryID combo box and position it in just under the Page Header bar so it becomes in the Page Header section (that way, it would display on top of every page) (There is a lot to do for the design of this catalogue. At this time, move only the controls around. You can adjust the properties such as font, colors, etc, in the next lessons)
23. Save and close the report

Lesson Summary

MOUS Topics