.jpg)
Microsoft Access for Beginners - Part V
Generating the Reports
Contents
Introduction
From Forms to Reports
Creating a Report
Report Margins
Sample Reports
Employee Profile
Adding Subreports
Automatic Report Features
Custom Report Sections
Mailing Labels
Employee List
Managing the Interface
Selecting the Data
Custom Menus
Conclusion
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 (Requires Access and Excel 2000 or above)
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 practice, reports are generally 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.
New reports are automatically divided into three main sections that can contain data:
- 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.
Report Margins
While in design view, you can select Page Setup from the File menu 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.
 |
 |
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 paper 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 feature of the design environment that is useful on reports are the ruler guides that show 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.
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 one or two page report that provides an overall picture of the information stored on each employee. The query behind it is much like the query behind 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 settings at the bottom of the screen 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.
The relevant setting here is Force New Page which instructs the report that every time it needs to print a new EmpKey header it should start a new page, in other words for each new employee, it needs to 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
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. 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.
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.
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.
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.
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.
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.
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 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 currently used. 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 straight 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.
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 the field name and the number of characters to grab as parameters. In this case, it's the EmpName field which returns the first employee name in the group and the function looks at the first character on the left.
The result is that when the report is previewed, each alphabetized section is preceded by a letter. 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. You can see in the Page Setup that this is not a difficult 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.
Managing the Interface
Users need some way of accessing new reports in the application, either from a button on one of the forms as 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
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 that is provided to the profile report 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 with the value showing in the corresponding field on frmEmployeeMain is returned to the report.
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 the database window instead of frmEmployeeMain and the employee form is not open at the time, you'll get an input box like this. The form field is not available for reference so the report is asking 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
The name cmdProfile_Click indicates that the code is run whenever the user clicks on the command button and the click event fires. It 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 the database window.
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.
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 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 whenever the application is open.
Conclusion
I've shown you the basics of reporting in Access but I hope you can see that there's a lot more possibilities available to explore. Having such a rich built-in design environment is one of the great strengths of the program. Other development tools such as Visual Basic require you to either use a separate reporting system or to design your own. I've even seen Access used solely for its a reporting tools by external programs designed in Visual Basic.
Reporting may be the final step in the design of your application but also may be the one 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.