Building cross-tab queries using Access

Despite SQL Server's stability, performance, and range of tools, no facility is provided for building cross-tab queries. Check out how Arthur Fuller uses Microsoft Access to make up for SQL's lack of a cross-tab wizard.

Cross-tab queries are an extremely common requirement in database applications. Given SQL Server's stability, performance, and range of tools, it is surprising that no facility is provided for building cross-tab queries. But before I demonstrate how to build cross-tab queries, I'll review what a cross-tab report does.

In general, it takes rows from a table, often summing them, and then presents the group totals as columns rather than rows. For instance, you might need to create a report that summarizes sales by customer by year, presenting the sum of sales for each year as a column. The leftmost column would display the customer name, and successive columns would show the total sales for that customer during each year in the range.

Listing A shows you how to create the view using the Northwind database. This will give you a result set that looks like this, in which only the first customer is shown:

CompanyName           Year    Total Sales
AlfredsFutterkiste   1997     $2,022.50
AlfredsFutterkiste   1998     $2,250.50

Now you can see the problem. You need two columns, headed by their years and containing the total sales amount. One way to do it is to create columns that sum the Subtotals conditionally, as in Listing B.

This works well, but it's quite tedious and isn't very scalable. We need to know the range of years in advance and create columns named after those years. Furthermore, the whole operation is manual. If we expand the range of years, then we're back to editing the view. Granted, we can simply copy and paste the last column and perform a quick edit, but that's a long way from automation.

I often use Microsoft Access and specifically Access Data Project (ADP) files so that I can take advantage of its graphical query builder. However, Access excludes the crosstab wizard when using an ADP file. But we can create a standard MDB file instead, and then link it to the tables in the Northwind database. This requires creating a Data Source Name file, but the wizard lets us do this easily. Be sure to test the connection to ensure that it works, and then select the Customers, Orders Order Subtotals tables. (In a real situation, you may need more tables and/or views.) The next step is to create a query that joins the three tables and gives us the columns of interest.

In the Access database window, select the Queries tab and then click the New button on the toolbar. (Don't use the Create Query By Using wizard within the window—this gives you a list of wizards, and we only want the Crosstab Query wizard.) Select the query you just created as the data source. From there, simply follow the wizard's prompts.

The result will look similar to this:

CompanyName         Total Of Sub…       1996       1997       1998
AlfredsFutterkiste    $4,273.00   $2,022.50  $2,250.50
Ana Trujillo Emp…      $1,402.95      $88.80    $799.75    $514.40

The significant advantage of creating the crosstab query this way is that there is no more need to specify the columns or even to define the range of years. The crosstab query takes care of it by using Access's unique flavor of SQL:

TRANSFORM Sum(SalesByCustomerByYear_qry.Subtotal) AS SumOfSubtotal
SELECT SalesByCustomerByYear_qry.CompanyName,
 Sum(SalesByCustomerByYear_qry.Subtotal) AS [Total Of Subtotal]
FROM SalesByCustomerByYear_qry
GROUP BY SalesByCustomerByYear_qry.CompanyName
PIVOT SalesByCustomerByYear_qry.Year;

I'm a big fan of choosing the right tool for the job. Given SQL Server's lack of a cross-tab wizard, I'm fully prepared to lean on Access to provide it. At the end of the day, management is usually more interested in results being valid than how you obtained those results.

TechRepublic's free SQL Server newsletter, delivered each Tuesday, contains hands-on tips that will help you become more adept with this powerful relational database management system. Automatically subscribe today!

Editor's Picks

Free Newsletters, In your Inbox