Crosstab queries are a powerful means of summarizing data. But they can sometimes lead to unexpected situations. Mike Watson looks at how you can ensure that your data-driven crosstab reports can work reliably. Along the way, he also shows how Access helps you out by ignoring unwanted data.

A classical tale: Near year’s end, the boss asks you to set up a report summarizing the sales statistics by quarter. He wants it by yesterday. You give it a little thought and, bingo, you think, “This is where I’ll use my first crosstab query!” A bit later you’ve created the crosstab, designed the report (see Figure 1), tested it, and–it works perfectly! The boss likes it, the report goes into the production system, and it’s distributed to all of the regional sales offices–and they love it, too! You’re a hero and modestly concur with the compliments and plaudits that rain down on you. Career prospects: very positive.

 
Figure 1

Then along comes the first quarter of 2005, and suddenly you’re receiving Help Desk complaints that your sales statistics report is bombing! Career prospects: cooling. What’s happened? What’s happened is that you’ve fallen afoul of the evil Crosstab Missing Column beast!

This lurking menace is due to how the crosstab logic creates the Column Heading fields. When you created the crosstab query in the last part of the year, there was at least one occurrence of your data for each quarter. The resulting crosstab query therefore had four Column Heading fields, one for each quarter: Tot1, Tot2, Tot3, and Tot4. In your report design you bound four textboxes (one for each quarter) to the four fields in the query that represented the four quarters. In the first quarter of 2005, however, the available data is for the first quarter only. The resulting crosstab query, as a result, contains only one Column Heading field. When you run your report, it bombs with following error: “The Microsoft Jet database engine does not recognize ‘[2Tot]’ as a field name or valid expression.”

Your report expected four bound data fields, but the underlying query has only supplied one.

A simple solution

There’s a very simple solution to this problem: In Design View for your query, click in the table area at the top of the query, right-click, and select Properties. On the single tab for Query Properties, there’s an entry called Column Headings. You can enter a list of column headings here and, if the crosstab query doesn’t generate a column with this heading, then a column with the heading would be inserted. To solve my problem, for instance, you’d set this property to “1Tot”, “2Tot”, “3Tot”, “4Tot”. In the first quarter of 2005, the column “1Tot” will be generated from the data and the missing columns will be inserted automatically.

There are two problems with this solution. The least important is that this property isn’t available on all versions of Access. The key problem is that my data-driven report is now no longer being driven by the data. Instead, some of the data comes from the tables in my database and some of the data is driven by property settings on the query (property settings that not everyone is going to be aware of).

Imagine, for instance, that my column headings aren’t derived from dates but, instead, are derived from the names of departments within the company. On some occasions, when I run the report, there will be data for all the departments; on other occasions there won’t be data for all the departments. Like my report for the four quarters of the year, my crosstab-based report will fail unless all of the departments are present.

I could list all of the department headings in the Column Heading property for my report. But now, when I go to add a new department to my company, I’ll also have to remember to add the department to the Column Heading property in my report. Some problems solve themselves: When a new department is added and doesn’t appear on the report, someone will notice the department’s absence and bring it to my attention so that I can add a new column to the report. However, it’s extremely unlikely that I (or my replacement) will remember to update the Column Heading property. And why should anyone remember? Reports should be data-driven–it’s bad enough that I have to go in and add the new column to the report.

To solve this problem, you have to design the report’s underlying query to always include at least one occurrence of quarter data, even if there’s no real data for that quarter. And this solution should be driven by the data in the database, not by some arbitrary property setting. This ensures that the crosstab query creates all the fields that your report expects. At runtime, you have to ignore any dummy records that were included to correctly create the crosstab.

To be perfectly honest, there are at least two other possible solutions:

• You could design a different static report for each quarter at the cost of building and maintaining four reports and figuring out how to pull them together to present the data as a whole. An excellent strategy if you’re paid by the hour, I would think.
• You could dynamically modify the report in Design mode at runtime to cater for the potential missing Column Heading fields. This actually has some potential but is far more complex than you really need.

A data-driven solution

For this article I’ll assume that there’s a table called tblOrdersSingleYear that either contains data for all four quarters of 2004 or only data for the first quarter of 2005. The table contains three fields: OrderDate, OrderAmount, and Salesperson. In order to extract the quarter from the date, I’ve created a query called qry_OrderTotals: It converts the OrderDate field from tblOrdersSingleYear into a quarter number in a field called CodQuarter by using the DatePart function, passing “q” (to indicate that I want the quarter) and the OrderDate field:

SELECT tblOrdersSingleYear.SalesPerson,

Year([OrderDate]) AS OrderYear,

DatePart("q",[OrderDate]) AS CodQuarter,

tblOrdersSingleYear.OrderAmount

FROM tblOrdersSingleYear;

Now I’m ready to create the crosstab query qry_OrderTotals_X, shown in Figure 2. The Column Heading fields are constructed from the CodQuarter value and the string “Tot”. For 2004 data there will be four Column Heading fields: 1Tot, 2Tot, 3Tot, and 4Tot.

Figure 2

In Figure 3, you can see the report that’s based on this query. The figure shows the four report fields bound to the four query fields (you can see the four quarter value fields in the Fields List window on the right).

Figure 3

To ensure that the query will always contain the necessary fields, I have to build a query that includes at least one record for each quarter. For the date-driven example that I’m using, this means creating a table to hold the quarter numbers, tblQuarters (see Figure 4). In many cases, creating a new table won’t be necessary. For the report driven by department names, those names are probably already listed in a table somewhere in your database. Consider creating a new table, as I have here, as the worst-case scenario.

Figure 4

In my next step, I created a query that joined tblQuarters (my new table) with qry_OrderTotals (the query that extracts the data for the report). When joining these two tables, I used a Right Join that ensured that all of the rows from tblQuarters will appear in the result, even if there’s no matching quarter in qry_OrderTotals. This means I always get four quarter numbers and only the data for the available quarters.

I then converted this query into a crosstab query. As you can see in Figure 5, I have all four quarter columns present and a blank record due to the Right Join that resulted in null data in the fields.

Figure 5

Eliminating null data

I have only one problem left: How do I avoid the blank record appearing in the report? I have two solutions:

• Create another query based on qry_AllQrtrsOrderTotals_X, put an “Is Not Null” criteria on the Salesperson field, and then base the report on this new query.
• Use this query for my report and put an “Is Not Null” criteria into the report’s Filter property and set the report’s Filter On property to True.

I’ve chosen the latter just to keep the number of queries in my database window to a minimum. Now, no matter when I run the report the output is what I expect. At the start of 2005, the first quarter column has valid data and the remaining columns are empty.

But you may not even have to add the filter to eliminate the null record. Data-driven reports often eliminate the null data automatically.

As an example, consider a more complicated report that shows both the sales totals and the sales counts per quarter by salesperson. To create this report, I’ll write a new crosstab query that counts sales by salesperson within each quarter (qry_AllQrtrsOrderCounts_X), again using a Right Join to my table of quarters. To drive the report, I create a query that joins the counts crosstab to the totals crosstab using the SalesPersons field as the JOIN column.

When I build the report, I don’t bother putting in the “Is Not Null” test that I used in my first report. However, the Null record representing missing quarters in the data doesn’t appear in the report output even though I haven’t explicitly done anything to ignore them. The reason is that Access won’t join on a null field. Since the Salesperson field in the dummy records is null, Access has kindly done the work for me and not included the dummy records in the report that were necessary to create all four quarter fields: I’ve got a more complex report by doing less work.

By having a table that the column headings will be derived from, I’ve made my crosstab query completely data-driven. If I do need to ensure that a column heading is present, I can do that by updating the table rather than some obscure property. If I take the time to create a maintenance form based on the table, my users can make the update and I’ll have even less to do. In fact, with any luck, the table of headings will automatically be updated through whatever business processes are already in place. Going back to my department-driven example, for instance, it’s likely that the new department’s name will end up in the list of departments through whatever processes my users follow in adding a new department to the company. Who says that a free lunch doesn’t exist?