MS Access for the Business Environment: Reporting in MS Access: Grouped Transactional Report Part I
September 2, 2003
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:
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.
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.