Access replace Crosstab Query with Expression

This Microsoft Access tutorial explains how to replace Crosstab Query with Expression in order to add multiple aggregate values.

Access replace Crosstab Query with Expression

In previous post, I have introduced how to use Crosstab Query to build a matrix report.

However, Crosstab Query has a limitation that it can only add one aggregate value in the report, which is the Sum of amount in the above example. I also don’t like the fact that I need to define the order of the month in the Property Sheet.

In this post, I will demonstrate how to build matrix report using Expression to replace Crosstab Query.

Build Matrix report using Expression

Using the above payroll history as an example. First of all, create an Expression for January.

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

In the Expression, I put a condition that if month of pay date is 1 (meaning January), then return [Amount], otherwise return 0, and finally sum up all the [Amount]. This is an extremely important technique to place condition on aggregate Function.

Repeat the same for other months, each month is an Expression.

For the Expression of  Grand Total just sum the amount without any condition

Grand Total: sum(Amount)

Now run the Query, we got the exact same result as using Matrix Report.

The best part of using Expression to build matrix report is that we can add as many aggregate Function as we want. Let’s say we also want to see the payroll headcount of each month.

Rename field Jan to Jan Amount, insert an Expression Jan HC after Jan Amount

Jan HC: Sum(IIf(Month([Pay_Date])=1,1,0))

Also press the Total button in ribbon to add total of column (optional). Now we have two aggregate values in the result.

 

To conclude, there are a lot more flexibility to build a Matrix Report in Expression rather than Matrix Report. My personal preference is using Expression over Crosstab Query, unless the requirement is very simple.