MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part I

Tuesday Sep 2nd 2003 by William Pearson

Create a report that mirrors a real-world business need. In the first segment of this two-part article, author Bill Pearson begins the step-by-step design of a report that presents transactional customer information, then groups that information at multiple levels with corresponding totals.

About the Series ...

This article continues the series, MS Access for the Business Environment. The primary focus of this series is an examination of business uses for the MS Access relational database management system. The series is designed to provide guidance in the practical application of data and database concepts to meet specific needs in the business world. While the majority of the procedures I demonstrate will be undertaken with Access 2002, many of the concepts that we expose in the series will apply to other versions of MS Access.

For more information on the series, as well as the hardware / software requirements to prepare for the tutorials we will undertake, please see Tutorial 1: Create a Calculated Field with the Expression Builder.

Introduction to this Tutorial

This two-part tutorial will explore the creation of a basic report in MS Access to fit a common business need. The report type under consideration will be a transactional report that groups the information it presents at multiple levels, for most of which a corresponding total will be displayed. Our data source will be the Northwind sample database that is installed as a part of a typical Access installation. Other Reporting in MS Access tutorials will follow, the focus of which will be to illustrate specific methods for successfully presenting data, within the context of specific business needs that are illustrated well by a given type of common report, in the MS Access environment.

The report that we create in our lesson will focus on orders placed by the customers of Northwind. It will present summary information about orders placed by customers over year-to-date and other time intervals--intervals that can be controlled by a given information consumer at report run time. We will discuss the specific business needs that the report addresses, as well as several key steps involved in professional report design and creation. Next, we will proceed through the individual steps to design and create our report in a hands-on manner. Within each step, we will undertake an examination of the details involved, and the results that we expect to obtain, within our design.

In this, the first part of the two-part tutorial, our focus upon the design and creation of a multiple level report will include:

  • A general discussion of common steps for successful reporting efforts;
  • Gathering of business requirements from the intended audience of the report;
  • General report design considerations;
  • Locating and accessing the targeted data within the data source;
  • Creation of the new report;
  • Preliminary setup of report characteristics.

Part II will pick up with the preparation for report sorting and grouping, and the data selection and inclusion process, then continue with the common steps for reporting that we will overview in this section. In addition, we will return to the data source, as well as to the report, to adjust them to meet illustrative enhancements requested by information consumers as part of the review and feedback cycle that takes place in collaborative report design.

Let's begin by discussing the steps common to the design of any effective report. Then, having an idea of the associated general procedures, we will look at the steps required to design and create a report to meet a hypothetical business need.

Common Steps for Successful Reporting

While the majority of the reporting projects that we encounter in the business world will have individual peculiarities and special considerations, several common steps tend to usher us toward a successful conclusion. These steps include the following:

This article will focus upon each of the above steps within the context of the multi-level transactional report that we have selected for this session. Future Reporting in MS Access sessions will deal with different considerations that might apply for other types of reports. Suffice it to say that each will have its own requirements and will need to be developed, to some extent, to meet the specific needs of the audiences, although the general steps above will be very similar in most cases.

Although MS Access 2002 provides wizards to help us create simple reports, we will focus on the creation of a report with a level of complexity similar to that which we might expect to encounter in a business scenario. We will therefore begin with an empty form, and create from scratch an entire report to meet the expressed needs of the information consumers that have requested it.

Gather the Business Requirements

The creation of a professional report requires planning. I cannot count the times that I have witnessed clients, as well as fellow consultants, get a general idea of the final product, then cast off into immediate creation of a report with an incomplete or incorrect understanding of the specifications. This typically means, at best, that the report writer is compelled to return to the information consumer several times to gain a better understanding of the data, to ask questions about the intended appearance of the report, or to otherwise assemble an idea of the intended structure that he / she is attempting to create.

Interviewing members of the intended audience for the report is obviously of great value in designing the final product. Multiple perspectives within the audience can be paramount in creating a superior product. We can attempt to address those perspectives through a consolidated medium in many cases, rather than creating a specific report for each of several perspectives whose differences are so minimal that we might have easily addressed them with a little creativity in the design of a single report.

An example that I see frequently: We are putting together the requirements for an Accounts Receivable report, complete with the associated aging "buckets," when we notice that the time ranges for the "buckets" ("Current", "30-60 days," "60-90 days," and so forth), within which we intend to aggregate data, are identical to those required in an Accounts Payable report for the same organization. The two reports, in fact, appear identical in draft, with the only real difference being the accounts selected for inclusion in each report: the Accounts Receivable or Accounts Payable account(s).

While it is often difficult to get a refined target from a prospective consumer, especially when the consumer is not fluent in database, business intelligence and / or reporting tool concepts, it often helps to enforce a policy of drafting a "picture" of the intended report, and gaining consensus on the design of the draft with members of its intended audience, before even beginning to design the report in MS Access.

For purposes of our lesson, we will assume that we have been asked to develop a report that presents summary information about cumulative orders placed by customers. While other needs might be identified as a part of our report building process, we will initially set out to meet the need as expressed in our business requirements gathering phase, making enhancements and other adjustments as we progress with feedback from members of the intended audience.

Design the Report

After discussing the report with our information consumers, we create an initial draft based upon our understanding of the business requirement. We then present the draft, listening closely to the feedback of the report's intended audience. We might make a few tweaks to the draft, resulting in a document (we will tentatively call it the Customer Orders Report) that is roughly depicted in Illustration 1.

Illustration 1: Draft of the Proposed Customer Orders Report

The drafting process also helps to generate discussion about the intended purposes of the report, together with ways to make the report serve multiple ends--for example, through the use of prompts we can often make a "monthly" report become a "quarterly" or "annual" report, based upon the runtime input that a consumer might provide. Discussions with the consumers involved can often mean far more useful reports in this and other areas. It also helps to fine tune our design, as items that were overlooked frequently come to light as a part of the drafting process.

Once we have a basic draft of the report design (having used any of several means for creating the draft, including good old pencil and paper, when appropriate), and once we envision ways we might extend the utility of the report beyond, perhaps, the initial stated need, we are ready to locate the data that we will need to support the presentation we have drafted. Keep in mind that our mission is to present data from our corporate database in a way that is useful to its audience. The options available for sourcing that data within MS Access are either to pull it from a table, or from a query that we create to assemble the data from a table, or multiple tables. Binding the report to a query, versus a single table, is, by far, more common.

Let's get to the first step of building our report: We will begin by locating and accessing the data through a query similar to the one we have discussed.

Locate and Access the Data

Because we have created a preliminary design, we have a complete idea of the data that the report will need to access. In addition to our draft (shown in Illustration 1 above), we might want to list the data fields that the report will contain. For purposes of this exercise, we will note their identities as we build the query, from which we will entrain them into the report.

Let's get started by opening MS Access, and creating a query that will act as the source of data for the report.

1.  Go to the Start button on the PC, and then navigate to the Microsoft Access icon.

2.  Click the icon to start Access.

MS Access opens, and may display the initial dialog. If so, close it.

3.  Select File -> Open from the top menu, and navigate to the Northwind sample database (the file might also be accessed from the Open a File menu atop the task pane, if it has not been disabled previously, at the right side of the main window in Access 2002.)

4.  Select Northwind.mdb.

The splash screen may appear, depending upon whether we have suppressed it before.

5.  Click OK, as necessary.

The splash screen disappears, and is replaced by the Main Switchboard, as we have seen in earlier lessons.

6.  Click the Display Database Window, or get there by an alternative approach.

We arrive at the Database Window.

7.  Click Queries, under Objects in the Database window.

8.  Click New on the Database window toolbar, just above the Objects pane.

The New Query dialog appears, as shown in Illustration 2.

Illustration 2: The New Query Dialog

9.  Ensuring that the Design View option is selected, click OK.

The Select Query dialog appears by default, with the Show Table dialog appearing in front, as shown in Illustration 3.

Illustration 3: The Select Query Dialog (Compressed View), with Show Table Dialog Foremost

10.  Select the following tables, highlighting each, and then clicking the Add button, to add each successively to the Select Query dialog.

  • Customers
  • Orders
  • Order Details
  • Products

11.  Click the Close button on the Show Table dialog to close it.

The Select Query dialog, upper portion, displays the newly added tables, appearing as shown in Illustration 4.

Illustration 4: The Select Query Dialog, Selected Tables (Compressed View)

We notice that the joins / relationships have been placed automatically. This is because the AutoJoin feature is enabled. (AutoJoin can be disabled by going to Tools -> Options, selecting the Tables/Queries tab, and unchecking the Enable AutoJoin checkbox).

We review the joins for correctness, as always, and find them to be adequate. Next, we select destination fields and, thus, determine the result datasets that the query will generate.

For each of the tables that follow, double-click the indicated fields to place it in the corresponding field of the matrix in the bottom half of the Select Query dialog.

12.  From the Customers table, select:

  • Customer ID
  • Company Name
  • City
  • Country

13.  From the Products table, select:

  • Product ID
  • Product Name

14.  From the Order table, select:

  • Shipped Date
  • Order Date

15.  From the Order Details table, select:

  • Unit Price
  • Quantity
  • Discount

The Select Query dialog displays the newly added tables and fields, appearing as partially shown in Illustration 5.

Illustration 5: The Select Query Dialog, Selected Tables and Fields (Partial View)

We will perform some added steps to make the query a more useful reporting data source at this stage.

16.  Move the Shipped Date column to the far left of the columns, by click-selecting it, then dragging and dropping it into position to the left of the Customer ID column.

The Shipped Date column is now the left most column in the display.

17.  Move the Order Date column to the immediate right of the Shipped Date column, so that it rests between the Shipped Date and Customer ID columns.

18.  Click the Criteria field for the Shipped Date column, to place the cursor into the field.

19.  Type the following expression into the Criteria field:

Between CDate("01-Jan-"+CStr(DatePart("yyyy",[AsOfDate]))) And [AsOfDate]

The purpose of this expression is to create a prompt for the "as of" date for which the report is to be generated. While not specifically requested by the information consumers, this feature is a common enough enhancement to those of us that are "old hands" at reporting. The ability to prompt for "as of" date means we can run the report not only for the date at which we physically generate it, but we can run it as of any other point in time and (assuming that we have data in place to select), and see the results as of the date we select at run time.

This expression also performs another function: it creates a date range that includes all dates from the beginning of the year (the year being based upon the "as of" date we supply at the prompt) up to the "as of" date; the time range that results will effectively act as a "year to date' range, as we shall see.

20.  Click the top ("Field") field of the first empty column to the right of the columns we have occupied in the query design grid (this should be the column to the immediate right of the Discount column), to place the cursor into the field.

21.  Type the following expression into the top field:

CCur([Order Details]![UnitPrice]*[Order Details]![Quantity]*(1-[Order Details]![Discount]))

The purpose of this column is to generate, via the calculation above, a Net Order value for each of the shipping transactions in the query. This value will serve as the basis for some of the details values and totals that we display in the report.

Let's save our work at this juncture.

22.  Select File -> Save As.

The Save As dialog appears.

23.  Type the following into the "Save Query 'Query1' to:" box:


The Save As dialog should now appear as shown in Illustration 6.

Illustration 6: The Save As dialog, with New Query Name

The Select Query dialog should now appear as partially shown in Illustration 7 (only enhanced / added fields depicted, to save space).

Click for larger image

Illustration 7: The Select Query Dialog (Alterations Only)

Now, let's run the query and examine the result set that it returns.

24.  Select Query --> Run from the main menu.

As soon as we kick off the report, we are prompted, via the Enter Parameter Values dialog that appears, for the "AsOf" date that we built into the selection Criteria field for the Shipped Date above.

25.  Type the following into the AsOf Date box:


The Enter Parameter Value dialog appears as depicted in Illustration 8.

Illustration 8: The Enter Parameter Value Dialog with our Response

26.  Click OK.

The Enter Parameter Value dialog closes and the query executes. The query returns its data set, whose size is 1,042 rows, as partially displayed in Illustration 9.

Illustration 9: The Data Set Returned by Our New Query (Partial View)

Scrolling through the result data set, we can see that the entire year of 1997 appears to be represented within its rows.

We have now created and saved a query in our Access database. This query will serve as the data source for the Customer Orders report that we will build in following sections to demonstrate the steps involved.

27.  Click File --> Close to close the Customer_Orders_Source query.

28.  Click Yes, if prompted to save the layout of query Customer_Orders_Source.

We are returned to the Database window.

Create the New Report

Now that we have put a query in place to act as a data source, we are ready to set about the report's creation, and its subsequent binding to its source. We will take the following actions to complete our objectives:

1.  In the Objects pane on the left side of the Database window, click the Reports button, shown in Illustration 10.

Illustration 10: Select Reports in the Object Pane of the Database Window

We arrive at the Reports window, and see various sample reports that are already there.

2.  Click the New button that appears in the toolbar, as depicted in Illustration 11.

Illustration 11: The New Button in the Toolbar of the Reports Window

The New Report dialog appears, with Design View selected by default.

3.  Ensuring that Design View is selected, click the arrow on the selector for the Choose the Table or Query where the Object's Data comes from box in the lower section of the dialog.

4.  Select the Customer_Orders_Source query we created in the last section.

The New Report dialog appears as shown in Illustration 12.

Illustration 12: The New Report Dialog, with Settings

5.  Click OK.

After a few seconds, perhaps less, we are greeted by the Report Design window, which displays header, detail and footer sections. Also apparent is the Field List from the Customer_Orders_Source query, which floats, as it were, over the Report Design window, as depicted in Illustration 13.

Illustration 13: The Report Design Window Dialog, with Source Field List

We can see, by the contents of the Field List, that the blank report is bound to the data source. We are now in a position to establish some of the physical characteristics of the report.

Establish Report Characteristics

While we will not go into too much detail about report formatting and cosmetics in this lesson, we will take a moment at this stage to consider layout characteristics for the new report page. This is an advantageous time to accomplish this, before we begin pulling in data and so forth; specifying layout first can, to some extent, help us to minimize the time-consuming movement of data later that characterizes the "afterthought" approach to design.

Let's set up preliminary page layout with these steps:

1.  Select File --> Page Setup from the main menu in MS Access.

The Page Setup dialog appears, defaulted to the Margins tab.

2.  Change the settings for each of the margins to 0.5, as shown in Illustration 14.

Illustration 14: New Margin Setpoints for our Report

3.  Move to the Page tab by clicking it.

4.  Ensure that the Portrait radio button is selected.

5.  Leave all other setpoints at default.

The Page tab of the Page Setup dialog should replicate that shown in Illustration 15.

Illustration 15: Page Setup--Page Tab Settings

6.  Click OK on the Page Setup dialog.

The Page Setup dialog closes, and we are returned to the report Design View.

7.  Click the Report Properties button in the Report Design View toolbar.

The Report Properties button is depicted in Illustration 16.

Illustration 16: The Report Properties Button

The Report Property Sheet appears.

8.  On the Format tab, in the Width field, type 7.5. (This is converted to inches (") when you click outside the field / close the dialog, or otherwise "apply" the setting to the field.

The Width field appears with the desired setting in Illustration 17. This is the setting for the width of the entire report, as it is set at the Report property level.

Illustration 17: Report Property Sheet, Width Field with New Setting Circled

9.  Close the Report Property Sheet.

The report appears as depicted in Illustration 18 in its current empty state.

Illustration 18: The Blank Report, Design View

10.  Select File --> Save As.

The Save As dialog appears.

11.  Type "Customer Orders Report" in the Save ... To: box.

12.  Leave the As selector set to Report.

The Save As dialog appears as depicted in Illustration 23.

Illustration 23: The Completed Save As Dialog

13.  Click OK to save the report.

We have done the majority of the preparation for populating our report with the data that has been requested by our information consumers. Having accomplished the preliminary layout for our report, we will begin, in Part II of this tutorial, to bring in and arrange the data for presentation.

Conclusion ...

With this lesson, we began a two-part tutorial surrounding the creation of a transactional report that groups and summarizes the information it presents at multiple levels. We discussed the requirements that the report will need to address, then listed the general steps involved in professional report design and creation. We then began to proceed through the individual steps to design and create our illustrative report in a manner that closely mirrors that of a successful report writer in a collaborative business environment. Within each step, we discussed the details involved and the results that we seek to obtain within our design.

In Part II, we will pick up where we left off with the common steps for successful reporting efforts, focusing initially on the sorting and grouping of data in the report, then selecting data, from the data source we created in Part I, for inclusion. After entraining the data, we will focus upon the arrangements of labels and text in the report, as well as establishing settings based upon grouping and other attributes specified by the intended audience. Finally, at relevant points throughout the report creation cycle, we will review and refine the report based upon feedback that we receive from information consumers who review the report at various evolutionary stages.

» See All Articles by Columnist William E. Pearson, III

Mobile Site | Full Site
Copyright 2017 © QuinStreet Inc. All Rights Reserved