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.
-
Create the Report
Ensuring the report Albums by Artist is selected in the left navigation pane, click the Report button in the Ribbon.
-
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).
-
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.
-
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.
-
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.
-
Select a new Text Box Control
From the Design tab of the Ribbon, click the Controls button and select the text box option.
-
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.
-
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.
-
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.
-
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.
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.
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 page header and page 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
-
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.
-
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.
-
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.
-
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. -
Report View
Displays the report as the user sees it.