8.1. Report Basics
You can take more than one path to create a report. Experienced report writers (like you, once you've finished this chapter) often choose to create a report from scratch. Report newbies (like you, right now) usually generate a quick report with a single click. This section covers the simplest method for generating a report.
8.1.1. Creating a Simple Report
It takes just two steps to create a simple report, and a few more to fine-tune it. If you want to try out this technique for yourself, open the Boutique Fudge database (included with the downloadable content for this chapter, explained in Section 03.3.4) or a database of your creation, and follow these steps:
| UP TO SPEED |
Doing the Heavy Lifting with a Query
The most obvious way to build a report is to base it on an existing table. However, you can also create a report on top of a query . This approach lets you use some heavy-duty filtering or sorting on your records before they reach the report. It also makes sense if you want to create a report that uses information from more than one table.
For example, imagine you decide you want to create a product list that includes additional details from another table (like the category description from the ProductCategories table). Although you can create this report from scratch, it often makes more sense to structure your data with a query first. That way, you can reuse the query for different purposes (like editing), and you can change it any time.
In this example, the first step is to create a query that joins the Categories and Products table (Section 6.3). Then, you save this query, select it in the navigation pane, and choose Create Reports Report to create a report thats based on the query. You can then follow the normal steps to perfect your report.
You've already learned how you can shuffle columns around in a report. However, that's not all you can move. You can also add space between the rows (see Figure 8-4) and adjust all the following elements:
The logo (in the top-left corner). In a new report, the logo looks like a notebook with a circle around it.
The report title (right next to the logo). To start out, this is the name of the table or query on which the report is based (like Products).
The date and time (which is updated every time you open the report). Initially, this appears in the top-right corner.
The page number . This appears at the center-bottom of each page. In Layout view, Access treats the report as though all the data occupies one page, so you need to scroll to the end to find this element.
The report data (after the title). To change where the table in the report first appears on the page, click one of the column headers, and then drag it down (to add space between the title and the report data) or up (to remove the space).
The totals (at the bottom of some columns). Access automatically adds calculations for numeric fields. For example, when the ProductCatalog report is first created, Access adds a total at the bottom of the Price column that indicates how much it costs to buy one of each product. (This total is of dubious valueto change it, select the column, and then pick another summary option from Report Layout Tools Formatting Grouping & Totals Totals menu.)
If you're tired of merely rearranging columns, you may want to try adding ones that aren't already included or removing existing ones that you don't want. Removing a field is easy: just click to select it, and then press Delete. (You can try out this technique with the Discontinued field in the ProductCatalog report.)
When you create a simple report using the quick creation technique described in Section 8.1.1, you usually end up with all the fields you need. However, there are two reasons why you may need to add an additional field that isn't already in the report:
You want to add a field that's hidden in the Datasheet view (Section 3.1.4) . When you create a new report, hidden fields are left out.
You want to add a field with related information from a linked table . For example, you could add fields from the ProductCategories table to show information about the category that each product is in.
To add a new field, you need the help of the Field List pane (see Figure 8-5). To show it, choose Report Layout Tools Formatting Controls Add Existing Fields.
When you add a new field, Access uses the field name for the column heading, which isn't always what you want. Maybe you'd prefer Product Name (with a space) to ProductName . Or maybe you'd like to shorten ProductCategoryID to just Category . After all, the report shows the name instead of the numeric category ID, because the ProductCategoryID field uses a lookup (Section 5.2.5). Fortunately, renaming the column headers is easy. Just double-click one to switch it into edit mode. You can then edit the existing text or replace it altogether.
| FREQUENTLY ASKED QUESTION |
Adding Pictures to Reports
Can I store pictures in a table and show them in a report?
Many tables include embedded pictures using the Attachment data type (Section 2.3.8). You can use this technique to store employee photos, product pictures, or supplier logos. Depending on the type of picture, you may then want to include them in your printouts.
It is possible to show your pictures in a report (and even print them), provided you meet the following requirements:
The Dolls table in the bobblehead data-base Products table fits the bill, which lets you create a report like the one shown in Figure 8-6.
Alternatively, you can show the file name or the file type of an attachment in a report. To do this, you need to use the Field List pane (Figure 8-5). For example, if you have an attachment field named Picture, it appears with a plus button next to it in the Field List pane. Click the plus button, and you'll see the three Picture-related details you can display in a report: Picture.FileData (the attachment content itself, which is the image), Picture.FileName (the name of the file), and Picture.FileType (the type of file). If you want to show these details, just drag them onto your report.
Just like tables and queries, you can use several different views to change a report. When you create a report using the quick creation technique described earlier, you begin in Layout view, which is an ideal starting place for report builders. But depending on the task at hand, you may choose to switch to another view. You have four viewing options:
Layout View . Shows what the report will look like when printed, complete with the real data from the underlying table. You can use this view to format and rear-range the basic building blocks of the report.
Report View . Looks almost the same as Layout view but doesn't allow you to make changes. If you double-click a report in the navigation pane, Access opens it in Report view so you can see the data it contains without accidentally changing its design. One common reason to use Report view is to copy portions of your report to the clipboard, so you can paste them into other programs (like Microsoft Word). Figure 8-7 shows how that works.
Print Preview . Shows a live preview of your report, just like Layout view and Report view. The difference is that the preview is paginated (divided into print pages), so you can figure out how many pages your printout needs and where the page breaks fall. You can also change print settings (like page orientation) and export the complete report, as described in Section 8.2.2.
Design View . Shows a template view where you can define the different sections of your report. It's not nearly as intuitive as Layout view, but it does give you complete, unrestrained flexibility to customize your report. Access experts often begin creating a report in Layout view and then add more exotic effects in Design view. Learn more about Design view in Access 2007: The Missing Manual .
You can switch from one view to another by right-clicking the report tab title, and then choosing the appropriate view from the pop-up menu. (Or, you can use the Home Views View menu or the view buttons in the bottom-right corner of the Access window. Its just a matter of personal preference.)
After you've closed your report, you can reopen it in the view of your choice. Just right-click the report in the navigation pane, and then choose the appropriate view. Or double-click the report in the navigation pane to open it in Report view.
So far, you've learned how to quickly create a report based on a table or a query. However, you have another choiceyou can start with a blank slate and explicitly add each field you want. Both approaches are equally valid. You may prefer to use the quick creation technique when you want to build a report that closely follows the structure of an existing table or query. On the other hand, if you plan to create a report that uses just a few fields from a table, you may find it's easier to start from scratch.
Here's how you create a report from the bottom up:
Choose Create Reports Blank Report .
A new, empty report appears in Layout view. The Field List appears on the right, with all the tables in your database.
Add the fields you want from the appropriate table, either by dragging them from the Field List onto the report surface or by double-clicking them .
You can also use fields from related tables. For example, you can create a report that combines product information and the category details for each product. In this case, the report automatically uses a join query (Section 6.3) to get the results.
Format the columns .
When you create a report from scratch, the columns start off with no formatting at all. You'll need the formatting techniques described in the next section to add color and emphasis.
Add any other elements you want, like a logo, a title, page numbers, and the date .
When you create a simple report, you get all these ingredients for free. Fortunately, it's just as easy to add them to a report you're building from scratch. Just head to the Report Layout Tools Formatting Controls section of the ribbon (see Figure 8-8).