لطفا منتظر باشید ...
Building Reports Based on More Than One Table
The majority of reports you create will probably be based on data from more than one table. This is because a properly normalized database usually requires that you bring table data back together to give your users valuable information. For example, a report that combines data from a Customers table, an Orders table, an Order Details table, and a Product table can supply the following information:
- Customer information :
company name and address - Order information :
order date and shipping method - Order detail information :
quantity ordered and price - Product table :
product description
You can base a multitable report directly on the tables whose data it displays, or on a query that has already joined the tables, providing a flat table structure.
Creating One-to-Many Reports
You can create one-to-many reports by using a Report Wizard, or you can build the report from scratch. Different situations require different techniques, some of which are covered in the following sections.
Building a One-to-Many Report with the Report Wizard
Building a one-to-many report with the Report Wizard is quite easy; just follow these steps:
Figure 6.16. Step 2 of the Report Wizard: selecting a layout.
Figure 6.17. Step 3 of the Report Wizard: selecting groupings.
Figure 6.18. Step 4 of the Report Wizard: selecting a sort order.
Figure 6.19. Adding summary calculations.
The report created in the previous example is shown in Figure 6.20. Notice that the report is sorted and grouped by City and CompanyName. The report's data is in order by OrderDate and ProductName within a CompanyName grouping.
Figure 6.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 tablesfiguring out how to build the complex queries needed for the report or formwas one of the major enhancements in Access 95. It's a huge timesaver and helps hide unnecessary complexity from you as you build a report. Although you should take advantage of this feature, it's important that, as a developer, you know what's happening under the covers. The following two sections give you this necessary knowledge.
Building a Report Based on a One-To-Many Query
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 though it were based on the data within a single table. First, you build the query that will underlie the report (see Figure 6.21).
Figure 6.21. An example of a query underlying a one-to-many report.
When you have finished the query, you can select it rather than select each individual table (as done in the previous section). After you select the query, creating the report is the same process as the one used for the previous report.
Building a One-to-Many Report with the Subreport Wizard
You can also create a one-to-many report 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 report's data in a one-to-many relationship rather than in a denormalized format (as shown in Figure 6.20). If you want to use the SubForm/SubReport Wizard, you must make sure that you select the Control Wizards tool before you add the SubForm/SubReport control to the main report. Here is the process:NOTEIf you did not do a complete install of Microsoft Access, the SubReport wizard might not be available to you. To run these examples, you must first install the SubReport wizard.
Figure 6.22. The SubForm/SubReport Wizard: identifying the relationship.
As you can see in Figure 6.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 6.23. A completed one-to-many report created with the SubForm/SubReport Wizard.
Working with Subreports
When a subreport has been added to a report, it's important to understand what properties have been set by the 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 or other object that's 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 in the subreport. - Can Shrink
Determines whether the control can shrink to eliminate blank lines when no data is found in the subreport.
Not only should you know how to work with the properties of a SubReport object, but you should also be able to easily modify the subreport from within the main report. You can always modify the subreport by selecting it within the list of reports in the Database window. To do this, click the report you want to modify; then click Design. You can also modify a subreport by selecting its objects directly within the parent report.TIPAccess 2002 and Access 2003 make it easier to work with subforms and subreports in Design view. Scrolling has been improved so that it's easier to design subforms and subreports. In addition, you can now open subreports in their own separate Design view window by right-clicking the subreport and selecting Subreport in New Window. Alternately, instead of right-clicking the subreport, you can select the subreport and then click View, Subreport in New Window from the menu bar.
