Access has quite a few processes for generating reports. Any user can create a report quickly in Access, whether they’re a beginner or an expert.
With MS Access 2016, you can create a report from a table or query with the click of a button. You may also make use of the Report Wizard to guide you through the method. Similarly, more experienced users can begin with a blank report and build it exactly as they want.
Create a Report based on a ms access query
We will make a report in view of the access query we created earlier — Women’s Tops.
This query will require user input which would tell the query the colour they would like to find from the Women’s Tops list. However, the ms access query does not show the cloth colour in the output, so we will need to show this parameter on the report as well. If not, the reader would not know the colour of the garment.
- Create the Report
Make sure the Women’s Tops query is selected in the left navigation pane, then click the Report button in the Ribbon.
- The Parameter
Access instantly tries to create a report based on the selected query. But since it’s a parameter ms access query, you need to enter a parameter before the report can be shown.
In our example, input the cloth colour and click OK. You may also just click OK to display a blank report.
- The Report
The report shows the fields in columns, with the field names as column headers. It has basic formatting applied, and this can simply be altered using the different formatting options. However, there is a single problem with this report — it does not show the colour of the garment. If a stack of reports were printed or sent to another person, they would have no idea which reports belongs to what colour.
Save the report by right-clicking on the report’s tab and clicking Save.
Add the ms access query Parameter to the Report
The issue with the above report is that it does not show the garment colour. Thus, there is no hint of which colour the reports belong to.
The reason being is that when we created the ms access query , we decide not to display the garment colour in the results. If we did, the garment colour would be repeated for every garment. So, if there were 10 garments, the colour would be repeated 10 times.
Now that’s OK with the query. The user who runs the query will know what colour they entered.
But that is not the case with reports. The report could be one of several reports sitting on somebody’s desk (or in their email inbox). They’re most likely not going to distinguish what colour each report belongs to without the garment colour being indicated somewhere on the report.
To address this issue, we should work out how to add the ms access query parameter to the report.
- Shift 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 the « icon, also known as Shutter Bar Open/Close Button, to allow more room for the report.
- Increase the height of the report header by clicking on the bottom edge of the report header and dragging it down to make it a little higher.
- From the Design tab of the Ribbon, click Text Box from the Controls group.
- After clicking the text box icon, position the cursor where you want to place the text box on the report and click on it. You should see one like the image below.
- You can always alter the width or height of any element. Do this by clicking and dragging, or by opening the Properties Sheet and entering a specific width/height.
- Add the parameter to the text box by entering the following:=[Reports]![Women’s Tops]![What colour?]Alternatively, the parameter can also be added into Control Source on the Properties Sheet.
- Change the label to read Colour.
- Run the report by clicking the Report View icon in the bottom right corner of the screen.
- You will be asked to type in a colour as required by the query. Input a specific colour and click OK to display the report.
- The report is now shown, and it has the colour name in the header.