- Learning How to Create Reports
- The Asia Database
- Begin the Report by Creating a Query
- Beginning a Report in Design View
- Detail Section
- Page Header and Footer
- Using Concatenation
- Report Header and Footer
- Creating a Report Using the Report Wizard
- Comparing the From-Scratch and From-Wizard Reports
- Conclusion
- Case Example
This chapter is from the book
This chapter is from the book
This chapter is from the book
Creating a Report Using the Report Wizard
Whew! If you've built all of rptAdministration from scratch, you can see why even Access pros like to use the Report Wizard, at least to get a running headstart. The wizard can create the same report (or one very similar to it) that took much work by hand, with just a few clicks of the mouse. But because our purpose is pedagogical, we'll go slowly through it and take a few detours so you can understand how the wizard works and what it offers.
Collecting Data
Because the Report Wizard works so seamlessly, it's difficult to see that you're accomplishing essentially two different tasks. First, you create a query; second, using the query as a record source, you build a report.
So let's begin to unveil the wizard's workings by taking a look at the Simple Query Wizard. This tool can be used to create single-table or multitable queries. I haven't talked about it much because it's a training-wheels tool that doesn't train you, and it won't save you much time in query making. But it's useful for understanding what the Report Wizard is up to.
In the Asia database, click the Queries button in the Database window. Double-click Create Query By Using Wizard. Open the Tables/Queries drop-down list. In this dialog box, you can choose fields from any tables or query to include in your query. Click Cancel.
Click the Reports button in the Database window, and double-click Create Report By Using the Wizard. Hmm, look familiar?
The point is that, however you create a report, you start by gathering data. Usually, that means creating a query. If you don't currently have a query for your report, you can use the wizard to assemble your data from different tables.
Assemble Your Data
You want a report with the same information as the one you built from scratch, so you could just select the query you created earlier in the chapter. But let's start from scratch and get the data you need from the various tables.
- With the first dialog box of the Report Wizard open, open the Tables/Queries drop-down list and select tblCountries.
- Select the Country field and click > to add it to the selected fields. Do the same for Area and Population.
- Open the Tables/Queries drop-down list and choose tblGovernment. Add GovType to the Selected Fields pane.
- Open the Tables/Queries drop-down list and choose tblHistory. Add the HistRule field to Selected Fields.
- Click Next to go to the next dialog box.
Create the Groups
In this dialog box, Access selects a group for you, depending on the relationships in the underlying tables. Because you can customize groups the way you want them in the next dialog box, you can ignore this dialog box and click Next.
The main group you want is government type. Select GovtType in the available fields and click >. A hierarchy is set up with GovtType at the top level and all other fields at a second level.
Nesting Groups
In the report you created in Design view, there was just one grouping level: government type. But you can also nest groups—you can have groups within groups within groups. Why would you want to do that?
Let's say you work for an international consumer company, and you're creating a sales report for the vice president of sales. He wants to see a breakdown of sales by country, and within each country by region, and within each region by sales rep.
In this case, you would create three groups. The top level would be country data. Nested within the country group would be regional data. And nested within the region would be sales rep data.
You can nest groups within the Asia database as well. Suppose that within each government group you want to add another level for the historical rule. In that case, you'd have the first group—say, Communist—and within the group, there would be additional groups for each historical rule (British, French, and so on). Then you'd have the second group, Constitutional Monarchy, and within this group, there would again be groups for each historical rule. (I included in your solution database AsiaChap10End.mdb an example of such a report. Its title is rptAdministrationTwoGroups, and you can view it at your leisure.)
- At the left of the dialog box, select HistRule and click the right arrow.
The report is now grouped first by government type and then by historical rule (see Figure 10.17). The HistRule group is selected in the scheme.
Figure 10.17 Two grouping levels in the Report Wizard.
- In the dialog box, click the up arrow above Priority. Now your report is grouped first by historical rule and then by government type.
- With HistRule selected, click < so your report is grouped only by government type.
- Click Next to go to the next dialog box.
)
Sort the Records
Within each group, you can choose additional field sorts. Within each government type group, let's sort the records by country. Open the drop-down list and select Country.
Use an ascending sort to keep them alphabetized. (You can change the sort to descending by merely clicking Ascending.)
Q&A
Q1: Why would I possibly want to add as many as four sorts?
A1: Again, let's assume that it's a sales report for an international company. Assume that you have just one group, country. Even though you've decided that you have no particular need for summary statistics for subgroups, you still would like to sort the records by several fields so you can easily locate the detail records. Within each country you would sort records by region, within each region by city, within each city…well, you get the idea.
Summary Options
The wizard can add aggregate totals for you, for both the group and the entire report.
- In the current dialog box, click Summary Options.
- Click the Area and Population boxes under Sum to select them. This gives you sums for these fields for each government group, as well as grand totals for each at the end of the report. Because you want to see both records and totals, the Detail and Summary option should be selected.
- Click OK and click Next to go to the next dialog box.
Layout
The dialog box for choosing a layout isn't terribly helpful, to say the least. Both the names and the graphical schemes are imprecise, and some are more confusing than anything else. If reports are central to your work, you'll want to try a few different layouts and see how they look. You might want to create a few reports using different schemes to see what they actually look like.
- In the Layout section, select Align Left 1. In Orientation, Portrait should be selected. Many reports use landscape orientation, which turns the printed page sideways so you can fit more fields on each page, but fewer records. You designed the earlier report using a portrait orientation, so to make the comparison equal, use Portrait (it shouldn't make much of a difference with relatively few fields). Note that, within the report itself, you can choose the orientation by selecting File, Page Setup, Page tab.
- Select Adjust the Field Width so all fields fit on a page. This choice can be catastrophic when you have many (say, 8 or 10) fields scrunched into one page, but you have relatively few here, so you should be okay.
- Click Next to go to the next dialog box.
Style and Title
The style choice is your own. Only Casual is unsuitable for business purposes (and even that's questionable).
- Select Corporate and click Next to go to the next dialog box.
- Give your report the title rptAdministrationWizard and click Finish. Access builds your report (see Figure 10.18).
You can compare your report with the completed rptAdministrationWizard in your solution database AsiaChap10End.mdb.
Figure 10.18 The report created from the wizard.
)