14.4 Customizing Reports

Layout View

Layout View is a great way to see the print preview grid lines, with the ability to change the size and placement of all labels, text boxes, and design elements. Right-clicking and selecting Layout View shows how much of the report will fit on a page. In Layout View, each control on the report displays real data, making this a useful view for setting the size of controls or performing tasks that affect the report’s appearance and usability, as Figure 14.50 shows.

Figure 14.50 In the Layout View, you can make changes while viewing the form to see how the user will see the form with data included. (Used with permission from Microsoft)

Design View

In Access, Design View is a view that allows you to create and customize the layout and structure of a report. Design View is an important feature for creating professional-looking reports, as it allows you to customize the design and appearance of the report to suit your needs.

When you open a report in Design View, you’ll see a grid that represents the layout of the report. The report is divided into several sections, including the Report Header, Page Header, Group Header, Detail, Group Footer, Page Footer, and Report Footer. Each section has its own set of properties that you can modify to change the appearance and behavior of the section. You can also add various types of controls to the report, including text boxes, labels, buttons, and images in this view. Grouping and sorting options to the report can help organize and display the data in a meaningful way.

Design View also allows you to set properties for the entire report, such as its orientation, margins, and page size. You can also add themes and styles to give the report a consistent and professional appearance. Overall, this view allows you to create custom reports that suit your needs in inventory management and retail store operations while at WorldCorp. By taking advantage of the many customization options available in Design View, you can create reports that are both informative and visually appealing.

As you can see in Figure 14.51, the Design View includes all sections of the report.

Figure 14.51 The gridlines in Design View are helpful to keep the form looking professional by lining up controls and sizing elements appropriately for the fields. (Used with permission from Microsoft)

Grouping and Sorting in a Report

Tools that can group and sort your information will be of utmost importance when organizing your report. Information is often easier to understand when it is divided into groups. For example, in the invoice for Awesome Clothes Inc., the sections are used to group the details and relevant information. In addition, placing a total (a sum of all Amounts) in the Report Footer section can replace a lot of manual interaction with a calculator. The customer can quickly see the total of the grouped sections, showing the amount owed.

Grouping and sorting are important features in Access reports that allow you to organize data in a meaningful way. Additional considerations why you might want to use grouping and sorting in your Access reports are endless. Grouping and sorting can make it easier for users to read and understand the data in a report. By grouping related data together and sorting it in a logical order, you can help users quickly identify patterns and trends. It also allows you to summarize data by creating subtotals or totals for each group. Sorting can help you identify outliers or anomalies in your data. For example, if you’re sorting a list of customers by purchase amount, you might notice that one customer has made an unusually large purchase, which could indicate a potential sales opportunity or a data entry error.

Grouping and sorting can help you make more informed decisions by presenting the data in a clear way. By using these tools in different ways, you can analyze the data from multiple perspectives and identify opportunities for improvement. By using these features, you can create reports that are more informative and easier to understand, which can ultimately help you make better business decisions that affect our success at WorldCorp.

Filtering Records

Another useful tool in Access is the filtering tool. This gives you the ability to filter a report prior to printing or presenting the information. There are just a couple quick steps you can take every time to ensure the information that is obtained in the report is correctly filtered to the desired data points.

To start, open the report in Design View. Click on the Sorting and Grouping button in the Grouping & Totals section of the Design tab on the ribbon. In the Sorting and Grouping dialog box, select the field that you want to filter by. Simply click on the “...” button next to the Filter property. In the Filter dialog box, select the criteria that you want to use for the filter. You can select from a variety of options, including Equals, Not Equals, Contains, Does Not Contain, Between, and more.

Enter the value or values that you want to use for the filter. You can enter a single value, multiple values separated by commas, or a range of values. Next, click the OK button to close the Filter dialog box and save and close the report. Open the report in Print Preview or Report View to see the filtered results. Note that you can also apply filters to a report while viewing the report in Print Preview or Report View. To do this, click on the Filter button in the Sort & Filter section of the Home tab on the ribbon, and then select the criteria that you want to use for the filter.

To use an actual example, let’s filter our customers living in Ohio. Simply right-click on states listed as OH, and click Text Filters - Equals OH (see Figure 14.52).

Figure 14.52 A report of information gathered from data entered into the form can be sorted and filtered by text or by fields. (Used with permission from Microsoft)

In Figure 14.53, Access applies the filter, and you can now print the report including only the data selected from the filter.

Figure 14.53 The report will only display the information based on the filter; the remaining information will be hidden but is still contained in the table. (Used with permission from Microsoft)

Adding Totals and Subtotals

Adding totals and subtotals to a report in Microsoft Access is useful for summarizing data and providing meaningful insights to numerous activities at WorldCorp. A few reasons why you might want to add totals and subtotals to your report could include summarizing data, grouping data, identifying outliers, and comparing datasets.

Totals and subtotals allow you to summarize data in a report by providing aggregate values such as sum, average, count, minimum, maximum, and more. This can help users quickly understand the overall trends in the data. Grouping data by a particular field or criteria, such as by department or product category is very useful for subtotals by allowing users to analyze the data from different perspectives. Totals can be used to identify outliers or anomalies in the data. For example, if the total sales for a particular product are much higher than the other products, it may indicate that there is a potential opportunity for growth. In addition, they can be used to compare data across different time periods, geographic locations, or other criteria. This can help users identify trends and patterns in the data that may not be immediately apparent. Layout View be used to add totals or averages to a report.

To add a total in a report, start by opening the report in Design View. Click on the field where you want to add the total. Then, in the Properties window, scroll down to the Total property and select the type of total you want to add. See Figure 14.54 for the available options. Finally, save the report and reopen it in Print Preview or Report View to see the total.

Figure 14.54 Access provides some tools to help you analyze information, although not to the extent that you can do data analysis in Excel. (Used with permission from Microsoft)

Adding Fields to a Report

To add fields to a report in Access, start by opening the report in Design View. Select the Add Existing Fields button in the Controls section of the Design tab on the ribbon. In the Add Existing Fields dialog box, select the table or query that contains the fields that you want to add to the report. Select the fields that you want to add to the report by checking the boxes next to their names. Click the Add button to add the selected fields to the report.

In addition, you can drag the fields to the desired location on the report once they are added. You can also resize the fields by dragging the edges of the control box. Make sure to save the report. Once you are done adding the fields to the report, you can customize their appearance and formatting by adjusting the properties in the Properties window.

As an example, we will review how text boxes can be added to a report to improve its ability to communicate information at WorldCorp. Start by opening your report in Layout View or Design View. Note: If your database does not contain any tables, the Field List pane will be empty. When you find the field(s) you want to add, double-click the field and then drag it from the Field List pane to the form or report, as Figure 14.55 shows.

Figure 14.55 From the Report Layout Design tab you can add additional fields to the report from the Tools command group. (Used with permission from Microsoft)

Adding Time and Date

One area that is very important on an invoice is the invoice date and due date. In addition to the original purchase date, it can be helpful to calculate a due date or add a mailing date. In the following example, we will see the procedure for adding three dates: origin date, due date, and mailing date. This custom build will clearly explain to the customer the payment due date parameters.

Start in Layout View or Design View. The DateOut field, as shown in Figure 14.56 was included in the field list. This information can be presented on the report and used later in calculating the additional information.

Figure 14.56 Choose the field you want added to the report and place in the report in the desired location. (Used with permission from Microsoft)

Formatting a text box prior to copying it is highly recommended to save time and keep attributes alike. In the example shown in Figure 14.57, copy and paste the label and text box twice, pasting the second instance directly below the first. Then, change the labels to represent the three dates the customer should see: Purchase date, Invoice date, and Due Date (Figure 14.58). For appearance, the borders on each label were changed to transparent.

Figure 14.57 If you are using a similar layout for multiple fields in the report, you can insert once and copy and paste for the additional fields. (Used with permission from Microsoft)

Figure 14.58 Descriptive titles are needed for each field in the report. (Used with permission from Microsoft)

Next, open the property sheet and select the Invoice Date text box. Change the control source to =Date()—a quick change that will provide the current date for the text box, as shown in Figure 14.59.

Figure 14.59 By changing the properties for the inserted field, you can add formulas to automatically populate certain aspects of the form. (Used with permission from Microsoft)

Select the Due Date text box and open the property sheet. Change the control source to =[DateOut] + 30. The control source is the field to which your text box is linked; it will display the data in that field and any updates you make to the field. This sample expression makes a calculation: It takes the field DateOut and adds 30 days to the field. The text box will display the expected due date of the invoice, as you can see in Figure 14.60. As this is probably the most important date in the report for the customer, change the font to red and make it boldface. This subtle change will help direct the customer’s eyes to the important due date field.

Figure 14.60 Mathematical operators can be utilized in field properties to modify dates as needed. (Used with permission from Microsoft)

Adding and Formatting Text

Many companies require particular directions when they invoice their customers. Terms and conditions are required and standardized through contracts. Placement, size, and accessibility are important, especially for current or future customers. In the example of the customer invoice, suppose you want to include a direction in the Page Footer section to instruct the customer on how to make a payment. In the following example, a label is used in the Report Footer section as direction for making payment. It provides information on how to direct the payment and where to send it. In designing this label, you will need to know how to set the size, font, and color to meet company guidelines or standards.

To do this, open the report in Layout View and select the Label option from the Report Layout Design Controls (see Figure 14.61). Draw the label in the Footer section and proceed to enter the payment directions.

Figure 14.61 The tools available to customize a report look very similar to those used to customize a form. (Used with permission from Microsoft)

While still on the label, select the Label option Format and adjust the font, size, and color within the font section. Within the label, font styles, sizes, and colors can be changed from the ribbon, as in all Microsoft Office products (Figure 14.62).

Figure 14.62 Text contained on a form can be adjusted by size, font type, and others similar to changing fonts in Word. (Used with permission from Microsoft)

Once complete, review the report in Print Preview View. Make sure the message is displayed in the correct position and with the correct formatting. You can repeat these steps for any other information that may be standardized on the report (e.g., contract guidelines or customer service contact information).

Adding Page Numbers

Open the report in Design View or Layout View. On the Design tab, in the Header/Footer group, click Page Numbers. When the Page Numbers dialog box appears, choose the format, position, and alignment that you want (Figure 14.63). Note: If you do not want a number to appear on the first page of the report, be sure to uncheck the option to clear the Show Number on the First page checkbox. When you are done, click OK, and the page numbers will be added to the report. (To see how the page numbers will look when the report is run, switch to Print Preview.)

Figure 14.63 (a) In the Header/Footer command group, page numbers can be added to multi-page reports. (b) Just as in Word, you can determine the formatting and location of the page number on the report. (Used with permission from Microsoft)

Adding Logos and Images

In the example of the customer invoice, a company logo helps clearly represent the company’s ownership and brand. Beyond simply using font styles and colors to distinguish your company, a company logo can make reports look professional. Note: Before finalizing a report that may go out to a large audience, be sure to check with your company’s guidelines on placement, size, and color guidelines for logos. Larger companies often offer a marketing toolkit to help guide employees in the use of logos.

To better understand the use of logos, we will walk through steps for adding and modifying a static image on a form, using our clothing company example.

First, choose the image that you want to use as the logo for the clothing company. Ideally, this image has been given to you or made available. Company logos are usually saved as a JPEG or PNG file type. Open the report in Design View when you are ready to add the logo. Select the Image button in the Controls section of the Design tab on the ribbon. This will add an image control to the report.

Resize the image control to the desired size by dragging the edges of the control box. Right-click on the image control and select Properties from the context menu. In the Properties window, go to the Picture property and click on the ellipsis button (...) to select the image file that is the saved logo.

In the Picture Size Mode property, choose the Clip option to ensure that the image is displayed at its original size. Save the report once complete. You can also modify the appearance of the image by adjusting the properties in the Properties window. For example, you can change the border style, color, and thickness, as well as the background color of the control box. In this example, shown in Figure 14.64, the logo was placed in the center of the invoice header section, replacing the company name.

Figure 14.64 Company logos are an important part of reports such as invoices sent customers and are easily added in the header. (Used with permission from Microsoft)

Now it is time to print your report. Before you do, make sure you are happy with the final version. Remember that although forms are designed for users, reports are for audiences and stakeholders. It’s important to consider whether your message and data are clearly presented and whether the report meets all company guidelines for publication.