Ms Access Report
The report provides the user a way to view, format, and create a summary or notes from the information that is stored in your Microsoft Access Database.
In the database, the information is stored in tabular format. It is comparatively difficult to draw conclusions directly from the tabular structured data. The report which is generated is comparatively easier to understand. The user can create a simple report of total sales and the total profits or loss occurred during the current fiscal year. It can include various details such as the product that grossed the maximum profit and the product with the least sales etc. Anyone can easily read the report to learn these conclusions. It saves both time and effort.
In this article, we will first learn the importance and overview of the reports in MS Access. Then, we will discuss the parts of the report and learn how to implement the report in MS Access.
Further, we will perform various operations such as sorting, grouping, or summarizing the data.
Note: The user can only implement reports in the Microsoft Access Desktop Databases only. The web application of MS Access does not provide support for reports.
Various Application of Report
There is the various application of reports. In MS Access Database, the report allows the user to represent the information to multiple people, especially people with non-technical backgrounds from the database for any of the following purposes:
- Display the summarized result drawn from the information stored.
- Capture and archive the snapshots of information.
- It can be used to provide details of a particular record in the data.
- It allows the user to create labels.
Parts of a Report
MS Access allows the user to create two types of reports. The two types of reports are
- Unbound Report
- Bound Report
Unbound Report: These reports are not bound to any database or Structured Query Language. These reports themselves don't represent any data.
Bound Report: These reports are associated with a table or a database. These reports are used to present the data of the data source in a simpler and summarized manner.
Here, we will work on the bound report that has a data source associated with it.
The reports in MS Access are divided into different sections. The user can use the View from the Design View Menu in the application. If you want to use the reports efficiently with the MS Access Database, then you must understand this topic . This can help you create better reports.
For Example, the section of calculated control determines the process and steps for calculating the results in MS Access. Each section has a specific position and use in the report.
The following list is a summary of the section types and their uses:
|Name of the Section
||The position of the section in the Print view
||Application and use of the sections
||It is located at the beginning of the report.
||Since the header is the first information that will be visible to any user, this section usually constitutes the content displayed on the report's cover page. It may contain the details such as the logo of the concerning organization, the title of the report, or the date when the report was drafted if the user uses the calculated control section for implementing the Sum aggregate function in the report header. The aggregate function will compute the sum of the entire report. Then, the sum after computation will be printed before the page header. Otherwise, the page header is located at the top of every page in the report.
||It is located at the top of every page of the report.
||The page header can be used for various purposes. It can store the title of the report.
||A report can have multiple groups. The user can make groups in the reports. The group header is located at the start of each new group of records.
||The user can use these sections in the group header to print the group's name. For Example, if the user wants to group the sales of a particular product in different regions. Then the user can use the name of the product and use it in the group header. Then to compute the sum of the grouped data, the user can apply the Sum aggregate function in the group header. The user can place the calculated control in the group header, and the computation will be made for the current group. There are different groups in the report, and each group can have a different name in the group header of the report. The number of group headers will depend upon the number of groups created by the user or the level of groups used in the report.
||The detail section is available for each row in the record source.
||This is where you place the controls that make up the main body of the report.
||The footer marks the end of the page. The Group footer is located at the bottom of each group of records.
||The group footer is generally used to store the summary of the data. This section can be used to make notes from the information. The user can implement and create multiple groups in the same report. Each group can have a group footer. The number of group footers depends upon the level of grouping the user has performed.
||The page footer section is located at the bottom-most part of the page.
||Generally, the page footer is used to print the page number in your report. The footer can also contain the per-page information or other details such as total word count.
||The report footer has located at the end of the report.
Note: The Report View is located differently in the different Views in the Access. When you open the report in the Design View, the report footer is located below the page footer. However, in the rest of the View or when the user prints, the report footer appears above the page footer. The report footer is located just below the last group header or in the last detail line on the report's final page.
|Since the report footer is located at the end of the report, it can be used to print the final results after the computations. It may be used to store the notes drawn from the reports. It may include the summary of the entire report.
When working with MS Access, the user must prefer to normalize the database and each entity, and the relationship between the entities should be well defined before creating the report. It is relatively easier to make the report from the data source if the structure is well defined. Therefore, it is better if the user plans and designs the database.
Creating A Report in MS Access
To create reports in the Access desktop database, then the user is required to follow the given steps:
Step 1: The user must choose the source of the data that will serve as the record source for the report.
Points to remember when choosing the record source:
- The source of the report can be an entire database or a table, or it can also be either a name or an embedded query.
- The resource should include all the fields and values you want to display in the report.
- If the source exists and is a table or a query in the Access, choose the record source. All you need to do is select the particular table or query in the navigation pane.
- But, if the source does not exist, then the user is required to follow the given steps:
- The user can move to the next step without selecting any report. And at the second step, the user must use the Blank Report tool.
- The user can create a source for the report. The user is required to select the source from the Navigation Pane. After selecting the source, you can move to the next step. You can create tables or the query that include all the information you want to add to your report once the source is created.
Step 2:The step involves choosing the appropriate report tool.
- MS Access provides several report tools that allow the user to perform various functions when creating the report.
- The report tools are listed in the Create tab of the ribbon. The Create tab of the ribbon is located in the Reports group.
Below is a list of tools in the report and a description of these tools.
||The tools allow the user to create a simple, tabular report. The report includes all the fields that were in the record source that the user chose in the previous step.
||The tool allows the user to add the fields and controls in your report. The report design opens a blank report in the Design View.
||The tool is used when there is no existing source for the report. The blank report will be opened in Layout View. The user can add the necessary fields to the report.
||The report wizards open a prompt menu that allows the user to specify the fields. It is used to implement the sorting and grouping of fields in the reports. The wizard also contains the layout options.
||It opens a wizard from where the user can choose from a range of standard and custom labels. It also enables the user to select specific fields that the user wants to add to the report. It can also determine how the fields will be sorted.
Steps to Choose the appropriate report tool.
- Click on the tool that you want to implement in your report. If, after selecting the tool, a wizard prompt appears, then the user needs to follow all the instructions and click on the FINISH menu at the end.
- In MS Access, the report will be opened in the Layout View.
- The user can format the report to change the appearance of the report and ensure it suits the need of the user.
- The user can also change the size of various fields and labels in the report by dragging the edges until they are of suitable size.
- You can also move the field from one location to another in your report; to move the field, follow these steps:
- Select the field you want to move and drag it to the desired location. If the selected field also contains labels, you need to drag the labels to the location too.
- To perform formatting in a particular field, the user needs to select the field by right-clicking the cell of the field a shortcut menu will appear. The shortcut menu will display a list of operations that enable the user to perform formatting on the selected fields. You can merge, split or delete cells using the menu.
- The MS Access also performs additional features that can help the user increase the report's readability. These features are described in the sections. They also include functions that make the report more attractive.
Performing Group, Sort, or Total in the Report
- The most effective method to perform group sort or total operation on the database report is right-clicking the field for which you want to perform grouping, sorting, and total. After clicking the fields, the user must select the operation from the shortcut menu.
- If you don't want to use the shortcut menu, you can directly implement these three operations using the Sort, Total or Group Pane. They appear when the report is executed in the Layout or Design View.
- If the Pane for Grouping, Sorting, and Total is not already open in the Design Tab, then the user can choose the Group and Sort option from the Grouping and Totals group.
- To Group or Sort the data in the report, the user needs to click the Add a Group or Sort option, and once you have selected the options click on the fields for which you want to perform the groping and sorting.
Highlight Data With Conditional Formatting
When using the report in MS Access, the user can also highlight the important points in the report. The user can highlight the stored data and add conditional formatting rules for different control or group of controls. The users can implement the data bars if the user wants to perform a comparison between different groups in the report.
Adding Conditional Formatting to Controls in the Report
- If you want to begin, the conditional formatting opens the report in Layout View.
- To open the Layout View, the user needs to Right-Click on the Navigation Pane and, from the menu, select the Layout View.
- Click on the controls in which you want to implement conditional formatting.
- From the format tab, click on Conditional Formatting.
- For Selecting multiple controls simultaneously, the user can press the CTRL key while selecting the controls.
- Once you have clicked the Conditional Formatting, The Rules Manager dialog box will appear on the screen. Click on New Rule in the dialog box.
- It will open the New Formatting Rule dialog box, and from there, select a valid value for a rule type:
- Follow the given steps to create a rule:
- If you want to implement a rule that will be evaluated individually for every record, then either uses the Check values from the current records, or you can use an expression instead.
- Suppose you want to implement a rule that will perform a comparison between the selected records using data bars. Then select the Compare to other record option.
- Once you have selected the rule type, you can write the rule description. To type the rule description, use the Edit rule description from the menu.
- Specify the rule for which you want to implement the formatting. The user must not only specify the rule but also describe when the formatting should be applied and what formatting should be implemented in control.
- Once you have mentioned the above details, click on OK.
- If you want to add more rules to the control or the sets of controls, use the same steps to implement the next rule again on the controls.
Customizing the Color and Fonts of the Report
- Right Click in the Navigation Pane, from the Views in the Pane,
- Select the Layout View. This will open the report in the Layout View in your application.
- In the view, the Design tab will appear. In the tab, Select the Report Layout Tools options.
- From the options, select the Themes. It will display various themes available that can be used in your report.
- To see the effects before implementing the theme in your report. Move the cursor on the theme you want to implement. By doing so, you can preview the effects of the themes.
- Click on the themes and then save the changes implemented in your report.
- From the Design tab, you can change the color and fonts of the text in your report by choosing the options from the Colors and Font gallery.
Add a Logo or Background Image to your Report
The MS Access allows the user to add a logo or background image to your report.
Remember: If you update the image in your report at any point in time, the application will automatically update the image where the image is implemented in the database.
The user is required to follow the given steps to add or remove the image from the report:
- To add or remove the image in the report user must ensure to open the report in the Layout View.
- Right-click in the Navigation pane and select the Layout View from the options.
- Once the report is opened in the layout view, click on the part of the report where you want to add the image or the logo.
- Click on the Design tab in the Header/ Footer group. Then click on the logo.
- It will open a Navigation window. From the window, navigate to the image you want to add to the report and click on that image. This will add the image to the previously selected position in the report.
- Now the steps to remove the image that was added in the previous image.
- The user can delete the image from the report by right-clicking the image and selecting Delete from the shortcut menu.
- The following are the steps to add a background image to the report:
- Right-click on the Navigation Pane, and from there, select the Layout View from the available options.
- In the Layout View, click on the format tab in the Background group. From the menu, click on the background image.
- This will open the image gallery from which you can choose the image. You can also choose an image from the system by clicking on Browse. Select the image you want to add as the background. Then, click on OK.
Use Preview or Print in a Report
Using Preview in a report
- From the Navigation Pane, right-click on the report. And then, you click on the Print Preview from the menu. There are various commands in the Print Preview tab. The commands allow the user to perform the given functions:
- You can print the report using Print Preview.
- The user can also adjust the orientation, layout, and size of the pages in the report.
- You can also use the functions to check multiple pages simultaneously. The user can Zoom in or out in the print view.
- It also allows the user to refresh the data in the report.
- The user can also change the report format by directly using the export option in Print Preview.
- After performing the listed function, the user can close the Print Preview.
Print the Report
- Although previewing the report before actually printing it is preferred as it reduces the chance of error in the report, you can directly print the report without reviewing it. Follow the given steps to print it:
- Open the Report in the Navigation Pane. Then, Right, click on the report; from the list of options, select Print. Then it will print the whole report using the default printer connected to the system.
- The other method is selecting the Print option from the File tab. It will open the Print tab, where the user can select the number of pages to be printed and the printer from which they want to print the report. It also provides the option for selecting the type of Print that is high definition, color, or black and white.
- If not selected or modified, the entire report is sent to the default printer, and other default settings are applied. Then click on Print.