| home Java Script MS Access Perl HTML Delphi C ++ Visual Basic Java CGIPerl MS Excel Front Page 98 Windows 98 Ms Word Builder PHP Assembler Link to us Links |
The reporting engine of Microsoft Access is very powerful, with a wealth of features. Many types of reports are available in Access 95:
A Detail report provides an entry for each record included in the report. As you can see in Figure 7.1, an entry is found for each order in the Orders table within the period specified (1994). The detail within the report is grouped by country and within country by salesperson. The report provides subtotals by salesperson and country. The bottom of the report contains grand totals for all records included within the report. The report is based on a Parameter query that limits the data displayed on the report based on criteria supplied by the user at runtime.
Figure 7.1. An example of a Detail report.
A Summary report provides summary data for all the records included in the report. In Figure 7.2, only total sales by quarter and year are displayed in the report. The underlying detail records that compose the summary data are not displayed within the report. The report is based on a query that summarizes the net sales by OrderID. The report itself contains no controls in its Detail Section. All controls are placed in report Group Headers and Footers that are grouped on the quarter and year of the ship date. Because no controls are found in the Detail Section of the report, Access prints summary information only.
Figure 7.2. An example of a Summary report.
Cross-tabulation reports display summarized data grouped by one set of information on the left side of the report and by another set across the top of the report. The report shown in Figure 7.3 shows total sales by product name and employee. The report is based on a Crosstab query and is generated using a fair amount of VBA code. This code is required because each time the report is run a different number of employees might need to be displayed in the columns of the report. In other words, the number of columns needed might be different each time the report is run. This report and the techniques required to produce it are covered in Chapter 14, "Let's Get More Intimate With Reports."
Figure 7.3. An example of a Cross-Tabulation report.
Although the statement "A picture paints a thousand words" is cliché, it is also quite true. Research proves that people more successfully retain data displayed as pictures rather than numbers. Fortunately, Access makes the process of including graphics and charts in your reports quite easy. As shown in Figure 7.4, a report can be designed to include a combination of both numbers and charts. The report in Figure 7.4 shows the sales by product both as numbers and as a bar chart. The main report is grouped by product category. The report contains a subreport based on a query that summarizes sales by CategoryID, CategoryName, and ProductName for a specific date range. The chart totals product sales by product name, displaying the information graphically.
Figure 7.4. An example of a report containing a chart.
A report that emulates a printed form is a common need. The Access Report Builder, with its many graphical tools, enables you to quickly produce reports that emulate the most elegant of data-entry forms. The report shown in Figure 7.5 produces an invoice for a customer. The report is based on a query that draws information from the Customers, Orders, Order Details, Products, Employees, and Shippers tables. The report's Filter property is filled in, limiting the data that appears on the report to all records within a specific date range. The use of graphics, color, fonts, shading, and other special effects give the form a professional look and feel.
Figure 7.5. An example of a report containing a form.
The process of creating mailing labels in Access 95 is easy. Mailing labels are simply a special type of report with a page setup indicating the number of labels across the page and the size of each label. An example of a mailing label report is shown in Figure 7.6. This report is based on the Customers table but could have just as easily been based on a query that limits the mailing labels produced.
Figure 7.6. An example of a report containing mailing labels.
Reports can contain many parts. These parts are referred to as sections of the report. A new report is automatically made up of three sections (see Figure 7.7):
Figure 7.7. Sections of a report.
The Detail Section of a report is the main section of the report. It is the section of the report used to display the detailed data of the table or query underlying the report. Certain reports, such as Summary reports, contain nothing in the Detail Section. Instead, Summary reports contain data in Group Headers and Footers (discussed ahead).
The Page Header of a report is the portion of the report that automatically prints at the top of every page of the report. It often includes information such as the title of the report. The Page Footer automatically prints at the bottom of every page of the report. It often contains information such as the page number and date. Each report can contain only one Page Header and one Page Footer.
In addition to the three sections that are automatically added to every report, a report can contain the following sections:
A Report Header is a section of a report that prints once, at the beginning of the report. The Report Footer is a section that prints once, at the end of the report. Each Access report can contain only one Report Header and one Report Footer. The Report Header is often used to create a cover sheet for the report. It can contain graphics or other fancy effects, adding a professional look to a report containing important information. The most common use of the Report Footer is for grand totals. It can also include any other summary information for the report.
In addition to Report and Page Headers and Footers, an Access report can contain up to 10 Group Headers and Footers. Report groupings separate data logically and physically. A Group Header prints before the detail for the group, and the Group Footer prints after the detail for the group. For example, you can group customer sales by country and city, printing the name of the country each time the country changes and the name of the city each time the city changes. You can total the sales for each country and city. The country and city names are placed in the country and city Group Headers. The totals are placed in the country and city Group Footers.
You can create a new report in several ways. The most common way is to select the Reports tab of the Database window and click New. The New Report dialog appears (see Figure 7.8). This dialog allows you to select from the many options available for creating reports. Reports can be created from scratch using Design view. They can also be created with the help of five wizards. Three of the wizards help you build standard reports, one helps you build reports containing charts, and the last wizard automates the process of creating mailing labels. The Report Wizards are so powerful that I use one of them to build the initial foundation for almost every report I create.
Figure 7.8. The New Report dialog enables you to designate Design View or select from one of five wizards.
To create a report using the Report Wizard, select Report Wizard from the New Report dialog and click OK. The Report Wizard is launched. The first step of the Report Wizard prompts you for the table or query that will supply data to the report. It is best to base your reports on queries. This improves performance and enhances your ability to produce reports based on varying criteria.
After you have selected a table or query, you can select the fields that you want to include on the report. The fields included in the selected table or query are listed in the list box on the left. To add fields to the report, double-click on the name of the field you want to add or click on the field name and click >. In the example in Figure 7.9, six fields have been selected from the tblClients table.
Figure 7.9. The first step of the Report Wizard: table/field selection.
After you have selected a table or query and the fields you want to include on the report, click Next. You are prompted to add group levels to the report. Group levels add report groupings to the report. Add group levels if you need to visually separate groups of data or include summary calculations (subtotals) in your report. Report groupings are covered later in this chapter. If your report does not require groupings, click Next.
The third step of the Report Wizard prompts you for sorting levels for your report. Because the order of a query underlying a report is completely ignored by the report builder when the report is run, it is necessary that you designate a sort order for the report. You can add up to four sorting levels using the wizard. In the example shown in Figure 7.10, the report is sorted by the ClientID field. After you select the fields you want to sort on, click Next.
Figure 7.10. Third step of the Report Wizard: sorting report data.
The fourth step of the Report Wizard prompts you for the layout and orientation of the report. The layout can be Vertical or Tabular. Tabular reports are the most common. Tabular reports display report data in columns going across the report. Vertical reports display one record at a time, with each of the fields displayed one beneath the other down the report. The orientation can be Portrait or Landscape. This step of the Report Wizard also allows you to specify whether you want Access to attempt to adjust the width of each field so that all the fields fit on each page. After supplying Access with the required information, click Next.
The fifth step of the Report Wizard enables you to select a style for your report. The available choices are Bold, Casual, Compact, Corporate, Formal, and Soft Gray. Access allows you to preview each look before you make a decision. Any of the style attributes applied by the Report Wizard, as well as any other attributes of the report defined by the wizard, can be modified in Report Design view at any time after the wizard has produced the report. After you have selected a style, click Next.
The final step of the Report Wizard prompts you for a title for the report. This title will be used as the name and the caption title for the report. I provide a standard Access report name and modify the caption after the Report Wizard has completed its process. You are given the opportunity to preview the report or modify the report's design. If you opt to modify the report's design, you are placed in Design view (see Figure 7.11). The report can then be previewed at any time.
Figure 7.11. Design view of a completed report.
Another way to start the Report Wizard is to click on the Tables or Queries tab and then click on the table or query on which you want the report to be based. Use the New Object drop-down on the toolbar to select New Report. The New Report dialog appears. Select Report Wizard. You do not need to use the Tables/Queries drop-down to select a table or query. The table or query that you selected prior to invoking the wizard is automatically selected for you.
Although you will usually get started with most of your reports using a Report Wizard, you should understand how to create a new report from Design view. To create a report without the use of a wizard, click the Reports tab and then click New. The New Report dialog appears. Click Design View and then use the drop-down to select the table or query on which the report will be based. Click OK. The Report Design window appears.
The Report Design window is used to build and modify a report. Using this window, you can add objects to a report and modify their properties. Microsoft provides numerous Report, Report Grouping, and Control properties. By modifying these properties, you can create reports with diverse looks and functionality.
Several report design tools are available to assist you with the report design process. They include the Properties, Toolbox, Field List, and Sorting and Grouping windows. Two toolbars are also designed to facilitate the development and customization of your reports. They are the Report Design toolbar and the Formatting toolbar. The Report Design toolbar provides tools that enable you to save, preview, and print your report as well as cut, copy, and paste report objects. The Formatting toolbar is specifically designed to help you customize the look and feel of your report. It includes tools that enable you to change the font, font size, alignment, color, shading, and other physical attributes of the report objects.
The Properties, Toolbox, Field List, and Sorting and Grouping windows are all designed as toggles. This means that buttons on the Report Design toolbar alternately hide and show these valuable windows. If you have a high-resolution or large monitor, you might want to leave the windows open at all times. If you have a low-resolution or standard-sized monitor, you need to get a feel for when it is most effective for each window to be opened or closed.
Fields can most easily be added to a report using the Field List window. With the Field List window open, click and drag a field from the field list onto the appropriate section of the report. Just as with forms, multiple fields can be added at one time. Use the Ctrl key to select noncontiguous fields or the Shift key to select contiguous fields, and then click and drag the fields to the report as a unit.
One problem with adding fields to a report is that both the fields and the attached labels are placed in the same section of the report. This means that if you click and drag fields from the Field List window to the Detail Section of the report, both the fields and the attached labels will appear in the report. If you are creating a tabular report, this is not acceptable. You need to cut the attached labels and paste them within the Page Header section of the report.
Microsoft offers several techniques to help you select, move, align, and size report objects. Different techniques are effective in different situations. Experience will tell you which technique you should use in a certain situation. The process of selecting, moving, aligning, and sizing report objects is quite similar to performing the same tasks with form objects. The techniques are covered briefly in this chapter. For a more detailed explanation of each technique, refer to Chapter 6, "What Every Developer Needs to Know About Form Basics."
To select a single report object, click on the object you want to select. Selection handles appear around the selected object. When the object is selected, you can modify any of its attributes (properties), or you can size, move, or align the object.
To select multiple objects so that you can manipulate them as a unit, use one of the following techniques:
It is important to understand which objects are actually selected. The area of confusion involves attached labels. Figure 7.12 shows a report with four objects selected: the rptClients label, the Contact First Name label, the City text box, and the ContactFirstName text box. The City label is not selected. It is surrounded by one selection handle because it is attached to the City text box. If you were to modify the properties of the selected objects, the City label would be unaffected.
Figure 7.12. Selecting objects in an Access report.
If you want to move a single control along with its attached label, click on the object and drag it to a new location. The object and the attached label move as a unit. To move multiple objects, use one of the methods covered in the previous section to select the objects you want to move. After the objects are selected, click and drag any of them. The selected objects and their attached labels move as a unit.
Moving an object without its attached label is a trickier process. When placed over the center or border of a selected object (not on a sizing handle), the mouse pointer appears as a hand with all five fingers pointing upward. When the mouse pointer appears as a hand, the selected object and its attached label move as a unit, maintaining their relationship to one another. If you place your mouse pointer directly over the selection handle that appears in the upper left corner of the object, the mouse pointer appears as a hand with the index finger pointing upward. If you click and drag with the mouse pointer in this format, the object and the attached label move independently of one another so that you can alter the distance between them.
To align objects to one another, select the objects you want to align. Select Format|Align and then select Left, Right, Top, Bottom, or To Grid. The selected objects align with each other.
You need to be aware of a few "gotchas" when aligning report objects. If you select several text boxes and their attached labels and attempt to align them, Access attempts to align the left sides of the text boxes with the left sides of the labels. To avoid this problem, you need to align the text boxes separately from their attached labels.
The Snap to Grid feature is a toggle. It is found under the Format menu. When Snap to Grid is selected, all objects that you are moving or sizing snap to the grid lines of the report. To temporarily disable the Snap to Grid feature, hold down your Ctrl key while sizing or moving an object.
Access offers many techniques to help you size report objects. A selected object has seven sizing handles. All the handles that surround an object, except for the upper leftmost handle, can be used to size the object. Simply click and drag one of the sizing handles. If multiple objects are selected, they are all sized by the same amount.
The Format|Size menu also can help you size objects. It contains the six options: To Fit, To Grid, To Tallest, To Shortest, To Widest, and To Narrowest. These options are covered in detail in Chapter 6.
Access offers a great trick that can help to size an object to fit. Simply double-click on any sizing handle. The object is automatically sized to fit the text within it.
Access also makes it easy for you to control object spacing. Both the horizontal and vertical distances between selected objects can be made equal. With the objects you want to affect selected, click Format|Horizontal Spacing|Make Equal or Format|Vertical Spacing|Make Equal. You can also maintain the relative relationship between selected objects while increasing or decreasing the space between them. To do this, select Format|Horizontal/Vertical Spacing|Increase/Decrease.
Reports usually contain mostly labels, text boxes, lines, rectangles, and bound and unbound object frames. The other controls are generally used for reports that emulate data-entry forms. The various controls that can be placed on a report as well as their uses are discussed briefly in this section.
Labels are used to display information to your users. They are commonly used as report headings, column headings, or group headings for your report. Although the text they display can be modified at runtime using VBA code, they cannot be directly bound to data.
To add a label to a report, select the Label tool in the toolbox. Then click and drag to place the label on the report.
Text boxes are used to display field information or the result of an expression. They are used throughout the various sections of a report. For example, in a Page Header, a text box might contain an expression showing the date range that is the criteria for the report. In a Group Header, a text box might be used to display a heading for the group. The possibilities are endless because a text box can contain any valid expression.
To add a text box to a report, select the Text Box tool from the toolbox. Click and drag the text box to place it on the report. A text box can also be added to a report by dragging a field from the field list to a report. This works as long as the Display control of the field is a text box.
Lines can be used to visually separate objects on your report. For example, a line can be placed at the bottom of a section or underneath a subtotal. To add a line to a report, click the Line tool to select it and then click and drag to place the line on your report. Whe it is added, the line has several properties that can be modified to customize its look.
To ensure that the line you draw is perfectly straight, hold down the Shift key while you click and drag to draw the line.
Rectangles can be used to visually group items on the report that logically belong together. They can also be used to make certain controls on your report stand out. I often draw rectangles around important subtotal or grand total information that I want to make sure the reader of the report will notice.
To add a rectangle to a report, select the Rectangle tool from the toolbox. Click and drag to place the rectangle on the report.
The rectangle might obscure objects that have already been added to the report. To rectify this problem, the Back Style property of the rectangle can be set to Transparent. This setting is fine unless you want the rectangle to have a background color. If you want the rectangle to have a background color, select Format|Send to Back to layer the objects so that the rectangle lies behind the other objects on the report.
Bound object frames enable you to display the data contained within OLE fields. OLE fields can contain objects from other applications. Examples include pictures, spreadsheets, and word-processing documents.
To add a bound object frame to a report, click the Bound Object Frame tool in the toolbox and then click and drag the frame on the report. Set the Record Source property of the frame to the appropriate field. You can also add a bound object frame to a report by dragging and dropping an OLE field from the field list onto the report.
Unbound object frames are generally used to add logos and other pictures to a report. They are different from bound object frames in that they are not tied to underlying data.
To add an unbound object frame to a report, click the Unbound Object Frame tool in the toolbox. Click and drag the object frame to place it on the report. The Insert Object dialog appears (see Figure 7.13). This dialog box enables you to create a new OLE object or insert an existing OLE object from a file on disk. If you click on Create From File, the Insert Object dialog box changes to look like Figure 7.14. Click Browse and locate the file you want to include in the report. The Insert Object dialog gives you the option of linking to or embedding an OLE object. If you select Link, a reference is created to the OLE object. Only the bitmap of the object is stored in the report. The report continues to reference the original file on disk. If you do not select Link, the object you select is copied and embedded in the report and becomes part of the Access MDB file. No link to the original object is maintained. Figure 7.15 shows a report with an unbound object frame.
Figure 7.13. The Insert Object dialog enables you to insert a new or existing object into an unbound object frame.
Figure 7.14. The Insert Object dialog with Create From File selected.
Figure 7.15. A report with an unbound object frame.
It is generally preferable to use an image control rather than an unbound object frame for static information such as a logo. This is because the image control requires much less resources than an unbound object frame.
As mentioned earlier in this section, it is standard to include mostly labels and text boxes on your reports. Other controls can be added when appropriate. To add any other type of control, click to select the control and then click and drag to place it on the report.
Reports have many different properties. These properties can be modified to change the look and feel of the report. Like Form properties, Report properties are divided into the categories Format, Data, Event, and Other. To view a report's properties, you must first select the report. You can select the report in a few ways:
When a report has been selected, you can view and modify its properties.
When the report is selected, the Properties window shows all the properties associated with the report. To select the report and open the Properties window at the same time, double-click on the Report Selector. A report has 35 properties broken down into the appropriate categories within the Properties window. Twenty-eight of the properties relate to the format, data, and other special properties of the report. The remaining seven properties relate to the events that occur when a report is run. The format, data, and other properties are covered here. The event properties are covered in Chapter 14, "Let's Get More Intimate With Reports: Advanced Techniques."
A report has 15 Format properties. These properties allow you to change the physical appearance of the report.
A report has five Data properties. The Data properties of the report allow you to supply information about the data underlying the report.
A report has eight Other properties. These miscellaneous properties enable you to control other important aspects of the report.
Just as reports have properties, so do controls. Most control properties can be changed at design time or at runtime, allowing you to easily build flexibility into your reports. For example, certain controls can be visible only when specific conditions are True.
You can modify several formatting properties of the selected objects using the formatting toolbar. If you prefer, you can set all the properties in the Properties window.
The Data properties of a control are used to specify information about the data underlying a specific report control.
The Other Properties of a control are used to designate properties of a control that do not fit into any other category.
A common mistake that many developers make is to give controls names that conflict with Access names. This type of error is very elusive and difficult to track down. Make sure that you use distinctive names for both fields and controls. Furthermore, do not give a control the same name as the name of a field within its expression. For example, the expression =ClientName & Title should not be given the name ClientName. This results in a #error# when the report is run. Following these simple warnings will spare you a lot of grief!
Page breaks can be set to occur before, within, or at the end of a section. The way that you set each type of page break is quite different. To set a page break within a section, you must use the Page Break tool in the toolbox. Click the Design tool in the toolbox and then click the report where you want the page break to occur. To set a page break before or after a section, set the Force New Page property of the section to Yes. The Force New Page property applies to Group Headers, Group Footers, and the Detail Section of the report.
Be careful not to place a page break within a control on the report. The page break occurs in the middle of the data for the control.
Three types of controls can be placed on a report: Unbound, Bound, and Calculated. Unbound controls are controls that are not tied to data. An example is a logo placed on a report. Bound controls are tied to data within a field of the table or query underlying the report. Calculated controls contain valid expressions. They can contain anything from a page number to a sophisticated financial calculation. Most complex reports contain a rich combination of Bound, Unbound, and Calculated controls.
Calculated controls are made up of expressions. The expression is the Calculated control's control source. To create a Calculated control, you must first add an Unbound control to the report. Expressions must be preceeded with an equal (=) sign. You can build the expression by typing it directly into the control source or by using the Expression Builder. The Expression Builder is covered in Chapter 6. An example of a report expression is =Sum([BillableHours]).
This expression, if placed in the Report Footer, totals the contents of the BillableHours control for all detail records in the report.
The majority of reports you create will probably be based on more than one table, because a properly normalized database usually requires that you bring table data back together to provide your users with valuable information. An example would be a report that combines information from a Customers table, an Orders table, and an Order Details table. This report can provide the following information:
A multitable report can be based directly on the tables whose data it displays, or it can be based on a query that has already joined the tables, providing a flat table structure.
You can create One-to-Many reports in several ways. You can employ the use of a Report Wizard, or you can build the report from scratch. Different situations warrant different techniques. Some of the various techniques are covered in the following sections.
Building a One-to-Many Report with the Report Wizard is quite easy:
Figure 7.16. Step 2 of the Report Wizard: selecting a layout.
Figure 7.17. Step 3 of the Report Wizard: selecting groupings.
Figure 7.18. Step 4 of the Report Wizard: selecting a sort order.
Figure 7.19. Adding summary calculations.
The completed report created in the previous example appears in Figure 7.20. Notice that the report is sorted and grouped by City and CompanyName. The data within the report is in order by OrderDate within a CompanyName grouping.
Figure 7.20. A completed One-to-Many report.
This method of creating a One-to-Many report is by far the easiest. In fact, the background join technology that the wizards use when they allow you to pick fields from multiple tables, figuring out how to build the complex queries needed for the report or form, is one of the major enhancements in Access for Windows 95. It is a huge timesaver and helps to hide unnecessary complexity from you as you build a report. Although you should take advantage of this fabulous feature, it is important that you, as a developer, are cognizant of what is occurring under the covers. The following two sections provide you with this necessary knowledge.
Another popular method of building a One-to-Many report is from a One-to-Many query. A One-to-Many report built in this way is constructed as if it were based on the data within a single table. First, you build the query that will underlie the report (see Figure 7.21).
Figure 7.21. An example of query underlying a One-to-Many report.
When you have completed the query, you can select it rather than selecting each individual table (as was done in the previous section). After the query is selected, the process of creating the report is identical to that of creating the previous report.
A One-to-Many report can also be created by building the parent report and then adding a Subform/Subreport control. This is often the method used to create reports such as invoices that show the data within the report in a One-to-Many relationship rather than in a denormalized format (seen in Figure 7.20). If you want to utilize the Subform/Subreport Wizard, you must make sure that the Control Wizards tool is selected before you add the Subform/Subreport control to the main report. Here is the process:
Figure 7.22. The Subform/Subreport Wizard: identifying the relationship.
As you can see in Figure 7.23, the One-to-Many relationship between two tables is clearly highlighted by this type of report. In the example, each customer is listed. All the detail records reflecting the orders for each customer are listed immediately following each customer's data.
Figure 7.23. A completed One-to-Many report created with the Subform/Subreport Wizard.
When a Subreport has been added to a report, it is important to understand what properties have been set by the Subform/Subreport Wizard so that you can modify the Subreport control, if needed. You should become familiar with a few properties of a subreport:
Source Object: The name of the report that is being displayed within the control.
Link Child Fields: The fields from the Child report that link the Child Report to the Master report.
Link Master Fields: The fields from the Master report that link the Master report to the Child report.
Can Grow: Determines whether the control can expand vertically to accommodate data contained within the subreport.
Can Shrink: Determines whether the control can shrink to eliminate blank lines when no data is found within the subreport.
Not only is it important that you know how to work with the properties of a Subreport object, but it is also important that you are able to easily modify the subreport from within the main report. You can always modify the subreport from the Reports tab of the Database window. To do this, click on the report you want to modify, and then click Design. Here is a much easier way to modify a subreport from within the main report:
Unlike within forms, the sorting of the data within a report is not determined by the underlying query. In fact, the underlying query has absolutely no effect on the sort order of the report. Any sort order specified in the query is completely overwritten by the sort order of the report. The sort order of the report is determined by the Sorting and Grouping window of the report (see Figure 7.24). The sorting and grouping of the report is affected by what options you select when you run a Report Wizard. The Sorting and Grouping window can then be used to add, remove, or modify sorting and grouping options for the report. Sorting simply affects the order of the records on the report. Grouping adds Group Headers and Footers to the report.
Figure 7.24. The Sorting and Grouping window showing grouping by city and company name and sorting by order date and product name.
Often you will want to add sorting or grouping to a report. To do so, follow four steps:
Figure 7.25. Inserting a sorting or grouping.
To remove a sorting or grouping that has been added, click to select the field within the Sorting and Grouping window. Then press the Delete key. You are warned that any controls within the Group Header or Footer will be lost.
Each grouping within a report has properties that define the attributes of the group. Each group contains five properties (see Figure 7.26). These properties determine whether the field is used as a sorting, a grouping, or both. They also are used to specify details about the grouping options. Let's go over the Sorting and Grouping properties:
Figure 7.26. Sorting and Grouping properties showing the five sorting and grouping properties.
If you have set Keep Together to Whole Group and the group is too large to fit on a page, Access ignores the property setting. Furthermore, if you set Keep Together With First Detail and either the group header or detail record is too large to fit on one page, the setting is also ignored.
Each Group Header and Footer has its own properties. These properties are used to determine the behavior of the Group Header or Footer, and they are discussed in this section.
It is usually best to base your Access reports on queries. This option affords you several benefits:
Reports based on stored queries open more quickly than reports based on embedded SQL statements because when you build and save a query, it compiles and creates a Query Plan. This Query Plan is a plan of execution that is based on the amount of data in the tables involved in the query as well as all of the indexes available in each table. If you run a report based on an embedded SQL statement, the query is compiled, and the Query Plan is built at runtime, slowing the execution of the query.
The one situation in which basing a report on an embedded query might be preferable is when the volume of data, or the available indexes, might vary each time the report is run. In this specific case, you might get a more optimized query if the query is compiled, and the query plan is built, at runtime.
Often you will want to build several reports and forms all based on the same information. An embedded SQL statement cannot be shared by multiple database objects. At the very least, you need to copy the embedded SQL statement for each form and report you build. Basing reports and forms on stored queries eliminates this problem. You build the query once and modify it once if changes need to be made to it. Many forms and reports can utilize the same query (including its criteria, expressions, and so on).
Reports often contain complex expressions. If a particular expression is used in only one report, nothing is lost by building the expression into the embedded SQL statement. On the other hand, many complex expressions are used in multiple reports and forms. By building these expressions into queries on which the reports and forms are based, you need to create the expression only one time.
It is easy to save an embedded SQL statement as a query. This allows you to use the Report Wizard to build a report involving multiple tables. You can then save the resulting SQL statement as a query. With the report open in Design view, bring up the Properties window. Click the Data tab. Click in the Record Source property and then click the ellipse. The embedded SQL statement appears as a query. Select File Save As/Export. Enter a name for the query and click OK. Close the Query window, indicating that you want to update the Record Source property. Your query is now based on a stored query instead of an embedded SQL statement.
The time and billing application requires several reports. These reports will be designed throughout the book. A couple of the simpler ones are built in this section.
The rptClientListing report will contain a list of all the clients found within the tblClients table. The report will include the company name, contact name, city, state, ZIP code, and office phone of each customer and will be sorted by company name.
The rptClientListing report will be based on a query called qryClientListing, which appears in Figure 7.27. The query includes the CompanyName, City, State, OfficePhone, and Fax fields. It also includes an expression called ContactName that concatenates the ContactFirstName and ContactLastName fields. The expression looks like this:
ContactName: [ContactFirstName] & " " & [ContactLastName]
Figure 7.27. qryClientListing: foundation for rptClientListing report.
To build the report, follow these nine steps:
Figure 7.28. Selecting the qryClientListing query.
Figure 7.29. Selecting CompanyName as the sort order.
Figure 7.30. Preview of the completed report.
Figure 7.31. Changing the report caption.
The rptTimeSheet report is significantly more complex than the rptClientListing report. It includes two subreports: rsubTimeSheet and rsubTimeSheetExpenses.
The rptTimeSheet report appears in Figure 7.32. It is based on a query called qryTimeSheet, which appears in Figure 7.33. It contains fields from both tblClients and tblEmployees.
Figure 7.32. rptTimeSheet in Design view.
Figure 7.33. qryTimeSheet in Design view.
The rptTimeSheet report contains a Report Header that includes the title of the report. Nothing is found within the Page Header. The TimeCardID header contains the EmployeeName and DataEntered from the qryTimeSheet query. The Detail Section of the report contains the two subreports rsubTimeSheet and rsubTimeSheetExpenses. The Page Footer contains two expressions, one for the date and another for the page number. They look like this:
=Now() ="Page " & [Page] & " of " & [Pages]
The rsubTimeSheet report is based on qrySubTimeSheet. This query contains the following fields from the tblProjects and tblTimeCardHours tables:
tblProjects: ProjectName
tblTimeCardsHours: TimeCardID, TimeCardDetailID, DateWorked, WorkDescription, BillableHours, BillingRate, and an expression HourlyBillings: [BillingRate]*[BillableHours]
The design of rsubTimeSheet appears in Figure 7.34. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardDetailID from qrySubTimeSheets. View the data by tblTimeCardHours. Do not add any groupings, and do not sort the report. When you are done with the wizard, modify the design of the report. Remove the caption from the Report Header. Move everything from the Page Header to the Report Header. Collapse the Page Header. Remove everything from the Page Footer. Add a Report Footer with the expression =Sum([HourlyBillings]).
Figure 7.34. rsubTimeSheets in Design view.
Change the format of the HourlyBillings and the TotalHourlyBillings controls to Currency. Use the Sorting and Grouping window to sort by TimeCardID and TimeCardDetailID.
The rsubTimeSheetExpense report is based on qrySubTimeSheetExpense. This query contains the following fields from the tblProjects, tblExpenseCodes, tblTimeCardExpenses tables:
tblProjects: ProjectName
tblTimeCardsExpenses: ExpenseDate, ExpenseDescription, and ExpenseAmount
tblExpenseCodes: ExpenseCode
The design of rsubTimeSheetExpenses appears in Figure 7.35. This subreport can easily be built from a wizard. Select all fields except TimeCardID and TimeCardExpenseID from qrySubTimeSheetExpense. View the data by tblTimeCardExpenses. Do not add any groupings, and do not sort the report. When you are done with the wizard, modify the design of the report. Remove the caption from the Report Header. Move everything from the Page Header to the Report Header. Collapse the Page Header. Remove everything from the Page Footer. Add a Report Footer with the expression =Sum([ExpenseAmount]).
Figure 7.35. rsubTimeSheetExpenses in Design view.
Change the format of the ExpenseAmount and the TotalExpenseAmount controls to Currency. Use the Sorting and Grouping window to sort by TimeCardID and TimeCardExpenseID.
Reports provide you with valuable information about the data stored within your database. Many types of reports can be built in Access 95, including Detail reports, Summary reports, reports that look like printed forms, and reports containing graphs and other objects. Access offers many properties that enable you to customize the look and feel of each report to fit the needs of your users. Understanding how to work with each property is integral to the success of your application-development projects.
