A report provides an object used to print a database's records. Although you can print tables or forms, reports are customized to be printer friendly. They can perform and display calculations. Once again, Microsoft Access provides wizards to quickly create reports.

Microsoft Access can help you quickly create a report using one of the pre-designed layouts. This is done using the Report Wizard. The Report Wizard is available from the Reports section of the Create category of the ribbon.

Data on fields of forms and reports fall in three main categories: fields that directly originate from a table or a query, fields created as a combination of existing fields, and fields independent of any other fields. The techniques used to create these fields are different but a field created using one technique can be changed into another category as necessary.

If you want to use a field that is already part of a table or a query, before or when creating a report, you must specify the list that holds the fields. There are various ways you can do this:

• If you have already started a report and it is displaying in Design View but you did not yet select the table that holds the list of fields, on the Properties window for the form, you can click the arrow of the Record Source field and select the table from the list
• If you want to use only a selected list of fields from a table, start the report in Design View without selecting an object. Then, on the Properties window, click the Record Source field and click its ellipsis button. This will allow you to use the Query Builder to create a query that is a list of the needed fields. After creating the query, close it. Its Field List would display only the previously selected fields

To add a field to a report, you can either click a control from the Ribbon and click the report, or drag a field from the Field List to the report.

One of the ways you can display a report is called the Design View. As seen in previous lessons, to display a report in Design View

• From the Navigation Pane, you can right-click it and click Design View
• If the report is already opened, in the Views section of the ribbon, you can click the arrow button under View and click Design View
• If the report is already opened, you can right-click its tab or its title bar and click Design View

As done for a form, in the Design View of a report, you can add, position, format, configure, and manipulate the necessary controls. The Design View is equipped with one or more sections. The primary sections are the Page Header, the Detail, and the Page Footer:

As mentioned when studying form and report design, when a report is in Design View, the Ribbon is equipped with a Controls section in its Design tab. You can use those controls to populate your report. You can also select objects from the Field List and add them to the report.

To have an idea of what a report would look like on a printed piece of paper, you can display it in what is referred to as Print Preview. To do this:

• If you create a report using the Report Wizard, it would automatically display in Print Preview
• In the Navigation Pane, you can right-click a report and click Print Preview
• If the report is already opened, you can right-click its tab or its title bar and click Print Preview

When a report appears in Print Preview, the Ribbon is made of only one tab. To appear realistic, a report in print preview appears as a piece of paper with margins. Its body is filled with the data that would be printed. Here is an example:

The right side and the bottom-right side display a scroll bar each. Like a form in Form View, the Print Preview of a report may be equipped with navigation buttons. The functionality of these navigation buttons is as we described for a table.

After using the Print Preview, to close it, in the Close Preview section of the ribbon, you can click the Close Print Preview button . This would display the report in the view it previously had.

The Report View shows a report with its controls and the items in its sections but it does not show the margins:

Unlike the Print Preview, the Report View does not distinguish where a section starts and where it ends.

The Layout View of a report appears as a drawing board. It shows its title bar and its system buttons. In its body, it displays three dotted lines that represent the top section and the margins:

Like a Design View, you can use the Layout View to add and manipulate controls on a report. Unlike the Design View, when a report is in Layout View, the ribbon does not have a Design tab. Instead, it is equipped with a Format tab that has a Controls section; but that section is not similar to the Design View's. To use the controls of a data source, you can drag them from the Field List (to open the Field List, you can click the Add Existing Fields button from the Controls section of the Format tab).

When a piece of paper prints, it is made of a top section, a body, and a bottom section. To support this, a report can be equipped with a Page Header that represents the top part, a Detail section that represents the body of the report, and a Page Footer section that represents the bottom part.

If you create a report using either the Blank Report or the Report Design options of the Reports section of the Ribbon, the report would be equipped with a Page Header and a Page Footer sections:

If you have a report that does not have these sections, you can add them. To add these sections, you can:

• Right-click the report and click Page Header/Footer
• On the ribbon, click Arrange. In the Show/Hide section, click Page Header/Footer

You can use these same techniques to remove the sections.

As mentioned previously, the Page Header represents the top section of the printed paper. Therefore, when designing a report, put in the Page Header the objects you want to display on each top part of the printed paper. For example, you can put the common title or the page number in that section. That section is also typically used to display the title of a brochure or book.

Because the Page Footer represents the bottom part of each printed page, you can put on it the object(s) that would display on each page. For example, you can use it to display the date the report is being printed.

Like a form, to show the sections of a report, it must be opened in Design View. Like a form, the most fundamental part of a report is the Detail section. The detail section holds the most controls of a report. In fact, a report can have only that section. If you create a report using one of the options from the Reports section of the Create tab of the ribbon, the report would come equipped with various sections. To have only the Detail section, you can right-click the report and click the option of those sections to remove them. You may end up with only the Detail section:

You can then equip it with the desired controls. Here is an example:

As mentioned already, the report is the primary object used to print the data of a database. You may have created a database that represents many objects sold in a store or you could have created a database for a list of people such as the students of a high school. In these cases, you may want to create a report that shows the list. To print a realistic book, magazine, or brochure, you would want to have a front cover and a back page.

To support the cover and the back page, the report can be equipped with two other sections: The Report Header and the Report Footer sections:

If you create a report using either the Report or the Report Wizard options of the Reports section of the Ribbon, the report would be equipped with a Report Header and a Report Footer sections. If you have a report that does not have these sections, you can add them. To add these sections, you can:

• Right-click the report and click Report Header/Footer
• On the ribbon, click Arrange. In the Show/Hide section, click Report Header/Footer

You can use these same options to remove the Report Header and the Report Footer sections.

As seen in the above screenshot, you can have a report that has a Report Header and a Report Footer sections without the Page Header and the Page Footer sections. If you have a Page Header and the Page Footer sections but do not want to show them on a printed paper, you can completely reduce their heights:

Otherwise, if you are creating a book or brochure that would represent a list of various items, you should equip it with all these five sections.

By default, after adding a section to a report, the section would show in Design View and its contents would appear in the other views. If you want, you can hide the section in either the Design View or the other view. This characteristic is controlled by the Display When enumerated property. To apply it, display the report in Design View and access the Properties window of the section on which you want to control this characteristic.

The Display When property has three options:

• Always: This is the default value of the property. It indicates that the section will display in Design View and its contents would appear in all views
• Print Only: The section will appear in Design View and Print Preview only, not in Report View or Layout View
   

  Report View

  Print Preview

• Screen Only: The section will appear in Design View, in Report View, and in Layout View, but not in Print Preview

Page setup consists of customizing some of the behind-the-scenes aspects of a piece of paper resulting from printing. These include the size of a report, the orientation, and others. To assist you with this, when the report is displayed in Print Preview, the Ribbon is equipped with a section labeled Page Layout:

The Page Layout section is equipped with a button labeled Page Setup. When clicked, this button would open the Page Setup dialog box that provide some of the options of the Page Layout section and more.

Like a form, a report has a size, which is the combination of its width and its height. When it comes to the height, each section has and controls its own vertical measure. As done for a form, to specify the height of a section:

• You can drag up or down the bottom border of the bar of the next section
   
  
• You can access the Properties window of that section and change the value of the Height property

As seen for a form, the height that a report displays in Design View is the total height of its sections.

When it come to the width of a report, all sections use the same measure. The width that a report shows in Design View is the common width of its sections. Therefore, to specify the width of a report:

• You can drag left or right the right border of any section
   
  
• You can access the Properties window of the form and change the value of the Width property

To change both the height and the width of the report: 

• You can drag left, up, right, or down the bottom-right corner of the lowest section
   
  
• You can access the Properties window of the report then change the values of both the Height and the Width fields

As seen previously, a report is meant to print on a piece of paper whose size is 8.5 by 11 inches. If you want, you can change that size. To do this, while a report is displaying in Design View, you can click the Page Setup tab of the Ribbon and click the Size button:

From the list of sizes, you can click the one you want. Alternatively, when a report is displaying in Design View, click the Page Setup tab and click the Page Setup button . Then, in the Page Setup dialog box, click Page. In the Paper section, you can click the arrow of the Size combo box:

You can then click the desired size and click OK.

Although you can change the size of a report to almost any measures of your choice, in reality, the printing of a report is subject to external influences related to the operating system. In this particular case, these have to do with the printing process followed by the device used to print. The layout of a paper specifies whether the printed result would be considered vertically or horizontally. This is referred to as the report layout or the paper orientation. There are two main options: portrait and landscape.

When a report is displayed in Design View, to check its orientation, on the Ribbon, you can click Page Setup. By default, a report is meant to print in portrait and you can see this if the Portrait button is clicked . To configure it for landscape, you have various options. While a report is showing in Design View, on the Page Setup section of the Ribbon, you can click the Landscape button. Also, when a report is displaying in Design View, click Page Setup and click the Page Setup button . Then, in the Page Setup dialog box, click Page and click the Landscape radio button:

And click OK.

If you enlarge the report too much but keep the orientation to portrait, you would receive a warning message box:

When a report prints, the printer keeps some sections in the left, the top, the right, and the bottom sides empty. These sections are referred to as the margins. The printer is configured to follow some default options for the margins. If you want, you can exercise some control on them. You have two options. To use some preset options, while a report is displaying in Design View, on the Ribbon, you can click the Page Setup button, then click the Margins button. Three preset options would appear.

If one of the three margin options in the Page Setup section of the Ribbon is not enough, you can click the Page Setup button. This would display the Page Setup dialog box:

In the Print Options tab, you can change the values of the desired section(s) as you see fit.

In Print Preview, we mentioned already that a report shows how the printed paper would appear. To give you a better overview, Microsoft Access may "shrink" the view. Although this may make some characters unreadable, the intent is to give a preview of the resulting piece of paper, not necessarily read the contents of the report. If you still want to read the content of the report, you can enlarge its view. On the other hand, if you are not particularly interested in reading the content of the report but preview it, you can shrink the view more or enlarge it more. This is referred to as zooming the view and you can zoom in or zoom out.

Zooming is available and only relevant when previewing a report. Therefore, in order to use it, first display the report in Print Preview. To assist you with zooming, the Ribbon state for the Print Preview is equipped with a Zoom section:

By default, the Print Preview shows only one page of the report and the One Page button of the Zoom section of the Ribbon is clicked. Also, usually in that view, the characters could be difficult to read. To zoom in or zoom out of the view, you can click inside the report itself or click the (top side of the) Zoom button. This would change the zooming ratio. If you want a more precise zoom ratio, you can click the arrow button below the Zoom button. This would present a list of zooming ratios and you can select the one you desire:

As mentioned already, by default, the Print Preview presents one piece of paper. If your report contains more than one page and you want to preview a few or all of them, you can display more than one page. To display two pages, you can click the Two Pages button. To display 4, 8, or 12 pages, you can click the More Pages button and select the desired option.

By default, each section of a report appears white so its background would print as transparently as possible on a white piece of paper. If you want, you can change the background color of any section. To change the color of a section:

• You can right-click the section or its bar, position the mouse on Fill/Back Color, and select a color from the window
• You can click a section or its bar. Then, in the Font section of the Design tab of the Ribbon, click the arrow of the Fill/Back Color button and select the desired color from the window
• You can double-click the section or its bar to access its Properties window. Then, in the Format or the All tab of the Properties window, click Back Color and select the desired color

If you specify the background color other than white for a section, you should set the same background color for the controls in that section.

If you create repeating colors on a report, you can make the other occurrence of a section show a different color. This means that you can make every odd occurrence show a certain background color and every even occurrence to show a certain background color. This is referred to as alternating the colors.

To apply the color alternating feature, first select a section and set its background color using the Fill/Back Color button. Then:

• While the same section is selected, in the Font section of the Design tab of the Ribbon, click the arrow of the Alternate Fill/Back Color button and select a color
• While the same section is selected, in the Format or the All tab of the Properties window, click Alternate Color, then click the arrow of its combo box and select the desired color
• Right-click the section or its bar, position the mouse on Alternate Fill/Back Color and select the desired alternate color

Here is an example of the type of result you could get:

1. Right-click the Detail bar, position the mouse on Alternate Fill/Back Color, and select Brown 2 (3rd row - 10th column)
2. Right-click the title bar of the report and click Print Preview
    
   
3. Save and close the report

Consider the following design of a report:

If you create a report and design it with the default settings, the first record displays. If its values do not cover the whole page, the values of the next record start displaying on the same page but, if the next record cannot completely fit on that first page, it does not print on that page. Instead, it prints on the next page. This means that more than one record can print on the same page if and only if all those records can fit each completely on that page. In some cases, such as if you have a report that displays a type of magazine, brochure, or catalog, you may want the first record to print on the first page, when the first record finishes, print the next record on the same page. If both records have printed on the same page and there is still enough room, the next record could start. Once the first page is full, the values left from the previous record continue on the next page. As you may suspect, these two scenarios depend on the types of values that the report is asked to print and of course this highly depends on your intention.

The ability to keep each record on its own page is controlled by the Keep Together property of a section. To access it, display the report in Design View and double-click either the section or its bar. Keep Together is a Boolean property with a Yes and No options:

• If you set Keep Together to Yes, which is the default value, the first record displays or prints its values on the first page:
  • If there is still room on that first page, Microsoft Access calculates how much room the next record needs. If the next record can completely fit on the remaining area of the first page, then that next record is put on that first page:
     
  • If the next record is too long to fit on that first page, it would not print on it but would be sent to the next page and the remaining area of the first page would be left empty:
    

    This evaluation is made for each subsequent record.

  • If the first record itself is too long to fit on the first page, it would print on the first page and would continue printing on the next page. Then, if the next record can fit on that second page (which is usually unlikely), it would be put on that page
• If you set Keep Together to No, the first record displays on the first page, followed by the next record. If the record gets to the end of the page, it continues on the next page. This means that the records would simply continuously print one after the other:

Consider the following table that represents a list of students:

When creating this type of table, you may have values that can be considered in categories, such as sold and unsold items, discounted and not discounted items, or males and females. When preparing a report, you can show its records by each category. For example, on a table of students, you can show the list of girls only first. Then, after the group of girls, you would show the list of boys. This is referred to as grouping the records.

1. From the resources that accompany our lessons, open the Altair Realtors2.accdb database
2. To create a new report, on the Ribbon, click Create and, in the Report section, click Report Design
3. Right-click the title bar of the report and click Save
4. Set the name to Properties By Type and press Enter
5. If the Properties window is not available, on the Ribbon, click Design and, in the Tools section, click Property Sheet.
   On the Properties window, click the Data tab, click Record Source, type p and press Enter

To create a group of records on a report, you have various options. You can let the Report Wizard assist you or you can work manually. If you want to create the group yourself, first open the report in Design View and specify, or make sure, its Record Source has a table. Then:

• On the Ribbon, you can click the Group & Sort button
• Right-click somewhere on the report and click Grouping and Sorting

This would add a new section titled Group, Sort, and Total under the report:

One of the buttons in the new section is titled Add a Group. To create a new group, you can click that button. This would change the contents of the window and display a new button with a popup window that shows the fields of the table specified as the Record Source:

From the popup window, you can click the name of the field you want to use as the group leader. Here is an example:

This would add a new bar and section to the report above the Detail bar. The bar of the new section would display the name of the field followed by Header. Here is an example:

Also, under the report, the contents of the Group, Sort, and Total window would be changed into a new bar that displays the name of the field that was selected.

1. In the Design tab of the Ribbon and in the Grouping & Totals section, click the Group & Sort button
2. Under the report, click the Add a Group button
3. In the window that appears, click Property Type

After creating a group, you can add, or continue to add, the fields to the report. One of the most important, but not required, aspects of design is that you should add the field that holds the same name to the new section:

That way, when the report prints, the name of the group would show before the related records: remember that the report bars do not show on the printed paper. After adding the field in its section, you can add, or you can continue adding, the other fields. Those other fields should be added to the Detail section or the section under the group header. If you include their labels, these labels would show for each field:

This can appear unprofessional or redundant to some eyes. A better alternative is to put the labels of those child fields in the section of the group header, and put the controls in the section under the group leader. Here is an example:

Notice that, by default, the Detail section repeats to show each record related to the group leader. Therefore, another aspect of design is that you should provide room only as much as necessary for each related record. Here is an example:

Then, when the report displays or when it prints, all related records would show appropriately:

Still, if each record has some detail, such as a comment or a calculation you want to add, you can/should include it in the Detail section, probably under the other fields.

1. Design the report as follows:
    
   
2. Right-click the title bar of the report and click Print Preview
3. Save, print, and close the report

Consider the following table of a list of students:

In a certain table, you may have records that can be categorized. Then, inside of each category, there may be a sub-category. When creating a report for such a table, you can create one group, then create another group inside of that group. You start the report as we described so far: display it in Design View and specify its Record Source. You also start the grouping as we saw above. In the Group, Sort, and Total window, click the Add a Group button and select the primary group. Here is an example:

As mentioned already, a bar would be added to the Group, Sort, and Total section. Under that bar, there would be a new Add a Group button and a dotted curved line joining them:

To create a sub-group, you can click the Add a Group button to display the list of fields and click a field from that list:

A new section would be added under the main one and it would have the same name of the field that was added, followed by Header:

Notice that under the report, a bar for the new group is added and it has its own Add a Group button, which indicates that, if necessary, you can create another sub-section. You can continue the same approach to create as many sub-sections as you judge necessary.

When designing the report, in the main section, you should add only its own field. In the group under it, you should add the field of that group and the labels of the related records if that section does not have its own sub-group. Then, in the Detail section, add the last fields of the grouping:

If you have a group you do not like on a report, you can get rid of it. To remove one group, under the report, click the Delete button that corresponds to it. You would receive a warning so you can make your final decision. If you delete a sub-group, only its section would be removed from the report. If you delete a group that has one or more sub-groups, its section and its sub-sections would be removed from the report.

When creating a report, you add the fields of the records as you see fit in the desired sections and groups. As seen with the Keep Together Boolean property, you can exercise some control on how many records print on one page based on the number of fields of each record. We saw that if many records could fit on one page, then they would be grouped on that one page. In some cases, you may want each record on its own page, even if the record has only a few values, even if many records can fit on one page. Displaying each record on its own page is referred to as forcing a new page.

The ability to force each record on its own page is controlled by the Force New Page enumerated property. To use it, access the Properties window for a section or group. From the Format or the All tab, click Force New Page, and click the arrow of its combo box to display the list of options:

• None: This is the default value and simply specifies that the record will print on the page
• Before Section: The section or group will print in the top section of a new page and the rest of the page could be left empty. The next section or group would print on the next page:
     
  If you create a report with various groups, probably the best way to use the Before Section option is to apply it on the first or main group. This would display each record on its own page
• After Section: This section or group will print on this page. Then the section or group after this one will print in the top section of the next page
• Before & After: This section or group will print in the top section of this page. Then the section or group after this one will print in the top section of the next page