Designing Reports

3 4

The look and feel of the reports you design will vary depending on the type of information you want to present. If you’re creating a simple report that lists book sales in the southwestern region of the United States, you can use the Report Wizard to create a basic two-column report that lists only regions and sales in a matter of minutes. If you’re creating a more complex report that prints a student roster for your graduate program, complete with pertinent transcript subreports within each report, your planning might take several hours as well as the input of a number of people to ensure that the report meets the needs of the users who will read it.

Whether you want to print a report that gives a bare-bones listing of data in a table, a complex grouped report that summarizes data, or a report that displays linked data in subreports, plan to spend some time thinking through what data your report needs to display and how the data should be presented. Planning and foresight will save you editing and revision time later on. As you think about what you want to accomplish with the report you’re creating, consider these questions:

  • Who will be using this report? What information will users expect to see, and how do they want the information to be laid out?
  • What report format is best suited to your needs? For example, will you use multiple columns to present your data? How many columns do you need?
  • Does the report need group and/or report subtotals?
  • Should the report include all the data in the record source or just summarized data?
  • Should you use headings and graphic elements (such as lines and boxes) to help readers understand the report?
  • If you’re creating the report for a client, do you need to replicate current paper reports your client is used to seeing?

Access has a Report Wizard to aid you in creating reports. The various report types created by the Wizard can save time, and one of the Wizard’s selections may be exactly the type of report you need. Sometimes, however, you need a report type the Wizard doesn’t create. When that’s the case, you can create a report starting with a blank report in Design view, adding controls to the report grid manually, and setting report properties as desired. In the following sections, I’ll review several of the questions you should consider when designing reports.

InsideOut

Although you can save a form as a report, this "shortcut" usually ends up taking longer than creating a report from scratch because so much work has to be done to remove elements that work only on forms and to add report-specific components used to group and summarize data. I don’t recommend using this method to create a report.

Selecting Report Record Sources

Reports can use the same types of record sources that forms use: tables, queries, SQL statements, and recordsets. You select (or change) a record source for a report the same way you would for a form. For example, if you were designing a simple name and address list report, a table of contact data could be the report’s record source. A report that needs to list only a subset of data in a table could be based on a query that selects the output you need; for example, selecting records for the Southeast sales region and the year 2000.

A report that displays only summarized data could be based on a Totals query that uses the Sum or Count function to present the total dollar amount of sales or the count of orders. Just as for form record sources, SQL statements and recordsets can be used instead of queries as report record sources.

For more details on creating queries, see Chapter 9, "Using Queries to Select Data"; for information on using various types of record sources, see Chapter 5, "Creating Forms for Entering, Editing, and Viewing Data."

Using Grouped Reports

Reports have several features that don’t correspond to any form feature: One of them is grouping. A grouped report divides data into bands that correspond to the values in fields that you select for each group level. An example is grouping a name and address report by State and then by LastName. You can have up to ten group levels in a report, but reports rarely need more than three or four levels, and often one group level is all you’ll need. The Report Wizard has a Grouped Report selection that you can use to create a grouped report, but before you start the wizard you should create a query to use as the grouped report’s record source. This query should contain all the tables you need to obtain the data you want to include in the report, with just the fields needed for the report selected as query columns.

The fields used to create report groups are also fields used for sorting. Even if you don’t want to separate your data into distinct groups—and you don’t need group headers or footers—you can still use the Sorting and Grouping dialog box to sort the report dataas you want. This is especially useful if the report is based directly on a table. You can base several reports on the same table, sorting each by different fields, to produce very different reports from the same data.

For example, to create a report in the Crafts database that lists books and videos, grouped by author, you need a query that contains tblBooksAndVideos, tblBookAuthors, and tblAuthors, as shown in Figure 7-3. These three tables define the many-to-many relationship between books and authors.

figure 7-3. you can use this query with three linked tables as a grouped report’s record source.

Figure 7-3. You can use this query with three linked tables as a grouped report’s record source.

You can also create three-table queries for the Books–Sources and Books–Specialties many-to-many relationships. For an all-in-one report, you can create a query containing all the tables needed for books and videos, authors, sources, and specialties.

For more information about creating queries to select data, see Chapter 9, "Using Queries to Select Data."

Creating Reports with Subreports

Grouped reports require a record source (usually a query) that contains all the information needed to set up the groups. However, you have another way to create a grouped report: by using subreports to display linked data. Just as you can with linked forms, you can select the "one" side of a one-to-many relationship as a report’s record source and select the "many" side of the relationship as the record source of another report, and then you can embed the report bound to the "many" table as a subreport on the main report. Because the record source tables are linked on the key field, the subreport will always display the correct records linked to the main report record.

tip


You can embed a form in a report as a subreport. As a matter of fact, since Datasheet view is not available for reports, this method is the only way to create a datasheet subreport.

No wizard exists for creating reports with embedded subreports. You have to create such a report yourself in Design view, either by dragging the report you want as the subreport to the main report or by placing a Subform/Subreport control on the report and selecting the report intended to be the subreport as its source object.

For a discussion of various types of reports with embedded subforms and subreports, see "Adding Embedded Subreports and Subforms to Reports."

Using Wizards and AutoReport to Create Reports

The Report Wizard has several selections for creating reports of different types. To start the Report Wizard, double-click Create Report By Using Wizard on the Reports tab of the Database window. This wizard creates grouped reports in a variety of formats.

See "Grouped Reports," for more details on working with the Report Wizard.

There are several other ways to create a new report: If you click New at the top of the Database window, the New Report dialog box appears, offering several choices, as shown in Figure 7-4.

figure 7-4. the new report dialog box has selections for creating various types of reports.

Figure 7-4. The New Report dialog box has selections for creating various types of reports.

In addition, the New Object selector on the Database toolbar offers an AutoReport selection, which creates a not particularly attractive plain columnar report. Table 7-1 lists the available choices for report layouts and indicates where the selection is available. The sections that follow describe how to create reports using these layouts.

Table 7-1. Available report layouts

Layout selection Available from Description

Chart

New Report, Chart Wizard

Data from selected fields is charted in a variety of chart formats, using MS Graph.

Columnar

New Report, AutoReport: Columnar

Fields are laid out top to bottom, with visible borders around each text box control.

Crosstab

New Report, Design View

Generates a report based on a previously prepared crosstab query.

Datasheet

New Report, Design View

Lets you manually create a report with a datasheet subform.

Embedded Subreport

New Report, Design View

One or more embedded subreports are manually placed on the report; the subreports can be linked or unlinked.

Free-Form Report

New Report, Design View

No controls are automatically placed on the report; you can place controls where you want them.

Grouped

Report Wizard, choice of six layouts

Groups data by selected fields, with a choice of indented or left-aligned formats.

Label

New Report, Label Wizard

Data is formatted to be printed on one of a number of commercial labels or according to a custom label format.

Multicolumn

New Report, Design View

Report is manually set up as a multicolumn report in the Page Setup dialog box.

PivotChart

New Report, Design View

Creates a report with an embedded PivotChart subform, formatted to remove interactive elements.

PivotTable

New Report, Design View

Creates a report with an embedded PivotTable subform.

Tabular

New Report, AutoReport: Tabular

Fields are laid out left to right, with column headings.

Creating a Columnar Report Quickly

To create a simple columnar report in the minimum amount of time, select a table or query in the Database window, and then select AutoReport from the New Object selector on the Database toolbar, as shown in Figure 7-5.

figure 7-5. the new object selector lets you create an autoreport from the database toolbar.

Figure 7-5. The New Object selector lets you create an AutoReport from the Database toolbar.

The report, shown in Figure 7-6, is created automatically. It displays all the fields in the selected table or query, arranged in a single column from top to bottom. You can also create a columnar report from the New Report dialog box, as described in the "Columnar Reports" section. You’ll get exactly the same report whether you use the New Object: AutoReport button or the New Report dialog box selection.

tip


Acolumnar report is a very plain report indeed. Turning it into an attractive report requires considerable work. If you need a quick printout, say for proofreading data, a columnar report is handy; for most purposes, though, you’ll probably be better off choosing one of the Report Wizard selections, where you can choose the fields to display on the report and group the data by one or more fields.

figure 7-6. selecting autoreport creates this report.

Figure 7-6. Selecting AutoReport creates this report.

Grouped Reports

When you use the Report Wizard to create a grouped report, you have six choices for the report layout. The differences in layout are more a matter of taste than functionality, and all the other wizard selections are the same, so you can identify the layout you prefer by looking at the layout illustration on the wizard screen. Whichever layout you select, you’ll probably need to modify it somewhat to create an attractive report.

To create a grouped report, first prepare a query (such as qryBooksVideosAll) containing all the data needed for the report groups, as described in "Using Grouped Reports." Then follow these steps:

  1. Click the Reports tab of the Database window, and click New at the top of the Database window.
  2. In the New Report dialog box, select qryBooksVideosAll in the record source drop-down list. Select Report Wizard from the list of report types, and then click OK.
  3. Select the fields to place on the report, as shown in Figure 7-7. Click Next.

    figure 7-7.the report wizard offers a selection of fields for your report.

    Figure 7-7. The Report Wizard offers a selection of fields for your report.

  4. This page of the wizard appears only when the report’s record source has two or more tables. Select the table that you want to use as the primary table. In Figure 7-8, tblAuthors is selected as the primary table, so the report’s top grouping will be by author name. Click Next.

    figure 7-8.if the report’s record source has more than one table, use this page of the wizard to select the primary table for the report.

    Figure 7-8. If the report’s record source has more than one table, use this page of the wizard to select the primary table for the report.

  5. Select further grouping levels, if desired. In Figure 7-9, PublisherCode has been added as a group field. The resulting report will be grouped by publisher. Click Next.

    figure 7-9.select fields for group levels.

    Figure 7-9. Select fields for group levels.

  6. Select up to four sort levels. Selecting the sort field Title, as shown in Figure 7-10, will sort the Publisher group by book title. Click Next.

    figure 7-10.select sort levels.

    Figure 7-10. Select sort levels.

  7. Choose a layout from the Layout option group, as shown in Figure 7-11. You can also format the report for portrait or landscape orientation. Click Next.

    figure 7-11.in this example, the stepped layout and landscape orientation have been chosen for the report.

    Figure 7-11. In this example, the Stepped layout and landscape orientation have been chosen for the report.

  8. Select a style for the report, as shown in Figure 7-12. Click Next.

    figure 7-12.select a report style.

    Figure 7-12. Select a report style.

    note


    You can create a style of your own and save it as a named style. The technique is somewhat different from creating custom AutoFormat styles for forms. See "Using AutoFormat," for more details.

  9. Give the report a title, as shown in Figure 7-13. The title is used as the report name, as its caption in print preview, and as a field in the report header. Click Finish.

    figure 7-13.give the report a title on the last page of the wizard.

    Figure 7-13. Give the report a title on the last page of the wizard.

Figure 7-14 shows the finished report in print preview.

This report is a good start but needs some fine-tuning. None of the standard layouts is ideal, at least to my taste. The Stepped layout takes up an entire column for each group, whereas the Align Left layouts tend to use too many lines and boxes. For a clean, space-saving layout, try selecting the Stepped layout and then moving the group field controls and the first detail control to the left side of the report.

figure 7-14. the finished report looks like this in print preview.

Figure 7-14. The finished report looks like this in print preview.

Troubleshooting - My report breaks in the middle of a group of data

When a grouped report is printed, data that ought to be on a single page is sometimes broken up by a page break so that part of it prints on one page and the rest on the next page.

To prevent a group from being broken over two pages, open the Sorting And Grouping dialog box (in Design view), and specify Whole Group for the KeepTogether property for the group you want to keep together. For example, on rptBooksAndVideos, I wanted all the information on one book to be kept together, so I set KeepTogether to Whole Group for the Title group. Now if the information for a specific book title won’t fit at the bottom of a page, it will be moved to the beginning of the next page, instead of breaking in the middle of the group.

Troubleshooting - I want to suppress repetitive data in grouped reports

If the same data is repeating over and over in a grouped report, how can you make it print only once per group? For example, in a grouped report with a staggered layout, you generally don’t want to have the same data (such as a country name) repeating for every item in the Country group. Fortunately, text boxes on Access reports have a special property that can be used to suppress the printing of repetitive information.

For any text box that might contain repetitive data, set the HideDuplicates property to Yes. This allows the first instance of a particular value in the bound field to be printed but suppresses the printing of the same value on following lines in the same section.

Figure 7-15. A stepped report uses the <i>HideDuplicates</i> property to show only the first instance of each value in a group.

Figure 7-15 shows a simple stepped report based on data in the Crafts database, with the HideDuplicates property set to Yes for the txtPublicationYear and Publisher text boxes. (See Figure 7-59 for a "before" version of this report, in which the HideDuplicates property was left at the default setting No and publisher data repeats.)

Figure 7-15. A stepped report uses the <i>HideDuplicates</i> property to show only the first instance of each value in a group.

Figure 7-15. A stepped report uses the HideDuplicates property to show only the first instance of each value in a group.

Fine-Tuning Reports

You can use a wizard-generated report as is, and a wizard-generated report may be quite adequate for a quick data printout, say for proofreading purposes, as mentioned earlier. But for reports designed to be saved and studied, I recommend making the following changes to give your reports a polished appearance. You won’t need to make all of these changes for every report, but you’ll usually need to make most of them.

  • Remove unnecessary column headings for the group fields — if a field prints in a group header, it doesn’t need a column heading, too.
  • Adjust the labels and text boxes to the correct width to display their captions or data.
  • Change control text alignment as needed, such as setting the TextAlign property to Left for a numeric field that’s used as an identifier. (By default, Access right-aligns all numeric fields.)
  • Use a ControlSource expression to combine two or more fields to be displayed in one text box, such as the CurrencyType and Cost fields shown in the Cost column of the report shown in Figure 7-17, which are combined using the following expression:

     =IIf([Cost]>0,[CurrencyType] & Format([Cost],"#,###.00")) 

  • Format a dollar amount for two decimal points for standard display or zero decimal points to display rounded amounts (or apply the appropriate formatting for other currencies).
  • To prevent the user from wondering why a blank appears where data is expected, create an expression to print an informative phrase if data hasnot been entered into a field. In the example used in the "Using Grouped Reports" section, the following ControlSource expression prints No publisher entered if the PublisherCode field is empty:

     =IIf([PublisherCode],"Publisher: " & [PublisherCode], "No publisher entered") 

  • Move column headings as needed to improve the layout in a printed document.
  • Set the HideDuplicates property to Yes for the detail fields to prevent duplicate data from being printed on a grouped report.
  • Set the CanGrow property to Yes for the Title control, and also for any controls that can contain more than one line of data, to allow very long text to wrap.
  • Set the CanShrink property to Yes for all the detail controls, and also for the detail section itself, to avoid blank rows on the report when duplicate data is hidden or the field has no data for a record.
  • Set the KeepTogether property to Whole Group for a group in the Sorting And Grouping dialog box (as shown in Figure 7-16) so that the group will start on a new page if a page break would otherwise appear in the middle of the group.

    figure 7-16.set the keeptogether property to whole group to prevent an author group from splitting.

    Figure 7-16. Set the KeepTogether property to Whole Group to prevent an author group from splitting.

  • Set the RepeatSection property of header sections to Yes in their properties sheets so that these headers will repeat if needed.
  • If you don’t want to start each group on a new page, insert a blank line after a group by setting GroupFooter to Yes in the Sorting And Grouping dialog box. No controls are needed in this footer section, although you can add a dividing line if you want.

The third page of the sample report, modified in these ways, is shown in Figure 7-17.

figure 7-17. this report is more attractive after fine-tuning.

Figure 7-17. This report is more attractive after fine-tuning.

Changing Report Names, Titles, and Captions

Access reports have names (listed in the Database window) and captions (displayed as text in the report’s title bar). Additionally, reports—at least those you create with the Report Wizard—have a Title label control in the report header. You can examine (and change) a report’s caption by using the Caption property in the report’s properties sheet, as shown in Figure 7-18. You can’t change a report’s name in its properties sheet because no Name property is displayed there.

Figure 7-18. You can change a report’s caption by changing the <i>Caption</i> property.

Figure 7-18. You can change a report’s caption by changing the Caption property.

Reports do have a Name property, however. You can print the report name using this property by placing an expression such as the following in the Control Source property of a text box:

 ="Report name: " & [Name] 

A text box listing the report name (say, in the report’s page footer) is a handy way of identifying the name of a printed report, in case you need to revise it.

The Report Wizard uses the term "title" on the last page of the wizard. This can be confusing since reports don’t have a Title property. The text you enter as the report’s title will be used as both caption and name. This text will also be displayed in the title label control in the report header. This situation presents a problem for users who want to name their database objects in accordance with a particular naming convention, such as the Leszynski Naming Convention (LNC) discussed in Chapter 20, "Customizing Your Database Using VBA Code." If you use the LNC naming convention for database objects, your report names should start with the rpt tag. If you title the report rptBooksByAuthorAndPublisher in the Report Wizard, both the report’s title bar (in print preview) and the large title label in the report header will display rptBooksByAuthorAndPublisher, which looks strange.

If you want the report’s caption to differ from the report’s name (which is generally a good idea), you have to give the report a title in the wizard and then rename it in the Database window, as shown in Figure 7-19. To rename a report in the Database window, select the report name and press F2 to switch to edit mode. Edit the name as desired, and press Enter.

figure 7-19. you can rename a report in the database window.

Figure 7-19. You can rename a report in the Database window.

Alternatively, you can give the report the title rptBooksByAuthorAndPublisher in the wizard and then change the caption to Books by Author and Publisher in the report’s properties sheet and in the Title label.

Columnar Reports

Columnar reports, though plain in appearance, are useful for proofreading and checking data; their one-field-per-row layout leaves lots of room for marking corrections. The quickest way to create a columnar report is to click the New Object: AutoReport button on the toolbar, as described in the "Creating a Columnar Report Quickly" section. However, you can also create a columnar report by following these steps:

  1. Click the Reports tab of the Database window, and then click the New button at the top of the Database window.
  2. In the New Report dialog box, select a table or query from the record source drop-down list, and then select AutoReport: Columnar from the list of report types.
  3. Click OK. The rather ungainly report shown in Figure 7-20 is created.

    figure 7-20.this report was created using the autoreport: columnar selection.

    Figure 7-20. This report was created using the AutoReport: Columnar selection.

You can improve this report by adjusting control sizes as needed or by using expressions to concatenate address data, as described in the Troubleshooting sidebar "My Report Has Gaps in Its Data".

Tabular Reports

Tabular reports are most useful when you want a report laid out like a worksheet, usually to display financial data.

To create a tabular report, follow these steps:

  1. Click the Reports tab of the Database window, and click New at the top of the Database window.
  2. In the New Report dialog box, select a table or query in the record source drop-down list, and then select AutoReport: Tabular.

This report type (see Figure 7-21) looks much like the stepped report except that it lacks the group levels.

figure 7-21. the finished tabular report looks like this.

Figure 7-21. The finished tabular report looks like this.

Although a tabular report somewhat resembles an Excel worksheet, it’s significantly more difficult to work with than a worksheet or a datasheet form. This report doesn’t have adjustable columns like a worksheet, just text boxes and labels that you must move manually if you want to resize the columns.As is typical of reports created using the Report Wizard, you’ll need to resize some labels and text boxes to display all of their captions or data without truncation.

Chart Reports

Chart reports are used when you want to display your data graphically. The MS Graph component used to create Chart reports is showing its age, but it is still useful. However, you may prefer to just create a PivotChart instead. (See the "PivotChart Subreports" section) for details on this method.)

To create a meaningful chart, you need to select appropriate fields; generally charts are based on queries that preselect data, perhaps from several linked tables. For a pie chart, you need only two fields: one for the series, and one for the data. For example, you have undoubtedly seen a newspaper pie chart that shows government expenditures by department—the dollar amount of expenditures is the data on the pie chart, while the department names are the series.

Other chart types require three fields: one for the series (the horizontal labels), one for the axis (the vertical labels), and one for the data that is summarized. To extend the government expenditures example, a bar graph could have expenditures as data, department names as the series field at the bottom of the chart, and years as the axis field along the left side of the chart.

When creating a report with a chart, you usually won’t find all the data you need in a single table, so start by preparing a query that contains the data you want to display in the chart.

For information about creating a query, see Chapter 9, "Using Queries to Select Data."

After you’ve created the query, follow these steps:

  1. Click the Reports tab of the Database window, and then click New at the top of the Database window.
  2. In the New Report dialog box, select the query (or a table) that contains the data you want to chart in the record source drop-down list, and then select Chart Wizard.
  3. Select the fields to be charted. (See Figure 7-22.) As mentioned, for a pie chart you need only two fields: one for the series and one for the data. Other chart types require three fields: one for the series (the horizontal labels), one for the axis (the vertical labels), and one for the data that is summarized. Click Next.

    figure 7-22.select fields for the chart in the chart wizard.

    Figure 7-22. Select fields for the chart in the Chart Wizard.

  4. Select the chart type. Figure 7-23 shows the 3-D cylinder bar chart selected. Click Next.

    figure 7-23.select a chart type for the chart report.

    Figure 7-23. Select a chart type for the Chart report.

  5. The next page of the wizard allows you to preview the selected chart and adjust the fields used for the chart components as needed. One field is used for the axis (the bottom of the chart), another field is used for the series (the right side of the chart), and a third field is summarized for the data. By default, Access sums numeric data and counts non-numeric data, such as product codes and dates. In Figure 7-24, PublisherCode is the axis, Speccode is the series, and a count of BookID provides the data for the chart.

    figure 7-24.the chart wizard allows you to preview the selected chart.

    Figure 7-24. The Chart Wizard allows you to preview the selected chart.

    Click Preview Chart to preview the chart in a small window. Click Close to close the Sample Preview window. Then click Next.

  6. Name the report, as shown in Figure 7-25. The title you enter on this page is used only for the title displayed in the chart; it is not used for the report name or caption. You also have the choice to open the report or modify it in Design view. You’ll usually want to select the Modify option because you’ll need to resize the chart.

    figure 7-25.the name you give the chart report is not used as a report name or caption.

    Figure 7-25. The name you give the chart report is not used as a report name or caption.

  7. Click Finish. The report opens in Design view, as shown in Figure 7-26. The chart is very small; you’ll probably want to resize it.

    figure 7-26.the finished chart report looks like this in design view.

    Figure 7-26. The finished Chart report looks like this in Design view.

    InsideOut

    When you look at a chart report in Design view, you won’t see the real data, only dummy data. This is a peculiarity of MS Graph charts. The newer PivotCharts don’t have this constraint, so if you find it easier to work with a chart where you can see the real data, PivotCharts are a better choice than Chart reports. See the "PivotChart Subreports" section and Chapter 12, "Using PivotTables and PivotCharts to Analyze Data," for more information on creating PivotCharts.

  8. Drag the page footer down and the right edge of the report grid out until you get the report size you want, and then stretch the chart diagonally until it fills the report. Figure 7-27 shows the chart in print preview, resized to display its data clearly and named according to the LNC.

    figure 7-27.the finished chart report has been resized and named according to the lnc.

    Figure 7-27. The finished chart report has been resized and named according to the LNC.

InsideOut

You might need several runs through the Chart Wizard to get a usable chart. Unlike PivotCharts, MS Graph charts can’t be easily adjusted after creation. For this reason, I recommend that you use PivotCharts on reports when you need to display data graphically. (See "PivotChart Subreports.")

Label Reports

Use the Label Wizard to create mailing labels and other types of labels. The following example creates Avery #5161 mailing labels for the tblAuthors table in the sample Crafts database.

To create a label report, follow these steps:

  1. Click the Reports tab of the Database window, and then click New at the top of the Database window.
  2. In the New Report dialog box, select tblAuthors as the report’s record source and Label Wizard as the report type.
  3. Select the label type on the first page of the Label Wizard, as shown in Figure 7-28. Click Next.

    figure 7-28.select a label type in the label wizard.

    Figure 7-28. Select a label type in the Label Wizard.

  4. Select the font to use for the labels, as shown in Figure 7-29. A font size of 8 to 10 points is generally advisable. Click Next.

    figure 7-29.select font characteristics for the labels.

    Figure 7-29. Select font characteristics for the labels.

  5. Select the name and address components to use on the labels by field, entering punctuation marks as needed and pressing Enter to start a new row. Figure 7-30 shows the completed label layout. Click Next.

    figure 7-30.place name and address fields on the label.

    Figure 7-30. Place name and address fields on the label.

  6. Sort the labels by one of the fields, as shown in Figure 7-31. Click Next.

    figure 7-31.select a field for sorting the labels.

    Figure 7-31. Select a field for sorting the labels.

  7. Name the Labels report. (See Figure 7-32.) The name entered here is used for the report name and caption. Click Finish.

    figure 7-32.name the labels report on the last page of the wizard.

    Figure 7-32. Name the Labels report on the last page of the wizard.

caution


Asof the time of writing, label reports had two serious problems: The labels are the wrong size for the paper and printer, so they have to be manually resized; and most ZIP codes don’t print (although United Kingdom postal codes do print).

Creating Reports Manually

In the previous sections, you learned how to use the Report Wizard and the New Report dialog box to generate reports using Access’s pre-designed report styles. But not all the reports you create will fit into one of the automated report styles you’ve seen thus far: Some reports you’ll need to create manually. The following sections explain reports you may want to design on your own with Access, including free-form reports, multicolumn reports, and crosstab reports.

Free-Form Reports

A free-form report is one that you put together any way you like. No controls are placed on the report by default; you place controls on the report manually. To create a free-form report, follow these steps. (The same steps are used to start a multicolumn or crosstab report.)

  1. Click the Reports tab of the Database window, and click New at the top of the Database window.
  2. In the New Report dialog box, select Design View for the report type, and select a table or query as the report’s record source, as shown in Figure 7-33.

    figure 7-33.start a free-form report when none of the standard report formats meet your needs.

    Figure 7-33. Start a free-form report when none of the standard report formats meet your needs.

A blank report opens in Design view. Use the Toolbox to place controls on the report in the layout you want, format them as desired, and then open the Sorting And Grouping dialog box to set up report groups and sort the data as you prefer. See Chapter 6, "Working with Form Controls," for more information on working with controls; controls work much the same on forms and reports.

Multicolumn Reports

Multicolumn reports are useful for displaying data from one or a few fields in a space-saving manner. They are typically used to produce phone directories, lists of product codes, and other such information laid out in several columns. The Crafts Categories report in the sample Crafts database is an example of a multicolumn report.

InsideOut

Access has no wizard for creating a multicolumn report, although one is certainly needed since the settings used to create a multicolumn report in Access are so well hidden that many users don’t even realize that it’s possible to create such a report.

To create a multicolumn report, follow these steps:

  1. Create a blank report bound to the table or record source containing the data you want to put on the report.
  2. Choose File, Page Setup to open the Page Setup dialog box, and then click the Columns tab.
  3. Enter the number of columns you want in the Number Of Columns box, enter the desired row spacing (to insert vertical space between rows) in the Row Spacing box, and enter column spacing, width, and height in their property boxes. Select one of the two column layout options, and then close the dialog box. Figure 7-34 shows the completed Columns tab.

    figure 7-34.set the column properties for a multicolumn report.

    Figure 7-34. Set the column properties for a multicolumn report.

    In Design view, a multicolumn report doesn’t look different from a standard (single column) report—that is, there’s no indication of the column boundaries on the report grid. This means that when you place controls on the report grid, you need to keep them within the boundaries of a single column width. The report header and footer, however, will span the entire report width.

  4. Add a label control for the report title in the report header.

    Figure 7-35 shows the finished two-column report.

    figure 7-35. the finished two-column report looks like this.

    Figure 7-35. The finished two-column report looks like this.

If your report is both grouped and multicolumn, you can choose to start each group in a new column by setting the NewRowOrCol property of the group header to Before Section. Figure 7-36 demonstrates this option for a report based on qryBooksVideosSpecs.

figure 7-36. you can set a group header’s properties to start a new group in a new column.

Figure 7-36. You can set a group header’s properties to start a new group in a new column.

Figure 7-37 shows the grouped three-column report in print preview.

figure 7-37. a three-column grouped report looks like this in print preview.

Figure 7-37. A three-column grouped report looks like this in print preview.

Numbering Report Items

A report that lists many items often needs to have the items numbered sequentially so that they can be conveniently referenced. This is especially useful when the items don’t have IDs that can be used to identify them. The RunningSum property (available only for controls on reports) can be used to dynamically number report detail items, as follows:

  1. Place a text box named txtRunningSum at the left side of the report’s detail section, with a control source of =1 and the RunningSum property set to Over Group (to reset to 1 at the beginning of each group) or Over All (to number continuously throughout the report).
  2. If you want a period after the number, add another text box (txtNumber) with a control source, as follows:

     =[txtRunningSum] & "." 

Figure 7-38 shows the Books By Year report with consecutive numbers for books in each year group, which are handy for referencing the books.

Figure 7-38. Use the <i>RunningSum</i> property to create a report with numbered detail items.

Figure 7-38. Use the RunningSum property to create a report with numbered detail items.

Crosstab Reports

If you’ve created a crosstab query that summarizes data in a useful manner, and you want to print and share the summarized data with other department members, you can generate a crosstab report to print the output of the crosstab query.

To create a crosstab report, follow these steps:

  1. Create a crosstab query, as described in Chapter 9, "Using Queries to Select Data."
  2. Create a blank report in Design view. Select the crosstab query as the report’s record source.
  3. Drag all the fields from the field list to the report grid. (If the field list isn’t visible, click the Field List button on the toolbar to open it.)
  4. Select each label in turn, click Cut on the Edit menu to delete the label and save it to the Clipboard, and then select the form header selector and click Paste on the Edit menu to paste the label into the header section.
  5. When you have pasted all the labels into the form header, drag them to appropriate locations for column headings and format them.

    If you want, you can place a line control under the labels used as column headings.

  6. Arrange the text boxes under each column heading in the report’s detail section.
  7. If you want, you can add a large label for a title in the form header.

    Figure 7-39 shows the crosstab report in print preview.

    figure 7-39. the crosstab report in print preview looks like this.

    Figure 7-39. The crosstab report in print preview looks like this.

tip - Save time revising your report structure


If your data is relatively fixed and your columns won’t change—for example, if they contain quarterly financial data—a crosstab report is useful. But as you add data to the table underlying a crosstab report, the number of columns may change, so you’ll frequently have to revise the report structure. To avoid this problem, use a PivotTable report instead. (See"PivotTable Subreports.")



Microsoft Access Version 2002 Inside Out
Microsoft Access Version 2002 Inside Out (Inside Out (Microsoft))
ISBN: 0735612838
EAN: 2147483647
Year: 2005
Pages: 172
Authors: Helen Feddema

flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net