Crosstab and matrix presentation

The previous sections show how hard it is to make a data matrix. So there is a good reason that Access provides a non-standard SQL feature for making data matrices: the Crosstab query.

A simple example - room number versus date Figure 7.6A shows a simple example. We want a matrix with dates running horizontally and rooms running vertically. In the matrix we show the room state for each date (as a numeric code for simplicity). This matrix is made with the query shown to the right. It is based on tblRoomState. We have used the menu

Query -> Crosstab Query

to add the Crosstab gridline to the grid. We have marked roomId as a row heading and date as a column

heading. Note how we group data by roomId and date.

Each group is a bundle of room state records to be shown somehow in the matrix cell. In the hotel system, each bundle will contain at most one record since a room cannot have more than one state for a given date. This is just how it happens to be in the hotel case,

however.

Finally, we have marked state as the value to be shown inside the matrix. Since a crosstab is a kind of

aggregate query, we can only show data as aggregate functions. In this case we have used the aggregate function First. Since the bundle has at most one room state, we could as well have used Max or Min. The figure also shows the SQL version of the query. The inner part of the query is an ordinary SELECT - GROUP BY query. The group-by attribute becomes the row heading. There may be several group-by attributes, and then there will be several row headings. The first part of the query is a non-standard thing, the TRANSFORM part. It defines the value to be shown in a cell. The last part is another non-standard thing, the PIVOT part. It defines the attribute to be used as the column heading.

Note the query name: qxtbRoomGrid. The prefix qxtb tells the programmer that it is a crosstab query.

Fig 7.6A Crosstab query

qxtbRoomGrid:

TRANSFORM First(tblRoomState.state) AS FirstOfstate SELECT tblRoomState.roomID FROM tblRoomState GROUP BY tblRoomState.roomID PIVOT tblRoomState.date; Column heading Row heading Column heading Value in cell – aggregate data Row heading Query -> Crosstab Ordinary SELECT . . . GROUP BY

Outer join and several row headings

Figure 7.6B shows a more complex crosstab. We want to show also the room description. Furthermore we want to show all rooms, whether they are used or not. (In the top of the figure, room 22 wasn't shown because it wasn't used.)

The new crosstab is based on three tables: tblRoom- State, tblRoom, and tblRoomType. We use an outer join for tblRoomStates and tblRoom in order to include all rooms. We mark roomID and description as row headings.

The result is as shown. We have now got room 22 in the matrix. Unfortunately, we have also got a strange

column with the heading <>. It is caused by the outer join producing a row with a Null date since no room state matched room 22.

Figure 7.6C shows how to get rid of the Null column (<>). Instead of grouping by date, we group by a computed date:

d: IIf( IsNull(date), #24-10-02#, date)

The effect of this is that Null dates are replaced by a specific date in the range of dates we consider. Records with a Null date also have a Null state, so they don't show up as being used.

Unused room now included

Null date for the unused room

Fig 7.6B Outer join and two row headings

Fig 7.6C Avoiding the Null date

Avoiding the Null date

Dates with no used rooms are excluded

Restricting the date range

In practice we cannot show a matrix with all the dates. It would require hundreds of columns. We want to restrict the query to a specific range of dates.

In Figure 7.6D we restrict the range of computed dates to those between 24-10-02 and 27-10-02 (European date format).

The result is as shown. We got rid of the <> column and see only the dates in the restricted range. The only problem is that we don't get a column for every date in the range. For instance 26-10-02 is missing because no room is used this date.

Including all dates in the range

We might include all dates in the range by means of an additional outer join. To do this we need a table of all dates in the range, but this is not an existing part of the data model. The program might generate such a table, but let as utilize another feature in the crosstab. In Figure 7.6E we have added an IN-clause to the PIVOT part of the SQL statement. The PIVOT part specifies the column headings, and now we have explicitly asked for all the dates in the range. Notice that we have to use US date formats in SQL.

The IN-clause also causes the computer to discard all dates that are not in the IN-list. Thus we don't need the WHERE-clause anymore.

If you try to look for the IN-clause in design view (the query grid), you won't find it. It has become a property of the query grid. To see it, right click inside the diagram area and select Properties.

Unfortunately, the result looks ugly. The column headings have the programmer format, and I have not been able to find a way to change it. For other fields of the query, we can set field properties, for instance the display format. But not for the column headings. (I would call this a bug in Access.)

The IN-trick works okay if we use something else than dates for the headings. We will see an example now. Using the query in a subform

In a good user interface, we wouldn't present the query directly to the user, but embed it in a subform. To make the column headings change dynamically, we make the same trick as in section 7.5.

We let the query generate column headings that are the texts C0, C1, C2 etc. They will bind to the controls C0, C1, etc. in the subform. The program will dynamically set the labels of these controls to the real dates.

Fig 7.6D Restricting the date range

Avoiding the Null date

Restricting the date range to 24/10 - 27/10

Dates with no used rooms are excluded

The bottom of Figure 7.6E shows the query to be used. We compute the column heading with this expression:

IIf( IsNull(date), "C0", "C" & (date - #10/24/2002#))

Null dates are replaced by the text C0. Other dates are replaced by a C followed by the date index: 0 for the first day in the period, 1 for the next day, and so on. Notice that although the IN-clause has the texts in quotes, the result shows the text in user format without quotes.

Crosstab restrictions

Crosstab is great for quick and dirty solutions where you want to show the relationship between two attributes in a query. This is for instance the case for experimental data mining where the user defines queries on his own.

However, when designing a production system such as a hotel system, Crosstab provides too little flexibility. Crosstab can for instance not handle subqueries or Having, even if they occur in named queries.

Fig 7.6E Including all dates

qxtbRoomGrid4:

TRANSFORM First(tblRoomState.state) AS FirstOfstate SELECT tblRoom.roomID, tblRoomType.description

FROM tblRoomType INNER JOIN (tblRoom LEFT JOIN tblRoomState ON . . . ) GROUP BY tblRoom.roomID, tblRoomType.description

PIVOT IIf(IsNull(date), #10/24/2002#, date)

IN (#10/24/2002#, #10/25/2002#, #10/26/2002#, #10/27/2002#);

In design view, this list appears as the Column Heading property of the query Required column headings

Headings cannot be formatted (Looks like an error in Access)

qxtbRoomGrid5:

TRANSFORM First(tblRoomState.state) AS FirstOfstate SELECT . . .

PIVOT IIf(IsNull(date), "C0", "C" & (date-#10/24/2002#)) IN ("C0", "C1", "C2", "C3");

Column headings are used as control names in the subform 26-10-2002 is