Teaching Access users how to enter data into tables is relatively easy, compared to teaching them how to get information out of a table and into a report. This week, I’m starting a series of columns aimed at demonstrating must-know techniques for creating Access reports. (I'll use Access 2000 to illustrate these tips, but the approaches are similar in Access 97 and 2002.)
I’ll present a new set of Access reporting tips each month. To kick things off, let's go over some of the options available in Access’ Report Wizard. To illustrate these tips, I'll create reports based on the DemoReports database with the records shown in Figure A.
|I'll use the records in this table to create my sample reports.|
Take advantage of the wizard
There's a good reason why Microsoft includes the Report Wizard in Access: With just a few clicks, you or your users can create the majority of the reports needed. To demonstrate how the wizard works, open a database, click Reports, and then double-click the Create Report Using Wizard option. When you launch the Report Wizard, it follows this same script every time.
Decision 1: Which fields or columns do you need?
In the first screen, the wizard asks: "Which table or query contains the fields that you want to see in this report?" To answer that question, click the drop-down list in the section labeled Tables/Queries and choose a table or query from that list. When you click on a table or query in that list, the fields in the table or query appear in the Available Fields list. I usually teach users to click the All Fields button, the one labeled with two greater than signs (>>), to put all the fields in the Selected Fields window. Then, if there are fields you don't really need, select them and click the Remove button, the one labeled with a less than sign (<).
The upshot is that you don't have to always include all of the fields in the report. You can eliminate fields that aren't essential to the information you're trying to capture or summarize. Figure B shows what my sample report screen looks like after I clicked the All Fields button.
|In the first screen, you tell the wizard which fields you need in your report.|
Decision 2: Do you need to do any sorting or subtotaling (grouping)?
After selecting the fields you want, click Next. The prompt in the top-left corner of the dialog box will say, "Do you want to add any grouping levels?"
The problem is that "grouping levels" is a phrase that doesn't always make sense to novice Access users. In this context, grouping means sorting. The first field you click on will be what Access uses to determine the order in which records in the report appear. That is, Access will sort the records primarily on that first field.
Suppose you want to sort the list by the customer's last name. Click on the Lname field to select it, and then click on the Add Field button (>). Your screen will then look like the one shown in Figure C.
|The first field you use for grouping determines how Access will sort the records that appear in your report.|
If you click the wizard's Finish button at this point, Access will generate a report that sorts your records in alphabetical order by the Lname field. However, if you click the Grouping Options button, you’ll see the options shown in Figure D, which allow you to customize how the groups are labeled.
|The grouping options for a text field let you decide whether to label the groups using from one to five letters from each group.|
Figure E shows what my sample report looks like after I told the Report Wizard to group my records by the first letter of the Lname field. You can open the report in Design view to tweak the formatting of the group labels.
Decision 3: Do you need more than one level of sorting?
After you select the initial field on which to group your records, you'll want to click the wizard's Next button (instead of Finish). If you click Finish at this point, you won't be able to customize the report's layout, style, or title. Once you've clicked Next, Access will ask if you want to subtotal your records on any additional fields.
In this case, you'd want to use the Fname (first name) field to break any ties that occur when Access sorts the records by last name. To do so, just click on the drop-down list and select the appropriate field, as shown in Figure F.
|You can sort the records in your report by up to four additional fields, in either ascending or descending order.|
Decision 4: What layout would you like to use?
After you select the additional field or fields on which to sort your records, click the Finish button to accept the default report settings and display the report. However, if you'd like to customize the way your report appears, click Next to display a screen like the one shown in Figure G.
|Click on a radio button to display a preview of each Layout option.|
Decision 5: What style would you like to use?
After selecting a layout, click Next, and the wizard will present several stylistic options from which to choose, including Bold, Casual, Compact, Corporate, Formal, and Soft Gray. As you click on each option, you’ll see a preview and a brief description of each style.
Decision 6: What title should appear at the top of the report?
Once you select the style and click Next, you’ll reach the end of the Report Wizard process, and you’ll be asked to enter a title for your report. The default is the name of the table or query on which the report is based. Just enter the title and click Finish. When you do, Access will show an on-screen preview of the report.
When you close the report preview window, you’ll see the new report object listed in the Reports section of the database window. If you want to go back and customize the report, just open it in Design view.
Report tips to come
In next month’s column on Access reports, I’ll look at some cool things you can do to customize your reports in Design view, including how to group records on calculated fields and how to create a report-level cover page.
Access to information
To comment on this issue of Help Desk Advisor, please post a comment or write to Jeff.