Create Matrix Report in Access Report

This Microsoft Access tutorial explains how to create Matrix report in Access Report using Crosstab Query and Expression.

Access Matrix Report

Matrix Report is like an Excel Pivot Table, where you can place fields in row and column, and then apply Aggregate Functions on a field value such as Count, Sum, Max. In newer version of Microsoft Access, Pivot Table function has been removed, you can only create Matrix Report through Crosstab Query, or write expression in Access Report.

In this post, I will demonstrate how to use both methods to create Matrix Report in Access Report.

Create Matrix Report in Access Report – using Crosstab Query

Suppose you have created a Crosstab Query below (refer to my previous post)

 

Run or select the Query, navigate to Create tab > Report

 

A report is generated using the Crosstab Query “SUM” as Report Record Source.

 

Do some resizing of text box and formatting of fonts, now the Report looks exactly the same as Crosstab Query.

 

This is the simplest way to create Matrix Report in Access Report. However this Report lacks flexibility, because you cannot do further Group By or calculation, unless you change the Crosstab Query.

Create Matrix Report in Access Report – using Expression

Create a new Report. Instead of using Crosstab Query as Record Source of Report, use the original table “payroll_history” without any manipulation, because you can do everything in Expression.

Group the Report by Department.

Under Department Header, for January Text Box, type Expression

=Sum(IIf(Month([Pay_Date])=1,[Amount],0))

For Feburary, type Expression

=Sum(IIf(Month([Pay_Date])=2,[Amount],0))

Continue with the same logic until December, for Total Text Box,  type Expression

=Sum([Amount])

Now Run the Report, you have the same result as using Crosstab Query as Record Source.

 

Because this Report is using payroll_history as Record Source (the original data source), you can do more Group By, do different sorting and do more IIF statement in the Expression than using Crosstab Query.

 

Calculate Percentage of Total

Instead of displaying the total amount for each month, we can display percentage of total.

Using the previous example2 Report

 

We just need to divide the amount of each month by the total amount.

Under Department Header, for January Text Box, type Expression

=Sum(IIf(Month([Pay_Date])=1,[Amount],0))/Sum([Amount])

For Feburary, type Expression

=Sum(IIf(Month([Pay_Date])=2,[Amount],0))/Sum([Amount])

Continue with the same logic until December, for Total Text Box,  type Expression

=Sum([Amount])/Sum([Amount])

Select the Text Box as below, and then change the Format field to Percent, and type 1 for Decimal Places field.

 

Run the Report

 

 

Wyman W
Wyman is a Business Analyst based in Hong Kong, specialized in business analysis, project management, and also creating custom Function and Sub solutions, and is proficient in report automation with Access.

He is also a:
- Microsoft Most Valuable Professional (Excel)
- Microsoft Community Contributor
- Microsoft Office Specialist in Access / Excel
- Microsoft Specialist in MS Project
- Microsoft Technical Associate
- Microsoft Certified Professional
- IBM SPSS Specialist

Leave a Reply

Your email address will not be published. Required fields are marked *