Saturday, September 25, 2004

Period Matrix

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.


MS Access Pivots Part II

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.


MS Access Pivots Part I

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.