Archive for 'SQL Origami' Category
SQL Moment of Clairity
17 October 2006I was working on a stored procedure the other day, not one that I wrote originally but one that was causing performance issues in out system. The procedure was taking any where from 40 to 60 seconds to run, which is unacceptable in a web service. This in fact was causing the web […]
Period Matrix
25 September 2004The 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 […]
MS Access Pivots Part II
25 September 2004Pivoting 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.
Activity Table
The table Activity used in this example has the following layout:
AcctID
DeptID
Class
Year
BeginBalance […]
MS Access Pivots Part I
25 September 2004CrossTab 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.
As an example we look at Activity table in the Sample.mdb. If we were asked to show the January activity for each account - department […]