Drewslair.com - Microsoft Access for Beginners: Generating the Reports

Microsoft Access for Beginners

Part V - Generating the Reports

Introduction
From Forms to Reports
Creating a Report
Report Margins
Sample Reports
     Employee Profile
          Custom Report Sections
          Adding Subreports
          Automatic Report Features
          Custom Report Sections
     Printing Labels
     Employee List
Managing the Interface
     Selecting the Data
     Custom Menus
Conclusion

You can read the entire Microsoft Access for Beginners series, completely reviewed and updated for Access 2010, in the new Kindle edition now available from Amazon.com!

Introduction

Most database applications of any size involve some kind of reporting system. A well designed system compiles and summarizes information in a way that can be easily understood by those who need to make decisions based on it. Fortunately, one of the strengths of Microsoft Access is its built-in reporting tool. With Access, you can design a variety of report types, including charts and graphs, as easily as you would design a form.

Sample Files

If you have not already done so, please download the demonstration files:

Demo.zip - Access demonstration database and original Excel spreadsheet (These files require Access and Excel 2000 or later.)

From Forms to Reports

Designing reports is very similar to designing forms. Once you're familiar with one, you'll find many of the same features in the other. The only difference is that, in general, reports are intended for printing where forms are meant for screen display and user interaction. While reports use the same controls to display data such as text boxes, labels and subreports, the data is only intended for presentation and cannot be edited on the report.

Some other similarities:

Reports are based on a query that pulls the data from the tables. The data can be drawn from multiple related tables.
Once the source of the data is specified, controls are arranged and placed on the report design grid in the same way as they are on forms.
The report uses the same controls and tools as forms do although some controls such as combo boxes may appear differently in preview mode.
Reports have a set of properties and events that you can work with to control the behavior of the report.

Creating a Report

Creating a new report is very much the same as creating a new form and, as mentioned above, they share many features. The report has a Record Source property that determines where the data comes from and each control on the report has a Control Source that specifies which field it interacts with. The main differences are a greater emphasis on header and footer sections and the formatting of pages for printing rather than display.

Creating a new report in Access is much the same as creating a new form with options for automatically generated reports.

New reports are automatically divided into three main sections that can contain data:

Reports in Access have multiple sections on which data controls can be arranged to present the data exactly as needed.

Report Header / Footer - This section appears at the beginning and end of a report and can contain such things as the report title and the date it was run. On a multi-page report, the header only shows on the first page and the footer on the last.
Page Header / Footer - This section is for information you want on every page such as page numbers or data headings.
Detail - This section is for the actual report data and would contain all of the rows generated by the query behind the report.

Any of the header and footer sections can be shown or hidden based on the needs of your report. You can also add custom header and footer sections to reports based on the way data is grouped. An example in the demo database is the Employee Profile report where the data is grouped on the EmpKey value. This value is unique for each employee so it doesn't actually combine any rows but it does provide a value for the report to reference. By adding a header section for this value to the report, I'm able to generate a separate report for each employee as needed. I'll show more of the details on how this is done later in the chapter.

A report can include custom sections based on the way the data is grouped on the report. Here, the EmpKey header provides a space for information specific to the employee. As with forms, fields are added to the report by dragging them into design view from the field selector or manually setting the Control Source property.

Report Margins

While in design view, you can select Page Setup from the File menu in Access 2003 and the Page Setup tab from the Office ribbon in Access 2007 to see the page settings for the report as it will be printed. The first tab shows the margin settings for the report. The screenshots below show some of the page settings for the Employee Profile report.


(Click on either image for full size view.)

On the Margins tab, I have all of the margins set to 0.5 inches to provide plenty of space for the data. The Page tab shows that I've used the default settings for this report with letter-sized paper (8.5" x 11") and the orientation set to Portrait. The Source setting under the Paper section enables you to print from specific trays on your printer or to use a manual feed for such things as labels and envelopes. Also note at the bottom of this tab that you can specify a specific printer for the report from the printers setup on your computer. An example would be if you had a wide report that could only be printed on a wide-carriage printer.

Another useful feature of the design environment is the ruler guide that shows the height and width of the report page. While a form that is too big for the screen will only require the user to move a scroll bar, a report that is too big for the paper it's being printed on will print the overflow on separate pages which will probably not be what you want.

In the report designer, you can set the dimensions of the report to match the paper being used and the margins specified.

The screenshot above shows part of the design view for the Employee Profile report. Notice that the right edge of the report is set at 7.5" because the report is set with 0.5" margins on either side and it's printing on 8.5" paper. Setting it wider would result in a warning message when the report was opened in preview mode.

Sample Reports

To demonstrate some of the features available on Access reports, I've included three sample reports in the demonstration database. For the rest of this chapter, I'll offer some details on how I created each of them.

Employee Profile

The Employee Profile is a report that provides an overall picture of the information stored on each employee. The query behind it is much like that of the main employee data form. It contains several linked tables, each with one-to-one relationships to provide the different types of data.

Custom Report Sections

In addition to the normal sections, this report also has a section titled "EmpKey Header". This section represents a report grouping based on the EmpKey field which uniquely identifies each employee. To see the group settings, choose Sorting and Grouping from the View menu in design view.

The Sorting and Grouping dialog in Access. With this, you can generate custom headers and footers for your report.

(Click on image to see this screen in Access 2007.)

The settings specify that a header section for this group will be shown but no footer. The report is grouped on each separate value which, in this case, means each employee.

Custom headers and footers have their own sets of properties just as the other reports sections do. If you right click on the EmpKey header bar and choose Properties, the Properties sheet will appear.

Custom report sections also have sets of properties that can be adjusted for the right result.

The relevant setting here is Force New Page which instructs the report that every time it needs to print a new EmpKey header, in other words for each new employee, it should start a new page.

Custom headers can also be created for other types of fields such as date ranges or currency values.

Automatic Report Features

Click on the image to view this screen as it appears in Access 2007.

At the bottom of each report page, you can see the page number and the current date. Since reports are often multiple page documents and are meant to be printed for reference and presentation, there are a couple of page elements that you can automatically insert to enhance the report.

Choosing the options from the Insert menu in design view will bring up dialogs which include the necessary formatting options and this makes it pretty simple to add these elements. (In Access 2007, these options are found under the Design section on the Ribbon bar.) Behind the scenes, the dialogs simply create new text boxes on the report where the Control Source contains text and codes that Access translates into the current date and time or the correct page numbers.

Generating page numbers and date stamps on reports is as simple as entering the right formula in the control source of a text box.

The screenshot above is the property sheet from the page count on the profile report. It simply uses the equals sign and then concatenates a text string using the [Page] and [Pages] values of the report which provide the current page and total number of pages respectively. Knowing this, you could customize the box as needed.

The date and time are the same except that Access uses the Date() and Time() functions to show the correct values.

The Format property is used to specify how dates will be displayed in the control.

In this case, Access also uses the Format property to determine how to display the values. Again, knowing how this textbox is made, you can customize it to your own needs. You could even enter something like this for the Control Source:

= "Generated on " & Format(Date(),"Long Date") & " at " & Format(Time(),"Medium Time")

I've color-coded the above to help you distinguish between the literal text and the functions. This would show up on the report like this:

"Generated on Sunday, February 03, 2008 at 10:46 AM"

Because I'm concatenating a string with both the date and time and extra words, I have to use the Format() function instead of relying on the Format property of the text box. The function accepts the Date() or Time() function as its first parameter and then I use a pre-defined format style for the second parameter, in this case "Long Date" which includes the weekday and the full spelling of the date and "Medium Time" which displays the time with the AM/PM indicator. For more information, search Access help for the Format() function as it applies to Date / Time formats.

Read the entire Microsoft Access for Beginners series offline ...

Microsoft Access for Beginners
Andrew Comeau

Expanded, updated for Office Access 2010 and available from Amazon.com.

Click for more information ...

Adding Subreports

The Detail section of the report is usually where you would put most of the data generated by the report query. On the profile report, I did things a little different since I wanted the employee information under the custom header section that I created. In the detail section, I placed two subreports; one for the employee skills and the other for the employee notes. Subreports act a lot like subforms. They are based on a table that is related to a table in the main report query and linked to the main report on the corresponding fields.

Subreports function a lot like subforms. Both use table relations to relate to their parent forms or reports.

In this case, the subRptSkills report uses a simple query on tblSkills and links to the main report through the EmpKey field. Because the detail section is contained within the EmpKey grouping on the profile report, the subreport limits itself to the records pertaining to the employee shown on the current page.

In the screenshot above, you'll see another property of the subreport that you need to be aware of. The Can Grow property enables the subreport to expand vertically to accommodate extra data. In design view, you'll notice that the two subreports are set to a minimum height but when you run the report for certain employees, you'll see that the content of the subreport takes up more room on the page. With the Can Grow property set to Yes, the Skills subreport will expand as much as needed and the Notes subreport will move down the page to accommodate it. The Notes subreport will also grow as needed, pushing any content after it down as necessary.

Printing Labels

The second report, rptMailingLabels is a minor exception to my rule against using design wizards in Access.

The final option on the New Report screen is the Label Wizard which actually does a good job of setting up a report to print labels on a range of predefined label templates including different Avery sizes. Microsoft Word and other software titles will also do this. The difference here is that you can fill the labels with data from your database.

To use this wizard in Access 2007, select a table or query in the database window that contains the data you want to use, click the Create menu and then select the Labels icon under the Reports section.

The label printing wizard is very useful for adjusting the page settings to precisely match a wide variety of label types.

For this report, I'm using Avery 5160 labels which are a good size for standard mailing labels. The report is based on the Mailing2 query which combines the address and employee name fields into single fields that can be used in printed addresses. It also limits the records to current employees.

As with other wizards, the Label Wizard performs some steps that you can easily do manually, such as placing fields on the report.

Again, some of the tasks that the wizard does are ones that you could easily do yourself once you're familiar with report construction. In the screen above, it's laying out the fields from the query on a sample label. The main benefit of using the wizard here is that it works with the page setup to set the width and height of the labels to the exact dimensions defined by the various label makers. This is something where you would otherwise spend a lot of time on trial and error.

In the design view of the label report, you can see how the fields from the query are setup in the detail section and the report width is adjusted to the exact size of the label. The header sections are available but are not used as this report is just printing the labels. There are no other unique features to this report but it is a good beginning example of how Access reports can be tailored to more than just straight reporting of data.

The Employee List

The final report that I've added is a simple listing of employees. Its unique feature is that, in addition to listing them alphabetically, it uses the custom header I described earlier to group the names as well and provide a bit of extra formatting.

In this report, the custom header is created on the EmpName field of the Mailing2 query which shows the last name first. The group settings specify a header but no footer and the group is defined on the first characters of the field's value. The Group Interval of 1 indicates that only the first character will be used. In other words, the first letter of the person's last name.

Custom formulas can be used on reports to provide additional formatting as well as presenting data as needed.

The new EmpName header section has a single textbox in it called HeaderLetter. The control source is set to a formula that pulls the first letter from the name it sees. The Left function shown here takes two parameters; a string value, in this case the field name, and the number of characters to grab. The function returns the first letter from the first of the last names it finds in the group.

The result is that when the report is previewed, each alphabetized section is preceded by the letter for that section. Since there are only a few records in the database, I've limited the report to one page. If there were a couple hundred employees, though, you could also use the Force New Page setting in the header properties to start a new page before each group. This might be helpful if the list was being made available as a printed directory.

Another feature of this report is that it's manually divided into two columns since the content itself is not enough to fill the width of an entire page. The Page Setup makes this this a simple task. You'll also notice that the page header and footer are not affected by these column settings. The footer has a date field at the right-hand edge of the report.

Reports can be set to show multiple columns so as to best arrange your data.

Managing the Interface

Users need some way of accessing new reports in the application, either from a button on one of the forms as I've done with the profile report or from a menu. I try to avoid having users work with the database window as much as possible. Giving them interface elements to work with provides a smoother, more professional experience and protects the collection of objects that your application depends on. How you make a report available in the interface depends on how it will be used.

Selecting the Data

New reports can be integrated into the user interface by adding command buttons to forms or new menus from which to select them.

Usually, an employee profile report would only be pulled up on one or two employees at a time so, for this report, I added a new button to the employee form that will display the report for the current employee whose information is showing on the form. However, this report normally shows data for all employees. If you open it from the database window, it will have a page for each employee in the database.

This means that when the user clicks the button on the employee form, I need a way to limit the information the profile report selects so that only the current employee will be shown. There are a couple of ways to do this, both of which involve changing the query behind the report so that it references a field from the employee form.

One way would be to change the query in the report design view so that the criteria for the Employee field contains a reference to the corresponding field on the employee form. This would filter the query on the Employee field in tblPersonnel so that only the record currently displayed on frmEmployeeMain is returned to the report.

When Access can't find values matching the criteria specified for a query, it will ask the user to input them.

The problem with doing this in report design view is that it limits the usefulness of the report. The report is always limited to one record and if you open it from any other form in the database or from the database window, you'll get an input box like the one shown here. Since the necessary field from frmEmployeeMain is not available for reference, the report asks for the value to use. Users would generally find this confusing.

The other way to limit the report data is to do it while opening the report. For this, I use the code behind the command button that calls the report.

Private Sub cmdProfile_Click()

DoCmd.OpenReport "rptEmpProfile", acViewPreview, , "tblPersonnel.Employee = [Forms]![frmEmployeeMain]![Employee]"

End Sub

You can learn more about using VBA code in the Programming Microsoft Access series but for now, here are the basics. The name cmdProfile_Click indicates that the code is run whenever the user clicks on the command button and the click event fires. The remaining code uses one of the most common commands in VBA; the DoCmd statement which can run a wide range of commands, each with their own set of parameters. In this case, it's running the OpenReport command. Here, it has four parameters:

Report Name - This is the name of the report as shown in the database window ("rptEmpProfile").

View - This is the mode in which the report will be opened. While entering the command, the VBA environment presents choices that include design view and sending the report directly to a printer. Here, I've set it to open in preview (acViewPreview).

Filter - The double comma in the code is where I've left this option blank since I'm not using it. If I was, it would be the name of a saved query that would be used to filter the data.

Where Condition - The final parameter is the actual filter that the report will use. When Access opens the report from the employee form, it dynamically places this statement on the query as an SQL WHERE clause.

"tblPersonnel.Employee = [Forms]![frmEmployeeMain]![Employee]"

This effectively changes the query behind the report but since this code only applies to this instance of the report, it does not limit the data when the report is opened from elsewhere in the database.

Custom Menus

Another way of making reports available to the user is by adding them to the customized menu bar or toolbar. The sample application has both and I've added these two reports to to PersonnelMenu, the custom menu bar I designed for all of my interface forms to use.

The Access 2007 Ribbon menu system can be customized as needed although the process is more involved than customizing the menus and toolbars in previous versions. For more information, search for "Customize the Ribbon" in the Access 2007 help.

By creating custom menus and toolbars, you can provide the user with an easy way to access the new reports and other resources in your application.

By right-clicking on the menu and selecting Customize, you can switch to customize mode and change the menu and toolbar items. In the screenshot above, you can see that all database objects are available for use as button and menu items. These can be added to custom menu bars just by dragging them onto the menu. If your application has a large number of reports, adding buttons to custom forms could get cumbersome after awhile and that's where it's better to use the menu bar. Another advantage is that the menu bar does not require any code to be written.

In addition to adding the reports to the menu, I've added the menu to the design of the reports. Reports have Menu Bar and Toolbar settings in their properties collections just as forms do. I've set these in each of the reports so that the interface maintains a consistent appearance when the reports are open. The Application Startup options also contains a setting for a custom menu bar that will show now matter what object in the database is open.

For more information about custom menus and toolbars, see the supplemental article in this series on Advanced Form Design.

Conclusion

I've shown you the basics of reporting in Access but I hope you can see that there are a lot more possibilities available to explore. Having such a rich built-in design environment is one of the great strengths of Microsoft Access. Other development tools require you to either use a separate reporting system or to design your own. I've even seen Access used solely for its reporting tools by other programs.

Reporting may be the aspect of your application that adds the most value for the users who will ultimately decide if the application succeeds or fails as a production tool. It can also be the most demanding part of application design as it requires the most input from the people who will be relying on the reports and that input can sometimes be detailed and conflicting. The more familiar you are with the reporting tools and their abilities, the more likely you are to create a successful application that will impress the users and have them coming back for more.