Access 2007[c] The Missing Manual

2017-07-07 02:10:07

10.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.

Note: As you'll see, the simple approach to creating a report always puts your information in a tabular structure (with columns and rows). You'll learn how to break out of this design in Chapter 11.

10.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 3.4.2.3) or a database of your creation, and follow these steps:

In the navigation pane, select the table you want to use for your new report .

This example uses the Products table from the Boutique Fudge database. You can also create a report that's based on a query. See the box "Doing the Heavy Lifting with a Query" (Section 10.1.2) for more about this trick.

Choose Create Reports Report .

A new tab appears with a simple, automatically generated report. This report arranges information in a table, with each field in the table (or query) occupying a separate column. The Report view looks somewhat like the datasheet, except for the fact that it has nicer formatting and uses space more efficiently , as shown in Figure 10-2.

When you first create a report, the fields are arranged from left to right in the same order that they live in the table. It doesn't make any difference if you've rearranged the columns in the datasheet. However, any columns you've hidden in the datasheet (Section 3.1.4) are left out of the report.

Note: You can fine-tune exactly which data appears in your report by removing columns you don't want and adding new columns. Section 10.1.3 has more about this trick.

Resize the columns smaller or larger until you have the balance you want .

To resize a column, first click the column header to select it. (A dotted line will appear around the column.) Next , move the mouse to the right-side of the column header, so that it changes into the two-way resize pointer. Finally, drag the column border to the left (to make it smaller) or to the right (to make it larger). Figure 10-3 shows this process in action.

Figure 10-3. Drag the edge of the column to the desired width. A black box shows you the new width. When you release the mouse button, Access changes the column width and moves all the following columns accordingly . To prevent the last column from leaking off the edge of the page, you may need to shrink some columns after you expand others.

Note: You'll see a dotted line on the right side of your report that indicates the edge of the page. You can resize a column right off the edge of the pagewhich may make sense if you have dozens of columns, and the only way you can deal with them is to create a printout that's two pages wide. Generally, though, it's better to make sure all your fields fit the width of the page, and turn the page sideways using landscape orientation (Section 3.4.1.2) if you need to accommodate more columns.

Arrange the columns in the order you want by dragging them .

To move a column, click the column heading, and then drag the column to a new position.

Tip: You can also move columns with the keyboard. Just click to select the right column, and then use the left and right arrow keys to hop from one spot to the next.

Optionally, you can tweak the formatting by changing fonts, colors, and borders .

The quickest way to change the formatting of your report is to select the appropriate part (by clicking), and then use the buttons in the Report Layout Tools Formatting Font section of the ribbon. Using this technique, you can change how titles, column headers, and data appear. Section 10.3.1 has more on this technique.

Optionally, choose Office button Print to print the report now .

You can also adjust the print settings in Print Preview mode (choose Office button Print Print Preview), as described in Section 10.2.

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.

10.1.2. Arranging a 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 10-4) and adjust all the following elements:

The logo (in the top-left corner). In a new report, the logo looks like a note-book 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 value to change it, select the column, and then pick another summary option from Report Layout Tools Formatting Grouping & Totals Totals menu.)

Tip: You can also remove most elements by selecting them, and then pressing the Delete key. This trick is handy if you don't want to see details like page numbers , dates, or totals.

Figure 10-4. Top: To add space between the rows, click a value in one of the rows, and drag it down.

Bottom: All the rows are adjusted to have the same spacing.

10.1.3. Adding and Removing Fields

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 10.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 10-5). To show it, choose Report Layout Tools Formatting Controls Add Existing Fields.

Figure 10-5. The top portion of the Field List window lists the fields from the table (or query) on which the report is based. The middle portion lists the fields in any related tables, and the bottom portion lists unrelated tables (which you probably won't use). To add a field, drag it from the Field List pane and drop it on your report.

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:

Your picture is stored in an attachment field . (See Section 2.3.8 for more information about the attachment data type.)

Your picture is stored in a standard picture format (think .bmp, .jpg, .gif, .tif, .wmf, and so on) . If you have another type of file in an attachment field, you just see the icon of the related application (like Microsoft Word for a .doc file) in your report.

Your picture is the first attachment . If you have more than one attachment, when you select the row in the report, tiny arrow buttons appear above that you can use to move from one attachment to another. But it's way too much work to do this with all your records before you print a report.

The Dolls table in the bobblehead database Products table fits the bill, which lets you create a report like the one shown in Figure 10-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 10-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.

10.1.4. The Many Views of a 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 rearrange 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 10-7 shows how that works.

Figure 10-6. You can see this in the sample Bobblehead database examples for this chapter. (They're available on the "Missing CD" page at www.missingmanuals. com.) The report is named DollsWithPictures.

Figure 10-7. To select a bunch of rows, click in the margin on the left next to the first row you want to select, and then drag down to highlight the rows you want. Then, right-click the highlighted portion, and choose Copy to transfer it to the clipboard, so it's ready for pasting into other Windows applications.

Note: If you want to transfer the entire content of a report, you should consider the export features described in Section 6.3.1.

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 10.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. You'll learn more about Design view in Chapter 11.

Note: Design view is a throwback to previous versions of Access, which didn't include the more intuitive Layout view and Report view options. It's still useful for some tasks , but it's no longer the central station for shaping and formatting a report.

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.

10.1.5. Creating a Report from Scratch

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. Alternatively, 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 10-8).

Figure 10-8. The logo and title typically sit at the top of your report. You can use any picture for the logo and any text for the title. Access gives you more options for the date (Figure 10-9) and page number (Figure 10-10).

Figure 10-9. When adding date information, you can choose whether to include the date, the time, or both. You also pick the format. Once you've added the date information, you can change the font, borders, and colors, as with any other report element.

Figure 10-10. With page numbers, you can choose the format, the position, and the alignment. (The position determines whether the page numbers appear above or below the report data. Although you can drag the page numbers around after you add them, Access will shift the report data to make room, based on your choice.)