A database can hold almost any kind of data, can be sorted and displayed in a variety of ways, and solves many organizational problems. Efficient entry and analysis of that data is very important.

This article will create a report on sample data collected on elementary school children and their accidents, and attempt to make some analyses from it. MS-Access permits the user to invoke a wizard that will create a standard report. We will create a report in Design View and along the way, discuss a few mistakes one can make.

The database contains records in a table with fields of District, School, Name, Date/Time, and Accident Description. (Note: the data for this article is purely fictitious and the conclusions drawn from the analyses are probably wrong but serve only to illustrate the possibilities.)

We want to create a professionally-crafted report that has the District and School data included at the top of every page and, when the School name changes, beginning a new group, the new school starts on a new page. We also want front and back cover pages.

In the Design View, we are shown a blank grid area that represents the design area on the page. Begin by dragging the right border of the grid to the 6.5" mark. By default, the page margins are 1.25" on both sides. You can change that if you want (File, Page Setup, Margins), but realize that the sum of the two margins plus the width of the grid will add up to no greater than the width of the paper. Otherwise, a caution message stating that the document exceeds the boundaries will pop up when you switch from Design View to Print Preview mode. So now, the grid area more or less represents the printable area of the page.

There are various sections that a report may have: an overall report header and footer (better thought of as front and back covers), a page header and footer, any number of grouping headers and footers, and a detail section. Within some of these sections, a sub-report can be placed. I won't cover that here. However, within each section, a wide range of design elements can be placed. These elements include lines, text, pictures, action buttons, and drop-down lists. The text can be labels, the contents of table fields, or the results of calculations.

Let's start by getting to the meat of the problem resolved by the SPCUG AnswerGuys and add the flourishes later.

The essential problem was that the elements in the School Header group sections weren't being printed when a new page was required to continue the group listing. Also, there was an element in the group's footer section that seemed to do what was needed and that's what happened. But there was a better way to do it.

In the School group's footer, the individual had placed a "page break control," intending to begin the next group on the next page. The report has a group footer section an eighth-inch high and is only placed on the page when the last line item in that group is printed. What if there's not that much space available at the bottom of the current page? The group footer, which has the page break on it, gets printed on the next page. That page ends up being blank. It doesn't happen often, but it's something that can and should be avoided.

There are legitimate needs for the page break control, but not in this case. The footer has a built-in function that does the same thing and by using it we can eliminate the eighth-inch strip of page space that might end up on the next page.

Double-click on the Group Footer bar and find the "Force New Page" property. From the choices, select "After Section," then close the properties window. Move the Page Footer bar up against the bottom of the School Footer bar to eliminate that eighth-inch.

Normally, the group header section is only placed on the page when the top line item is from a group different than the top line item of the previous page. Since a forced new page occurs at the end of every group, the School Header section should appear whenever the name of the school changes. But even if there are enough line items to fill more than one page, only the first will have that group header section. One possibility is to put the data from the School field in the Page Header section. Bad move.

It's really not obvious why since the correct information would display. A group's header and footer will be placed on the page bracketing every new group.

Trouble comes when you might not want a new page after every group. That means several different groups could appear on the same page. And yet, only one school name appears on the page if the Page Header is where the field information is placed. Better to keep grouping information in the Group Header and Footer sections.

Another problem discovered with this individual's report was that each detail line item took 1.5" of space on the page or greater, regardless of how much information was actually printed. If that's what you want, then there's a right way and a wrong way. The wrong way is to make the text box short and allow it to grow. The right way is to make the box 1.5" high, do not allow it to grow, and try not to have too much text. Here's why:

If a box is allowed to grow (gets taller as text fills it), everything in the section below it has to be shoved down the same amount. That includes the blank space between the bottom of the box and the section bar of the section below. If the box gets taller by a half-inch, the whole detail section grows taller by a half-inch. When placing the box, size it so that the bottom of the box is touching the section bar below.

There is one flourish to the report that hasn't been solved yet. By employing a bit of programming, a solution may present itself. But for the time being, the report has dashed vertical separator lines. Even if the Detail section grows, the divider lines will not. The result looks weird. Maybe the proper object hasn't been discovered in the collection of tools, maybe a third-party object is needed. Further exploration is necessary.

Finally, be sure to put appropriate labels in the Report and Page Header and Footer sections. You need to properly title the report, put page numbers and possibly the "date printed," as well as giving yourself credit for a job well done.