Microsoft Access 2021: Advanced Reports

Name: Microsoft Access 2021: Advanced Reports
Author: John Varan

Learn about conditional formatting and subreports.

Preview the first lesson free — get full access to all 4 lessons.

Course: On-Demand

Hello, welcome to Knowledge City's course on Microsoft Access 2021, Intermediate. My name is John Veron, and in these lessons, you will learn about advanced reports. In this lesson, I will show you how to use calculated values in reports. Using calculated values in reports can be useful if you only need the value calculated for that report and not generally, such as in the table or for a saved query. In fact, calculated values are probably used more often in reports than anywhere else. Usually they are needed when compiling information for distribution in reports. Using calculated values in reports is very similar to using them elsewhere with just a few small differences. Let's create an example report that uses calculated values. We're going to use a Northwind database to create a report which shows the total price per order for each customer. The total order price for this example will consist of the unit price per product times the quantity order plus the shipping cost of the order. Let's go to the Create menu and click on Report Wizard. We're going to use a report wizard to do most of the work of creating the report even though it can't handle calculated values. We will add the calculated value afterward. In the wizard, we're going to add a few fields from several tables. From the Customers table, let's add the last name and the first name. From the Orders table, let's add shipping fee and from the Order Details table, let's add unit price and quantity. We can just keep everything as default. We want to go into the Report Designer. Once the report is created, select the unit price and the quantity text boxes and we want to shift these over to the left just to make a little room room. In the Control menu, choose Text Box and place it to the right of where the unit price and quantity are. For the value in this label, enter an equal sign which is how you let access know that this will be an expression or a formula instead of static text. This is similar to how formulas work in Microsoft Excel if you're familiar with that. After this equal sign, type the following. In square brackets, unit price times quantity plus shipping fee. This formula should calculate the total cost, so let's run this report. This calculation looks correct but we can clean the report up a little bit, so let's go back into Design View. Let's add a dollar sign in front of that since it's a price. To do that, we put the dollar sign in quotes and then we have to separate it from the rest with an ampersand which means to append the two together. While we're here, we can also change the border style of this text box to transparent so that it doesn't look like something that has to be entered in for the user and it will look a little nicer. Let's check out this report again by going to Report View. There we go. As you can see, the calculated values can be pretty useful when it comes to reports. Thanks for watching. Stay tuned for the next lesson where I will show you about conditional formatting in reports.

Intermediate Provider : John Varan 4 Lessons · 19m in English

Course Description

Subreports can be used to drill down into more specific information about certain pieces of data within a main report. Conditional formatting automatically highlights certain pieces of data, and can also provide feedback to users through various functions controlled by the data in your report. Reports that query external data sources take information from sources like Microsoft Excel, text files, or other databases and merge it with your Microsoft Access data, creating one combined report. Generating calculated values in reports is useful for showing data in reports that is not stored in your database but can be calculated from various pieces of stored data.

This module expands on your knowledge of using reports in Microsoft Access. You will learn how to write subreports, which are secondary reports embedded inside of a main report. You will also explore how to use conditional formatting in reports. Along with subreports and conditional formatting, you will learn to create reports that query external data sources and generate calculated values in reports.

What You'll Learn

Write subreports embedded inside a main report to drill down into specific data
Apply conditional formatting to automatically highlight data and provide user feedback in reports
Create reports that query external data sources such as Microsoft Excel, text files, or other databases
Generate calculated values in reports from data stored across your database
Merge information from external sources with your Microsoft Access data into one combined report

Key Takeaways

Subreports are secondary reports embedded inside a main report and let you drill down into more specific information about certain data.
Conditional formatting automatically highlights certain pieces of data and can provide feedback to users through functions controlled by the data in the report.
Reports that query external data sources pull information from sources like Microsoft Excel, text files, or other databases and merge it with Microsoft Access data into one combined report.
Generating calculated values lets reports show data that is not stored in the database but can be calculated from various pieces of stored data.
This module expands on existing knowledge of using reports in Microsoft Access.

Frequently Asked Questions

What will I learn in this course?

You will learn to write subreports inside reports, use conditional formatting in reports, create reports that query external data sources, and generate calculated values in reports.

What topics do the lessons cover?

The lessons cover Calculated Values, Conditional Formatting, Subreports, and Reports with External Data.

Is this an introductory or advanced course?

This is an advanced module that expands on your existing knowledge of using reports in Microsoft Access 2021.

What external data sources can these reports use?

Reports can query and merge information from sources such as Microsoft Excel, text files, or other databases with your Microsoft Access data.

What skills does this course build?

It builds skills in report writing, external reporting, Microsoft Access, Microsoft Data Access Components, Microsoft Reporting, and open reporting applications.