Office 2016 For Dummies (2016)
Part VI
Storing Stuff in Access
Chapter 18
Creating a Database Report
In This Chapter
Creating a report with the Report Wizard
Displaying and printing reports
Modifying the appearance of a report
Erasing a report
Data is useless if you can’t understand what it means, so that’s why Access lets you create reports. A report simply provides a printed version of your data arranged in some useful way.
One report can dig through your data and print a list of your top ten salespeople. Another report may print out the top ten products you sold last year. Reports simply provide a way to make sense of your data and print it so you can examine your data on paper.
Using the Report Wizard
The easiest way to create a report is to use the Report Wizard, which guides you step by step through arranging and selecting which data to print on a report, along with sorting your data at the same time. To use the Report Wizard, follow these steps:
1. Click the Create tab.
2. Click the Report Wizard icon in the Reports group.
The Report Wizard dialog box appears, as shown in Figure 18-1.
3. Click in the Tables/Queries list box and choose the table or query that contains the data you want to print in a report.
4. Click a field in the Available Fields box and then click the > button. Repeat this step for each additional field you want to display in your report.
5. Click Next.
Another dialog box appears, asking whether you want to group your data by a specific field, such as by Last Name or Employee Number, as shown in Figure 18-2.
6. Click a field name displayed in the box and then click the > button. Repeat this step for each additional field you want to use to group your data on the report.
7. Click Next.
Another dialog box appears, asking you to choose up to four fields to use for sorting your data in your report, as shown in Figure 18-3.
8. Click in a list box and choose a field to sort your data.
Data will be sorted by the order chosen here for each additional field you sort on.
9. (Optional) Click the Ascending button to change the sorting criteria from Ascending to Descending and vice versa.
10. Click Next.
Another dialog box appears, asking you how to lay out your report, as shown in Figure 18-4.
11. Select a radio button under the Layout group, such as Stepped or Block.
You may see different options depending on the fields you chose in the previous steps.
12. (Optional) Select a radio button in the Orientation group, such as Portrait or Landscape.
13. Click Next.
Another dialog box appears, asking for a descriptive name for your report.
14. Type a descriptive name for your report and then click Finish.
Access displays your report, as shown in Figure 18-5.
Figure 18-1: The Report Wizard lets you choose where to retrieve your data for your report.
Figure 18-2: A report can group data under categories so you can see all your data arranged by a specific field.
Figure 18-3: You can sort the data in your report, using up to four fields.
Figure 18-4: The Report Wizard offers different options for making your report look readable.
Figure 18-5: Access displays your report onscreen.
To view your report again, double-click the report name in the left pane of the Access window.
If you change any data, you can see those updated changes in your report by following these steps:
1. Right-click the tab that represents your currently displayed report.
A pop-up menu appears.
2. Choose Close.
3. Double-click the report name in the left pane of the Access window.
Your chosen report appears again, displaying any data you updated or modified since the last time you viewed the report.
Access won’t automatically update your reports just because you modified any data that the report displays.
Manipulating the Data in a Report
After you create a report, you can manipulate the data displayed in that report, such as sorting data in ascending or descending order, or applying a filter that only displays data that meets a certain criteria.
By using a report, you get a different view of your data. By manipulating the data in a report, you create alternate views of the same report. Some common ways to extract information from a report include counting, sorting, and filtering.
Switching a report to Layout view
To manipulate data in a report, you must first display your report in Layout view, which you can do by following these steps:
1. Double-click the report name in the All Access Objects pane.
Access displays your chosen report.
2. Click the Home tab and click the downward-pointing arrow underneath the View icon in the Views group.
A pull-down menu appears.
3. Choose Layout View.
Access displays your report in Layout view, which highlights an entire column (field) at a time, as shown in Figure 18-6.
Figure 18-6: Layout view highlights a single column of your report so you can manipulate the data within the highlighted field.
Counting records or values
To make reports more useful, you can have Access count and display information, such as which products are selling the best or the total dollar amount of each sale so you can tell exactly how much money your company made during March. By counting records or adding up values stored in fields, Access can help you better interpret the data displayed in a report.
To count the number of records or values in a report, follow these steps:
1. Switch to the Layout view of your report by following the steps in the preceding section, “Switching a report to Layout view.”
2. Right-click in the column (not the column heading) that you want to count.
Access highlights your chosen column and displays a pop-up menu, as shown in Figure 18-7.
3. Click Total.
The menu command displays the Total command along with the field name you right-clicked, such as Total Last Name or Total Sales.
Access displays a submenu that displays Count Records or Count Values.
4. Choose either Count Records or Count Values.
Access displays the total count in your report.
Figure 18-7: Right-clicking a column displays a pop-up menu for manipulating your data.
If you choose the Count Records or Count Values command again, you can hide the total count in your report.
Sorting a field
Access can sort each field in ascending or descending order. Sorting a field simply rearranges the data in your report for your convenience. To sort a column (field) in a report, follow these steps:
1. Switch to the Layout view of your report by following the steps in the preceding section, “Switching a report to Layout view.”
2. Right-click in the column (not the column heading) that you want to sort.
Access highlights your chosen column and displays a pop-up menu (refer to Figure 18-7).
3. Choose one of the following:
· Sort A to Z (or Sort Smallest to Largest): Sorts in ascending order.
· Sort Z to A (or Sort Largest to Smallest): Sorts in descending order.
Access sorts your chosen data in your report.
Filtering a field
Filtering can tell Access to only display data that meets certain criterion, such as a fixed amount. For example, if you have a report that lists all the sales of products, you can filter your report to show only those products that sold over a fixed amount, such as $1,000.
To filter data in a field, follow these steps:
1. Switch to the Layout view of your report by following Steps 1 through in the previous section, “Manipulating the Data in a Report.”
2. Right-click in the column (not the column heading) that you want to filter.
Access highlights your chosen column and displays a pop-up menu (refer to Figure 18-7).
3. Choose Filters.
Depending on the type of data your column contains, the Filter command may appear as Text Filters or Number Filters.
A submenu appears, as shown in Figure 18-8.
4. Choose a filter criteria, such as Equals or Less Than.
Depending on the criteria you choose, a Custom Filter dialog box appears.
5. Type your criteria in the Custom Filter dialog box and then click OK.
Access applies your filter to your report.
Figure 18-8: The Filters command displays a submenu of different criteria you can choose from.
You can always turn off your filter by clicking the Home tab and then clicking the Toggle Filter icon in the Sort & Filter group.
Editing a Report
After you create a report, you may want to modify it later to expand the space used to display data or eliminate fields altogether.
To edit a report, you must switch to the Design view of your report, as shown in the sidebar “Switching to Design view.” You can modify your report while you view it in Design view.
Switching to Design view
To see the Design view of a report, follow these steps:
1. Double-click the name of the report in the All Access Objects pane.
2. Click the Home tab.
3. Click the downward-pointing arrow underneath the View icon that appears in the Views group.
A pull-down menu appears.
4. Choose Design View.
Access displays your report in Design view, as shown here.
At this point, you can move, resize, add, or delete fields on your report:
· Text is bold for labels that print identifying text, such as First Name or Sales Region.
· Text in normal typeface (not in bold) represents fields that display data in your report.
Resizing fields
When the Report Wizard creates a report, it doesn’t always leave enough room to display your actual data. If a field is too small, Access may display data as a series of x’s, such as xxxxx. If this occurs, resize a field to make it wider. (If your data turns out to be smaller than the field, you may need to shrink the field.) To resize a field, follow these steps:
1. Display your report in Design view.
Follow the steps in the sidebar “Switching to Design view.”
2. Choose the field you want to resize.
Access highlights your chosen field.
3. Move the mouse pointer over the left or right edge of the field until the mouse pointer turns into a two-way pointing arrow.
4. Drag the mouse to resize and expand or shrink the field.
Access displays your report with your modified field size.
Deleting fields
If a report displays data that you no longer want to see, you can delete that field from your report by following these steps:
1. Display your report in Design view.
Follow the steps in the sidebar “Switching to Design view.”
2. Right-click the field you want to delete.
Access highlights your chosen field and displays a pop-up menu.
3. Click Delete.
Access deletes your chosen field.
You can press Ctrl+Z right away to retrieve any fields you may have deleted accidentally.
Making Reports Look Pretty
Reports can be useful for displaying data, but go one step farther and make your reports look visually pleasing as well. One way to change the appearance of a report is to use a predefined theme, which can instantly add color to make even the dullest report look interesting.
Applying themes
A theme simply rearranges the appearance of your entire report so it doesn’t look like a boring list of text and numbers. To apply a theme to a report, follow these steps:
1. Display your report in Design view.
Follow the steps in the sidebar “Switching to Design view.”
2. Click the Design tab.
3. Click the Themes icon in the Themes group.
A menu appears, listing all the available themes (as shown in Figure 18-9).
4. Move the mouse pointer over a theme.
Access shows how your report will look with the selected theme.
5. Select a theme to choose it for your report.
Access displays your report with your selected theme.
Figure 18-9: Themes give you a quick way to modify the appearance of a report.
Creating conditional formatting
A report can display data, but sometimes you may want help in identifying certain types of data. For example, you may want Access to highlight sales figures that are greater than $250,000 so you can spot this information easier. While you can manually examine a report and highlight such information yourself, it’s faster and more accurate to let Access do it instead.
Formatting data based on certain criteria is known as conditional formatting. The idea is that Access formats data only when certain conditions are met, such as a value greater than $250,000 or less than $10,000.
To use conditional formatting, you need to define the field to format, define a rule to trigger the formatting, and then the type of formatting you want to apply, such as highlighting the field in red or yellow.
To apply conditional formatting to data in a report, follow these steps:
1. Display your report in Design view.
Follow the steps in the sidebar “Switching to Design view.”
2. Select the field to which you want apply conditional formatting.
You can apply conditional formatting to a numeric or text field.
3. Click the Format tab.
4. Click the Conditional Formatting icon in the Control Formatting group.
A Conditional Formatting Rules Manager dialog box appears, as shown in Figure 18-10.
5. Click the New Rule button.
A New Formatting Rule dialog box appears, as shown in Figure 18-11.
You can click the Edit Rule or Delete Rule button here to edit or delete a rule that you’ve created.
6. Select a rule type:
· Check values in the current record or use an expression (you can create a rule that only considers the value of a single field)
· Compare to other records (you can create a rule that examines the value of the same field stored in other records)
7. Define your rule under the Edit the Rule Description category.
Depending on the option you chose in Step 6, the Edit the Rule Description category may display different options.
8. Choose the type of formatting to display if the data in a field matches the rule that you defined in Step 7.
9. Click OK.
The Conditional Formatting Rules Manager dialog box appears again, displaying your newly created rule.
10. Click OK.
Access now displays your formatting changes in the field if it matches your defined rule.
Figure 18-10: The Conditional Formatting Rules Manager dialog box.
Figure 18-11: The New Formatting Rule dialog box lets you define a rule.
Deleting a Report
Eventually, you may find that you no longer need a report, so you may as well delete it. To delete a report, follow these steps:
1. Right-click the Report tab.
A pull-down menu appears.
2. Choose Close.
3. In the left pane, right-click the report that you want to delete.
A pull-down menu appears.
4. Click Delete.
A dialog box asks whether you really want to delete your report.
Make sure that you really want to delete a report. You won’t be able to retrieve it afterward.
5. Click Yes (or No).