Skip to main content

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.



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 combination for each year using a crosstab query, we would create the following SQL:

   TRANSFORM Sum(Activity.Jan) AS SumOfJan
   SELECT Activity.AcctID, Activity.DeptID
   FROM Activity
   WHERE (Activity.Class = "actual")
   GROUP BY Activity.AcctID, Activity.DeptID
   PIVOT Activity.Year In (1997,1998,1999,2000);

The TRANSFORM identifies the column to aggregate and the type of aggregation (sum). Other types of aggregation possible are avg, min, max, count, etc.

The PIVOT identifies which column is pivoted on. In this case it is the Year column, the IN clause indicates that we only want to see these four specific column headings. If the IN clause was not specified then all possible values of Year would be used as column headings.

In the Query Builder, the Row Headings indicate which fields to show in the results, while the Column Heading shows the pivot field and the Value is the aggregated field. For a crosstab there is no limit to the number of Row Headings but you need one Column Heading and one Value. Of course with a crosstab you can have only one Column Heading and one Value.

The first eight rows of the result set would be:

AcctIDDeptID1997199819992000
10001000$1,955.00$3,925.00$9,195.00$0.00
10002000$8,540.00$8,090.00$9,620.00$0.00
10002010$9,285.00$5,510.00$1,820.00$0.00
10002020$8,460.00$5,005.00$8,825.00$0.00
10003000$4,605.00$8,355.00$2,265.00$0.00
10003010$6,715.00$8,665.00$5,390.00$0.00
10004000$540.00$9,085.00$7,775.00$0.00
10004010$7,885.00$1,035.00$2,870.00$0.00

As can be seen this is a very simple query, easy to setup and understand. Simply identify the column to pivot on and what column to aggregate and that is it. The problems start to arise whenever more than one pivot value or transform column is needed.

Pivoting on Two Columns - Multiple Value Field Query

Single CrossTab queries cannot pivot on more than one column. However, situations arise frequently where it is needed to pivot on two or more columns. One workaround for this, as documented in the Microsoft Knowledge Base (Q109939), is to build separate crosstab queries using the same underlying table, each pivoting on the same column but showing only specific values from the second column. The final result is obtained by joining the crosstab queries in a Select query. Microsoft refers to this as a Multiple Value Field query.

This method of joining crosstab queries is simple to implement whenever the number of columns to pivot on is small. However, as the number of column pivots increases the number of required joins also increases. Eventually a point is reached where the query is too complex to maintain or even to complex to execute. A rule of thumb should be that if it is needed to pivot on more than two columns then crosstab queries should not be used.

To create the Budget Variance and History report with a crosstab we will need to use a Multiple Value Field query. To accomplish this we use the previous crosstab query for our actual amounts and create the following crosstab for our budget amounts:

   TRANSFORM Sum(Activity.Jan) AS SumOfJan
   SELECT Activity.AcctID, Activity.DeptID
   FROM Activity
   WHERE (Activity.Class = "budget")
   GROUP BY Activity.AcctID, Activity.DeptID
   PIVOT Activity.Year In (1997,1998,1999,2000);

As you can see the only difference is the WHERE clause.

Next we create the select query using both of the crosstab queries as the source. We join on the account and department numbers:

   SELECT
      actual.AcctID, actual.DeptID,

      actual.[1997], actual.[1998],
      actual.[1999], budget.[1999]
      actual.[2000], budget.[2000]
   FROM
      [CrossTab Actual by Year] AS actual
      INNER JOIN [CrossTab Budget by Year] AS budget
         ON (actual.DeptID = budget.DeptID)
         AND (actual.AcctID = budget.AcctID);

In this case since we are currently in the year 1999 we would not have budget numbers for 1997 or 1998. The query builder looks like:

The results from this are:

AcctIDDeptID19971998actual.1999Budget.1999actual.2000budget.2000
10001000$1,955.00$3,925.00$9,195.00$3,575.00$0.00$8,470.00
10002000$8,540.00$8,090.00$9,620.00$9,980.00$0.00$6,145.00
10002010$9,285.00$5,510.00$1,820.00$2,340.00$0.00$5,110.00
10002020$8,460.00$5,005.00$8,825.00$9,000.00$0.00$9,500.00
10003000$4,605.00$8,355.00$2,265.00$6,115.00$0.00$5,050.00
10003010$6,715.00$8,665.00$5,390.00$695.00$0.00$5,885.00
10004000$540.00$9,085.00$7,775.00$8,550.00$0.00$8,255.00
10004010$7,885.00$1,035.00$2,870.00$1,265.00$0.00$5,510.00

From this result set it is easy to directly compare the actual amounts to the budgeted amounts. Of course it must be remembered that these amounts are from just one month and as can be guessed trying to compare two separate months from the same year using a crosstab in this manner is cumbersome.

Using crosstabs for simple queries is easy. However for typical reporting needs crosstabs become too unwieldy and difficult to maintain. Fortunately we do have some alternatives.

Comments

Popular posts from this blog

Json for jqGrid from ASP.Net MVC

jqGrid takes a specific format for its json (taken from jqGrid documentation): [js]{ total: "xxx",page: "yyy", records: "zzz", rows : [ {id:"1", cell:["cell11", "cell12", "cell13"]}, {id:"2", cell:["cell21", "cell22", "cell23"]}, ... ]}[/js] The tags mean the following: total - Total number of Pages. page - Current page Index. records - Total number of records in the rows group. rows - An array with the data plus an identifier. id - The unique row identifier, needs to be an int from what I have found. cell - An array of the data for the grid. The ASP.Net MVC framework has the JsonResult response type which we can use to populate the jqGrid. As an example I created a Person model and a method to return some data: [csharp] public class Person { public int ID { get; set; } public string Name { get; set; } public DateTime Birthday { get; set; } } public I

Changing Typed DataSet Connection String

I was working on a WinForm app that connected to a MS Access database. Yeah, Access sucks but I didn't have a choice in the matter. The app itself is used to import a bunch of CSV files into the Access database. It is more of a utility program and it has going through several variations, from being a simple hand driven command line tool to being GUI driven. The command line version was all hand controlled. I had to go in and update configuration files to point to the CSV files and the MDB database. That got old fast so I decided to make a GUI version that would allow me to pick the MDB file and each of the CSV files to import. Picking and using the CSV files was easy, it was changing the connection string for the MDB that proved to be the hardest. I am using strongly typed datasets in VS2005. If you have ever worked with them you find out soon that the connection string gets saved with the project in the app.config file, even if it is a seperate DAL dll project. My guess is

Remote Controlled RoboTank

This is my version of the ever popular to build RoboTank. It uses an Arduino Mega 2560 with the AdaFruit motor shield and an XBee S1 to communicate to the DFRobot Gamepad. The sketch for the RoboTank makes use of the AFMotor.h to drive the motors and includes a serial parser to read and process the commands coming from the Gamepad. Robotank-Sketch.zip DFRobot Wireless Joystick