TechShorts

The Period Matrix table, Pd_Matrix, is used in calculating accounting period formulas. These formulas include current month, quarter or years activity or balance. These formulas can also be created using standard SQL functions but not all functions are common between SQL Server and MS Access. The Period Matrix has the advantage in that it can be used in any DBMS and it also can be used in table pivoting and folding.

Pivoting a table on multiple columns with a crosstab query gets messy really fast.  To eliminate the need for complicated Multiple Value Field Queries you can use the lowly IIF() function and the obscure CHOOSE() function to pivot a table.

CrossTab Queries CrossTab queries are an easy way in MS Access to pivot tables. They are limited in that you can only pivot on one field and can only aggregate one field.