Chapter 18
In This Chapter
Creating an instant report from a single table
Making minor modifications to your instant report
Setting up a report step by step with the Report Wizard
Getting a sneak peak at your report-to-be
Choosing the perfect layout
The fact that you’re reading this chapter right now tells me that either you’ve already been asked to create a report or you’re afraid that might happen. Yes, if you’re like the rest of us, afraid applies now and then — the idea of reporting on a database seems daunting to many users. You might be wondering, “Do I have to learn some really complex Access features? Do I have to master some word-processing program so I can make the report look like more than just a list of records?”
The answers to those questions are no and no. You don’t have to learn anything other than a couple of quick mouse clicks in the Access workspace in order to whip up a snazzy report on the currently open or selected table, in just seconds. And you don’t have to master Word or any other word-processing program to dress up your report and make it look serious and important. No, you have all you need to make a quick, simple, yet professional-looking report, right here in Access 2016.
“But what if I have to report on more than one table?” you’re asking. What if your boss/customer/partner needs a report on data from Table A and Table B, and you know he or she does not want to see certain pieces of data from those tables anywhere on the report? In situations such as these, the Report Wizard comes in handy; using it, you can choose multiple tables as the sources for your report — and even pick and choose which fields to include from those tables.
Chances are you’ll need both of Access’s simple reporting tools (the Access Report and the Report Wizard buttons) over time — so it’s worth checking them both out now. I start with an analysis of both of them and then get into the procedural specifics of the simplest one first.
The Access Report and Report Wizard tools make reporting on your database extremely simple. If you use the Access Report tool, Access uses your table (the open or selected one at the time you click the tool’s Report button) to generate a report instantly. You can then go in and tweak margins, fonts, and other formatting so the result looks more like what you’d imagined and/or fits on the number of pages you prefer.
If you use the Report Wizard, you’re taken step by step through the process of choosing which fields (and which tables) to include in your report, how the report will look, and how the content will flow over one or more pages.
Each method has its merits in different situations:
To generate a report on an open table, all you really have to do is click your mouse twice:
Click the Create tab on the Ribbon, and then click the Report button. An instant report appears, as shown in Figure 18-1.
The table is now a report, laid out exactly as it appeared in Table view — as a series of rows and columns. It has a heading and a small graphic in the upper left corner, and some color has been added — using a default template — to the field names and the report’s title (which is the same as the table name).
If the Property Sheet panel opened along with your report, you can close it at this point. If you want to use the panel at some point in the future, however — perhaps when making design changes to the report — press the F4 key to redisplay it. Once the panel’s open, you can make changes to the settings for your report, and you learn more about how to do that in Chapter 19.
Use the File tab to access the Print command (or press Ctrl+P) if you want to print the report you see onscreen.
You can also display the report onscreen, now and in the future; there’s no requirement that you print the report immediately.
To save the report, press Ctrl+S or click the Save button on the Quick Access Toolbar. The Save As dialog box appears, as shown in Figure 18-2, and I suggest adding the word “Report” to the default name (which is the same as the table name). It helps later on when you’re making quick selections from the All Access Objects panel to not have two items (the table and the report) with the exact same name.
Although the Report tool works with only one table or query at a time, it still offers some choices for the way the report looks and how the fields appear in the report.
In the Report Layout Tools section of the Ribbon (the section is displayed after you use the Create tab to generate the quick, one-table report), you can choose from a variety of options for the tabular layout of your report. You can have each record listed on its own row, as a series of columns, or you can stack your fields with the Tabular button in the Table section of the Ribbon’s Arrange tab. The results of clicking the Stacked button are shown in Figure 18-3.
Hey, where is it, you say? If you can’t find the buttons to change your report’s tabular settings, click the Arrange tab, and look at the Table section (far left), also shown in Figure 18-3. There are several choices available, and my advice is that you experiment with different layouts, and when you like what you see onscreen, print. If you hate what you’ve done, just keep clicking the Undo button (up on the Quick Access Toolbar) until the report is back at its pre–I Don’t Like That state.
If you hate the report entirely, just right-click it in the left panel that lists the components of your database and choose Delete from the resulting pop-up menu. When a prompt appears, asking whether you really want to delete the report, just click Yes. Remember, all you had to do was click the Create tab and then click Report to make this thing — so how bad would it be to start over?
Not only can you pair up your report’s fields vertically, stacking them to keep relevant fields together, but also you can rearrange your columns so that (a) your report’s readers see what they want to see first (assuming they read from left to right) and (b) you can horizontally pair up things that relate to each other. As shown in Figure 18-4, all you have to do to rearrange columns is click on the heading for the column you want to move, and then Shift+click on any data in that column to select the data as well. You can also click the column heading and then click the Select Column button on the Arrange tab.
With your column selected, drag the heading and all the data that comes with it. When you release your mouse, the fields — the headings and then the data — are rearranged.
One thing that can be very appealing about a quick report on a single table is the ability to put the entire report on a single page (if you have only 20 or 30 records) or on a series of pages that includes all the table’s fields on each page (for large databases with hundreds or thousands of records, but not a whole lot of fields per record). This can be difficult on a report that has a lot of fields or uses all the fields in a table because rarely do they fit across an 8½-x-11-inch sheet of paper. Sometimes you can fit them all across a sheet of paper — if you resize the fields, narrowing them so they’re no wider than they have to be to display the widest entry in the column.
The easiest way to do this is to narrow the columns manually. You can widen all the columns in one fell swoop, but that requires working in Design view, which is discussed way over in Chapter 19. This way is quicker for now.
To adjust columns manually — the one column that’s taking up too much room or each of them, one at a time — simply click the column’s heading and then use the two-headed arrow that appears when you mouse over the column’s right seam to move the seam to the left to make the column header narrower. Then, using the same two-headed arrow, snag the first record in that column and drag it to the same width as you’ve just set the heading. The dragging process is shown in Figure 18-5, where I make more room for long company names, taking space away from Customer ID, where the horizontal space is not needed.
So you’ve decided to take things step by step, perhaps because you want to include multiple tables and/or queries in your report. Or maybe you’re still deciding what the best path is, and want to see what’s involved in the process.
The Report Wizard is simple. It requires a few more steps and decisions from you than the Report tool does, but it’s much more flexible than the instant Report tool. Here goes:
In your database window, click the Ribbon’s Create tab and then click the Report Wizard button. (It’s right there in the tab’s Reports section.)
The Report Wizard dialog box appears, listing all the fields in the active table. As shown in Figure 18-6, the dialog box offers
Use the Tables/Queries drop-down list to choose the table you want to start with.
The fields from the table you select appear in the Available Fields box.
Add fields to your report by double-clicking them in the Available Fields box.
By double-clicking, you add the fields to the Selected Fields box, and they become part of the report. You can also click a field once and then click the button with a >
symbol on it, as shown in Figure 18-7.
Repeat Steps 2 and 3 for each table and/or query in the database that you want to include in the report.
If, at any point, you want to add all the fields in a given table or query, just click the >>
button to add all the Available Fields to the Selected Fields list.
Click Next to move on to the next page of the Report Wizard (see Figure 18-8).
You can also click Next twice, which bypasses grouping issues, which, for a simple report, are often unnecessary. To explore this step of the Report Wizard in greater detail, check out Chapter 20.
Choose a sort order for your report — typically sorting on the field people will use to look up information in the report — as shown in Figure 18-9.
For example, if your report documents a list of employees, Last Name might be a good choice. A report on product sales would be useful in Product Number or Product Name order. You can sort by more than one field, choosing up to four fields to sort by and either Ascending or Descending for the sort order on each field. Figure 18-9 shows the LastName
field chosen for sorting in Ascending order, adding to the value of having grouped by Status, so that each status group is in alphabetical order by the volunteer’s LastName value.
Sorting is best done on fields that have either very few entries or a lot of duplicate entries:
Choose a Layout and an Orientation from the two sets of radio buttons and click Next.
Orientation decisions (Portrait or Landscape) are generally easier if you envision the report in your head — are there more fields than will fit across a sheet of 8½-inch-wide paper? If so, choose Landscape to give yourself 11 inches of paper (or 10 inches, to allow for the smallest margin possible) across which your fields will appear.
Figure 18-10 shows a format chosen at this stage of the Report Wizard and allows the user to set up the layout and orientation for that report.
You probably don’t want to use Justified unless your report has very few fields per record.
If you leave the Adjust the Field Width So All Fields Fit on a Page option checked, you run the risk of data being chopped off in the report and rendering the report unusable. If you have more than four or five fields, and if any of your fields have very long entries, turn this option off.
Click Next.
A default name for your report now appears in this next step in the wizard, as shown in Figure 18-11.
Give your report a name.
Type a name in the long box at the top of the dialog box. At this point, you also need to decide how to finish things up — with a Preview of the report, or by leaping right into Design view to make more changes to your report’s appearance and content. (This part of the process is covered in Chapter 19.) For now, choose to Preview the Report, which is the default.
Click Finish.
The report appears in a Preview window, at which point, you can print it or close it. To close it, right-click the report’s tab and choosing Close from the pop-up menu. If you make additional changes and haven’t chosen to save, you’ll be asked if you want to close without saving.
Figure 18-12 shows a preview of a report that lists a series of Volunteers, by Country (and in order by City within each Country), and that includes contact information for each customer.
When you’re in Print Preview mode (which results from clicking Finish to complete the Report Wizard process, as described in the steps in this chapter’s previous section), you can’t do a whole lot with your report except print it. But Print Preview shows exactly what your document looks like. Table 18-1 shows the tools Print Preview provides to help with your inspection.
Table 18-1 Print Preview Tools
Tool |
What It Is |
What It Does |
|
Print button |
Opens the Print dialog box. |
|
Size button |
Allows you to choose a paper size for your report. |
|
Margins button |
Allows you to set Normal, Wide, or Narrow Margins for your report. |
|
Show Margins |
Click the check box to display or hide the margins. |
|
Print Data Only |
Click the check box to include only your data in the report. |
|
Portrait button |
Converts your report to Portrait mode. |
|
Landscape button |
Converts your report to Landscape mode. |
|
Columns button |
Opens the Page Setup dialog box with the Columns tab chosen, allowing you to set up a columnar report. |
|
Page Setup button |
Opens the Page Setup dialog box with the Print Options tab chosen, allowing you to customize your printed output. |
|
Zoom button |
Click this button to choose a percentage view (from 10% to 1000%) or to Fit to Window. |
|
One Page |
Previews one page of your report at a time. |
|
Two Pages |
Previews your report two pages at a time. |
|
More Pages |
Click this to choose to preview four, eight, or twelve pages at a time. |
|
Refresh All |
Refreshes the report to display the latest data in the table(s) included in the report. |
|
Data buttons |
Exports your report to any of the following: Excel, a text file, a PDF or XPS file, or an email message; click the More button to choose Word or other options. |
|
Close Print Preview |
As you might have guessed, this closes the Preview window. |
In Figure 18-12, you see the report. In your preview (assuming you’re working along with me here or have tried this on your own), you may be able to see the entire page of your report. The parts you can see look okay, but how can you commit to printing if you don’t know how the whole page looks? In Figure 18-13, you can click your mouse when the pointer turns to a magnifying glass — as it will when you mouse-over the page. You can also use the Zoom tool to choose a lower zoom percentage.
Clicking any of the page-number buttons (One Page, Two Pages, More Pages) sets the Zoom view to the Fit View setting. When you have two pages showing, the odd-numbered page is always on the left, unlike book publishing, which puts the odd-numbered page on the right (unless the typesetting department is having a very bad day).
If you use the Zoom section of the Ribbon’s Print Preview tab rather than one of the page-number buttons, Access offers quite the selection of Page View options, as you see in Figure 18-14. Set your system to show 1 page or 2 pages — or click the More Pages button’s drop-down list to choose up to 12 pages per screen (of course, you won’t be able to read anything at this setting, but at least you can see how the whole report lays out).
You can right-click anywhere on the Print Preview screen to see a pop-up menu that gives you the choice of switching the zoom or viewing a specific number of pages, as shown in Figure 18-15. When you click the Zoom button drop list in the Zoom section of the Print Preview tab, you get a similar pop-up menu offering various zoom percentages, from 10% to 1000%.
Other than the Zoom submenu, the following useful commands are available when you right-click the Print Preview screen:
After looking at your report in the Print Preview window, you have a decision to make. If you’re happy with how your report looks, great! Go ahead and print the document. However, a few minutes of extra work does wonders for even the simplest of reports.
Start with the basics in the Page Setup dialog box. To get there, right-click anywhere on the report and choose Page Setup from the pop-up menu. (This command and others hidden away in the pop-up menu are briefly explained in the preceding section of this chapter.)
Use the Page Setup dialog box to fine-tune your report in terms of its Print Options, Page, and Columns settings. You can adjust margins, change orientation, and control how many vertical columns your report content is divided into — all from within this handy dialog box.
The Print Options tab of the Page Setup dialog box controls the width of the margins in your report — no surprises here.
Figure 18-16 shows your margin options. The page has four margins, so the dialog box includes a setting for each one (Top, Bottom, Left, and Right).
Here is how you set or change margins:
Double-click in the appropriate box (Top, Bottom, Left, or Right) and type a new setting.
When you double-click the box, the current entry is selected. Access automatically uses whatever Windows thinks is your local unit of measurement (inches, centimeters, or whatever else you measure with). On the right side of the dialog box, Access displays a sample image, which shows you how your current margin settings work on a page. After editing one margin, you can press Tab to move through the remaining fields (Bottom, Left, and Right, if you started out in Top), and you can adjust each one.
Look at your report in Print Preview to check your adjustments.
If you need to tweak the report, simply go back to Page Setup and play with the options until everything looks just right.
The Page tab of the Page Setup dialog box tells Access about the sheet of paper on which you plan to print your report — including its size and layout — as well as what printer you keep the paper in. You make some of the most fundamental decisions about how your report looks from the Page tab of the Page Setup dialog box. (See Figure 18-17.)
The Orientation box sets the direction that your report prints on paper:
Your other choices for the Page tab are determined by your printing capabilities:
The last part of the Page tab lets you choose a specific printer for this report.
Most of the time you can leave this setting alone; it’s useful only if you want to force this report to always come from one specific printer at your location. You can choose either
The Use Specific Printer option: You choose the printer yourself.
If you click the Use Specific Printer option, the Printer button comes to life. Click this button to choose from among your available printers.
You get to make more decisions about your report’s size and layout on the Columns tab, as shown in Figure 18-18.
The Columns tab of the Page Setup dialog box is divided into three sections:
If the number of columns you select fits (or if you’re willing to lose your view of the information in some of your fields), click OK to see a view of how your document looks with multiple columns.
The Grid Settings section of the Columns tab also adjusts
The bottom section of the Columns tab, called Column Layout, controls how your columns are organized on the page. You have two options here: