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

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.

William Pearson
William Pearson
Bill has been working with computers since before becoming a "big eight" CPA, after which he carried his growing information systems knowledge into management accounting, internal auditing, and various capacities of controllership. Bill entered the world of databases and financial systems when he became a consultant for CODA-Financials, a U.K. - based software company that hired only CPA's as application consultants to implement and maintain its integrated financial database - one of the most conceptually powerful, even in his current assessment, to have emerged. At CODA Bill deployed financial databases and business intelligence systems for many global clients. Working with SQL Server, Oracle, Sybase and Informix, and focusing on MSSQL Server, Bill created Island Technologies Inc. in 1997, and has developed a large and diverse customer base over the years since. Bill's background as a CPA, Internal Auditor and Management Accountant enable him to provide value to clients as a liaison between Accounting / Finance and Information Services. Moreover, as a Certified Information Technology Professional (CITP) - a Certified Public Accountant recognized for his or her unique ability to provide business insight by leveraging knowledge of information relationships and supporting technologies - Bill offers his clients the CPA's perspective and ability to understand the complicated business implications and risks associated with technology. From this perspective, he helps them to effectively manage information while ensuring the data's reliability, security, accessibility and relevance. Bill has implemented enterprise business intelligence systems over the years for many Fortune 500 companies, focusing his practice (since the advent of MSSQL Server 2000) upon the integrated Microsoft business intelligence solution. He leverages his years of experience with other enterprise OLAP and reporting applications (Cognos, Business Objects, Crystal, and others) in regular conversions of these once-dominant applications to the Microsoft BI stack. Bill believes it is easier to teach technical skills to people with non-technical training than vice-versa, and he constantly seeks ways to graft new technology into the Accounting and Finance arenas. Bill was awarded Microsoft SQL Server MVP in 2009. Hobbies include advanced literature studies and occasional lectures, with recent concentration upon the works of William Faulkner, Henry James, Marcel Proust, James Joyce, Honoré de Balzac, and Charles Dickens. Other long-time interests have included the exploration of generative music sourced from database architecture.

Latest Articles