Skip to ContentGo to accessibility pageKeyboard shortcuts menu
OpenStax Logo
Workplace Software and Skills

13.5 Creating Reports in Microsoft Access

Workplace Software and Skills13.5 Creating Reports in Microsoft Access

Learning Objectives

By the end of this section, you will be able to:

  • Understand why reports are useful
  • Create and edit a report using the Report Wizard
  • Customize a report by adding images and text

Databases are useful only if they enable us to transform data into information that will be ready for use when it is needed. Although queries are helpful in extracting the data that we need and even transforming it into actionable information, most decision-makers will need more refinement, formatting, and preparation before the data is ready for use.

Recall that a report is a formatted display of information designed for a specific use case or project. Reports do more than summarize information; they also create the foundation for making sound decisions. When we combine queries with formatting, we create reports that we can use in our work.

Using Reports in a Database

Reports are a cornerstone for business decisions. We can generate reports within a database for a specific request, and we can also set up reports to provide summaries and information that we will need on an ongoing basis. Access enables us to set up reports (e.g., a financial, sales, or summary report) that we can then access as necessary for decision-making. Companies routinely run reports on key performance indicators (KPIs) that show an organization’s effectiveness, efficiency, quality, economics, project performance, resource use, and personnel and workforce performance. Companies also routinely look at revenue, profit, and customer and employee satisfaction. Because tables filled with data do not adequately describe KPIs, businesses rely on reports as part of their evidence-based decision-making.

Tables are extremely efficient ways of storing data, and using a relational database with connections across tables is an effective way of building information storage. Unfortunately, however, tables do not display information in ways that people easily understand. We can manage this by creating reports—formatted, attractive displays of information. Reports use combinations of labels, values, and even graphics to present the results of a query in a way that supports action. Once designed, a report can easily be reused or copied and revised. Like other objects such as queries and forms, reports will show the most up-to-date information each time the database changes. Among the many reasons to generate reports are the need to create summary reports, to save snapshots of data, and to provide detail about specific customers or products.

Consider this example. Anna, an analyst in the sales and marketing group at WorldCorp, has been asked to get data ready for a meeting with sales representatives. The meeting will focus on products that are on hold, and the representatives will need to identify wholesale companies that may be interested in these products. Anna begins by determining what the sales representatives will need for the meeting and the level of detail they will want to have available for decision-making. She then looks at the available data and decides how to create the report. Anna’s final report includes two sets of information—the list of on-hold products and the list of wholesalers who have shown an interest in those products. Therefore, she creates two versions of the report:

  • List of on-hold products with a list of interested wholesalers for each product
  • List of wholesalers with a list of on-hold products that may be of interest to each wholesaler

Anna sends the final report to the person in charge of the meeting for review and comment.

Tips for Effective Reports

The way you design a report will depend on the data it will include, the audience that will use the report for decision-making, and the way the data will be used. Reports can be as brief as a few lines of summary text, or they can be highly detailed spreadsheets or within datasheets with many rows of records. The key to building a useful report is communication—your communication with the audience that needs the report, so you understand them, and the communication of the information to the audience so they can use it.

Effective reports should utilize the headers and footers of a report and its pages to aid the reader when navigating the report. Headers and footers should contain useful information such as the report title, author, and page numbers. This makes it easier for a report reader to quickly find the information they are looking for across multiple reports.

The goal of a report is to eliminate as many individual lines of information as possible and to simplify information through summary wherever you can. In general, consider the following when preparing effective reports:

  • Minimize line-by-line data
  • Use labels to explain the data that is shown on the report
  • Use formatting to designate titles and other labels
  • Incorporate white space to ensure readability
  • Test with possible audience members to be sure the report makes sense to the final audience
  • Focus on answering specific business questions generated by the person or people who request the report

Organizing a Report

A report can be flat, with one record per line, or you can use grouping to further organize the data. When designing a report, think about organizing your data as follows:

  • Sorting to order your data by an important field (e.g., Name), when you are discussing customers or sales region
  • Grouping records based on a field, such as active versus inactive products, regions that met specific targets versus those that are lagging in meeting goals, or geographical area
  • Totaling groups of data, such as sales figures, to show both the detailed figures (e.g., an individual salesperson’s sales figures) and the figures for the overall region

Access will allow you to quickly add and preview sorting, groups, and totals to ensure the report communicates what you need it to for the audience you are working with.

Creating and Editing Reports with the Report Wizard

WorldCorp is interested in creating a summary of their recent sales data for their latest product line. The report should include useful information such as which products have the highest total sales, which products have the highest net profit, and which customers are purchasing the most products. WorldCorp could create this report from a blank canvas, but it would be much easier to generate a report using the Report Wizard. This is a small program that will walk you through the steps of setting up a report, which you can then modify to meet your needs.

Using the Report Wizard is usually an efficient and easy way to start a report. Most reports will need some form of customization, but the Wizard will provide a good starting point and will save steps by adding fields that are bound to the database.

Creating a Report

To start a report with the Report Wizard, click on the Create menu in the ribbon and then choose the Report Wizard command from the reports section. The Wizard will walk you through each question before creating your report.

Step 1: In step 1 (Figure 13.40), you can set the table(s) and/or queries you want to work on within the report. This gives you flexibility, as you can use data from more than one table or query in your work. (Hint: Consider setting up a query for your report before starting the report. This will enable you to check the data before setting it up for display.) Once you have chosen and added tables, queries, and fields to the report (use the >> button to move fields to the selection box), click Next to move to the next screen in the Wizard.

Report Wizard pane reads: Which fields do you want on your report? Selections include Table/Queries (Query: ProductList with client information), Available Fields (ProductID), Selected Fields (blank). Arrows visible between Fields panes.
Figure 13.40 The Report Wizard allows you to select fields to include in the report. (Used with permission from Microsoft)

Step 2: In step 2 (Figure 13.41), you can choose grouping for your report. (Skip this step if grouping does not make sense for your report.) A group is a heading that organizes data with something in common. For example, you might group data based on the region a customer is assigned to. When used as a group, all customers belonging to a specific region will appear together. Once you set up grouping, you can click Next to continue.

Report Wizard pane reads: Do you want to add any grouping levels? ClientID selected. Product ID pane lists: ClientID, ClinetName, ClientMailingAddressAll. Left, Right, Top/Bottom arrows with Priority in between visible between panes.
Figure 13.41 In this example, grouping by product ID has been used, creating a report that first lists each product, with the client info of every client who has purchased that product listed under the ProductID. (Used with permission from Microsoft)

Step 3: Step 3 of the Report Wizard asks how you want to sort your records, as Figure 13.42 shows. Access allows you to sort by up to four fields in the designated order. For example, to create a report that fits the needs of your audience, you may want to sort a set of records by sales region in alphabetical order and then sort further by product ID. Once you set up sorting, you can move to the next step in the Wizard.

Report Wizard window reads: What sort order do you want for detail records? Panes available for populating (one displays ClientID) with Ascending buttons at right. Next button selected at bottom.
Figure 13.42 Using the ClientID field with the Report Wizard allows you to sort records in ascending order, from smallest to largest or from A to Z. (Used with permission from Microsoft)

Step 4: After you have set the sort order, the Wizard will ask you to choose the layout for your report (Figure 13.43). You can also set the orientation and ask Access to adjust all fields to make sure they fit on a page.

Report Wizard reads: How would you like to lay out your report? Stepped selected in Layout, Portrait selected in Orientation. Adjust the field width so all fields fit on a page (selected).
Figure 13.43 Setting the report layout in the Report Wizard allows for limited customization of the final report. (Used with permission from Microsoft)

Step 5: The final step in the Wizard allows you to set a unique title for your report and then choose whether you want to provide the results or go into design to modify the report (Figure 13.44). Usually, viewing the report will be useful, as it will show you what you want to change. When you are done, click Finish to continue the work.

Report Wizard reads: What title do you want for your report? (product and then client with mailing info). Do you want to preview the report/modify the report’s design? (Preview the report (selected)).
Figure 13.44 Setting a name for the report is the final step before Report Wizard generates a preliminary report. (Used with permission from Microsoft)

Reviewing a Report

When you double-click on a report in the navigation area, the report will open with current data displayed. Let’s look at a simple report in Figure 13.45: a listing of all product categories.

This report, named ProductCategoryList, was created quickly by clicking on Create and then Report.

ProductCategoryList selected from Queries in All Access sidebar. Report displays with header and Columns for ProductCategoryID and Description (populated with information in cells). Date/time listed at top.
Figure 13.45 The report generated by the Report Wizard is well organized and easy to read. (Used with permission from Microsoft)

The title at the top of the report shows the date the report was run, followed by two columns that list the product category ID and the description. This report is set up by Access and has not been modified. In Design View (Figure 13.46), this report appears differently, with the design and grid available for modification. This report is usable as is but taking a few minutes to customize it will make it more useful for your audience.

Property Sheet button selected from Tools (Report Design tab). Property sidebar visible with search bar and tabs for Format, Data, Event, Other, All (selected). Two columns populated with selections for formatting follow.
Figure 13.46 Access allows you to fully customize the layout and content of a report depending on your specific needs. (Used with permission from Microsoft)

In Design View, the report header appears on the first page and includes the title and dates of the report. This is followed by the page header which appears on each page and includes information useful to the report user, such as page number or report author. The report detail is the area where data from the database is displayed for each record. Each record will show the product category ID and description. The report has a page footer, which displays useful information, such as the page number, and appears on each page. The report footer shares the number of pages in the entire report. As you build reports, you will likely switch back and forth between the Report View and Design View to change elements such as spacing and formatting so that your report is useful for your audience.

Customizing Reports

Although Access does a nice job of starting a report, you will likely want to modify reports before using them. Following are some of the changes you may want to make:

  • Changing the size of fields (e.g., shortening them)
  • Formatting the text of the report (e.g., by adding boldface)
  • Shifting text boxes to lay the screen out more effectively

Page Layout View

Page layout view gives you a simple way to make changes to the overall appearance of a report. Changes made to a field in page layout view will be applied to all fields of the same type (e.g., if you boldface a field, all fields of the same type will also be bold). Page layout view displays live data while you make your changes, which makes decision-making easier.

To enter Layout View, click on the View command on the Home tab while you have a report open on the screen. You can preview the report by changing to the report view. When you are finished working, save and close your report.

Design View

The Design View screen has more options for customizing your report. To switch to Design View, use the View command on the ribbon.

Design View gives you a layout grid for your form and the ability to move each label and field independently, change the formatting of the text, and even change the attributes for each element on the report.

A common change in Design View is modifying the size of the field on the screen. For example, you may find that Access cuts off some of the label or content in a field. You can modify the size of the field or label by clicking on the element and then using the selection handles on the left and right sides to stretch or shrink the element.

You can also move elements manually by clicking and dragging them or by clicking on multiple items and using the commands on the Arrange tab on the ribbon. These commands allow you to move items up or down, arrange them together, or control how the elements are arranged on the report itself.

Adding an Image or Custom Text

You may want to include a logo or even a disclaimer as an image in a report. These images can add information or (as in the example of a logo) provide identification. Images typically appear at the top or bottom of a report. Remember that if you place an image in the detail section of the report, it will repeat for every line. (This is useful when you want to have each line start with a symbol or logo.)

To add an image in Design View, click on the Form Layout Design tab and choose the image that you want to add. You can also add any image to the page by clicking Insert Image (see Figure 13.47) and then locating the image file. Again, be aware that when images are placed in the detail section of the form, they will repeat for every line. If you would like the image to appear only at the top or bottom of a page, place the image in the page header or footer.

You may want to add elements that are not strictly images but display on the report (e.g., titles, dates, and times). Note that these items will be displayed on the report but will not be stored in the database; they will change each time you open the form.

Insert Image button in Controls toolbar (Report Design tab) selected. Categories in a form are displayed in design view.
Figure 13.47 You can export selected Access objects to an Excel worksheet. (Used with permission from Microsoft)

When you create and modify a report, Access will prompt you to name and save it. Each time you modify an existing report and close it, Access should prompt you to save it. You can save manually by right-clicking on the name of the tab in the work area and choosing the Save command.

To close a report that is open in the work area, click on the X next to the tab name.

Citation/Attribution

This book may not be used in the training of large language models or otherwise be ingested into large language models or generative AI offerings without OpenStax's permission.

Want to cite, share, or modify this book? This book uses the Creative Commons Attribution License and you must attribute OpenStax.

Attribution information
  • If you are redistributing all or part of this book in a print format, then you must include on every physical page the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
  • If you are redistributing all or part of this book in a digital format, then you must include on every digital page view the following attribution:
    Access for free at https://openstax.org/books/workplace-software-skills/pages/1-chapter-scenario
Citation information

© Apr 15, 2024 OpenStax. Textbook content produced by OpenStax is licensed under a Creative Commons Attribution License . The OpenStax name, OpenStax logo, OpenStax book covers, OpenStax CNX name, and OpenStax CNX logo are not subject to the Creative Commons license and may not be reproduced without the prior and express written consent of Rice University.