Access 2016: Create a Report

Access has several methods for creating reports. Any user can create a report quickly in Access, whether they're a novice or an expert.

In Access 2016, you can create a report from a table or query with the click of a button. You can also use the Report Wizard to walk you through the process. And more experienced users can start with a blank report and build it exactly as they wish.

Create a Report based on a Query

We'll create a report based on the Albums by Artist query we created previously.

The interesting thing with this query is that it asks for user input — the user tells the query which artist they'd like to see albums from. However, the query doesn't display the artist name in its result set.

So, we'll need to display this parameter on the report too — otherwise the reader might not know which artist the albums belong to.

  1. Screenshot of the Report button highlighted.

    Create the Report

    Ensuring the report Albums by Artist is selected in the left navigation pane, click the Report button in the Ribbon.

  2. Screenshot of the parameter prompt.

    The Parameter

    Access immediately attempts to create a report based on the selected query.

    But because it's a parameter query, you need to enter a parameter before the report can be displayed.

    In this case, enter the name of an artist and click OK (or just click OK to display a blank report).

  3. Screenshot of the newly created Report.

    The Report

    The report displays the fields in columns, with the field names as column headers.

    It has basic formatting applied, and this can easily be changed using the various formatting options.

    But there's one problem with this report. It doesn't display the artist name. If a stack of reports were printed or emailed to another person, they'd have no idea who each report is displaying albums for.

    So next, we'll add the artist name to the report.

    Save the report by right-clicking on the report's tab and clicking Save.

Add the Query Parameter to the Report

The problem with the above report is that it doesn't display the artist name. So there's no indication of which artist the albums belong to.

This is because when we wrote the query, we chose not to display the artist name in the results. If we did, the artist name would be repeated for each album. If there were say, 20 albums, the artist name would be repeated 20 times.

Now that's OK for the query. The user who runs the query will know which artist they entered.

But it's different with reports. The report could be one of many reports sitting on someone's desk (or in their email inbox). They're probably not going to know who each album belongs to without the artist name being displayed somewhere on the report.

To address this problem, we will need to work out how to add the query parameter to the report.

And that's exactly what we'll do now.

  1. Screenshot of the Report in Design View.

    Switch to Design View

    Switch to Design View by clicking on the Design View icon in the bottom right corner of the screen.

    Close the left navigation pane by clicking its « icon to allow more room for the report.

  2. Screenshot of the Report in Design View.

    Increase the Height of the Report Header

    Click on the bottom edge of the report header and drag it down to make it a little higher.

    We're doing this so that we have enough room to display the artist name under the report title.

  3. Screenshot of the Report in Design View.

    Select a new Text Box Control

    From the Design tab of the Ribbon, click the Controls button and select the text box option.

  4. Screenshot of the Report in Design View.

    Add the Text Box to the Report

    You can click and drag to place the text box where you want, and at the size that you want.

    Click in the report header, under the title, and drag it so that it's wide enough to display the longest text that will be displayed.

    The text box will be inserted where you click.

    You can always change the width or height of any element, either by clicking and dragging, or by opening the Properties Sheet and entering a specific width/height.

  5. Screenshot of the Report in Design View.

    Add the Parameter

    Add the parameter to the text box by entering the following:

    =[Reports]![Albums by Artist]![Which Artist?]

    Also change the label to read Artist:

    Alternatively, you can add the parameter into Control Source on the Properties Sheet.

  6. Screenshot of the parameter prompt.

    Run the Report

    Run the report by clicking the Report View icon in the bottom right corner of the screen.

    You will be prompted to enter an artist name (as required by the query).

    Enter an artist's name and click OK to display the report.

  7. Screenshot of the report in Report View.

    The Report

    The report is now displayed, and it includes the artist's name in the header.

    Use some of the features listed below to give the report a fresh new look.

Report Design

Reports can be designed and formatted in a similar way to forms. When you create a report, four new tabs appear in the Ribbon specifically for working with reports.

Screenshot of the parameter prompt.

Report Header & Footer

The report header and footer are displayed only once in the report.

The report header is displayed at the top of the first page, and the report footer is displayed at the bottom of on the last page. If a report contains say, 10 pages, the header and footer will be displayed on all 10 pages.

So, in our example above, the artist name will only be displayed once — at the top of the first page of the report.

Page Header & Footer

The page header and footer are displayed on every page of the report. If a report contains say, 10 pages, the header and footer will be displayed on all 10 pages.

So, in our example above, if we moved the artist name to the page header, it would then be displayed on every page of the report.

Detail

The report detail section is where the actual data is displayed. This can run across many pages if required, and each page will continue where the previous left off.

The Property Sheet

  • Screenshot of the Report in Design View with the Property Sheet open.

    As with other Access objects, you can use the Property Sheet to modify your report's properties. You can modify properties for the whole report, or just for selected elements.

    You can change formatting properties (such as height, width, color, etc), data properties (such as where a control's data is sourced from), event properties (such as when a user clicks on the element), and more.

    The Property Sheet can be accessed by clicking on Property Sheet on the Design tab of the Ribbon, or by right-clicking on an element and selecting Properties.

    The Property Sheet only displays properties for the selected item.

Report Views

Access provides four different views for reports.

As with all other Access objects, you can toggle the view using the buttons at the bottom right of the screen, or by using the View button on the Ribbon.

  • Screenshot of a Report in Design View.

    Design View

    Allows you to view and design the report structure. Doesn't display live data. Instead, it displays any expressions that are used to generate content.

    For example, when you look at the above report in Design View, you can see that various fields use in-built functions to display dynamic data. The Date() function is placed in the right corner of the header to display the current date (i.e. the date the report was generated). Similarly, the Time() function displays the current time. And the Count() function is used in the footer to display a count of the records returned in the report.

  • Screenshot of a Report in Layout View.

    Layout View

    Provides a view that more closely resembles what the report will actually look like to the user. Layout View allows you to edit the report design while it contains live data.

  • Screenshot of the Report in Print Preview.

    Print Preview

    Allows you to see what the report will look like when printed. You can also go ahead and print from this view by clicking the Print button in the Ribbon.
  • Screenshot of the Report in Report View.

    Report View

    Displays the report as the user sees it.