Unit 9. Crosstab queries (III)

In this window the wizard asks us to introduce the column heading. Only here may we select one field, and for every different existing value in the source, generate a column with this value as the column header.

In the lower part of the window the result can be seen, we see that by selecting the Course start field, a separate column for each value found in the Course start column of the Courses table will appear in the result of the query.

As we have selected as datetime type field, the wizard allows us to refine the column heading a little more in the following window:

When the column header has datetime type, we would normally want our totals per month, year, or quarter rather than per every date, and so the wizard asks us in this window what kind of grouping we want. E.g we have selected the Month interval, well then in the result, a column will appear for every month instead of one for every separate date. Here we can also access the result in bottom zone of the window.

Click on the Next> button to go to the next window:

In this window we are asked which value should be calculated in the column and row intersection.

In the Functions: list we select the summary function that will be used to this value to be calculated, and in the Field: list we select the field on which the summary function will be performed. E.g We have selected the Course number field and the Count function, therefore we will have the number of courses started in every month as a number of hours

If you have any doubts over the summary functions, revise the Summary queries Unit.

The wizard also permits us to add a summary column to the datasheet, this column contains the sum of the values contained in the row. In our example we will be given the total number of courses with the nº of hours of the row. In order for the wizard to add this column we need to check the Yes, include row sums box

After this, click on the Next> button and we arrive at the last window of the wizard as we will see on the next page...


Page. 9.3

Legal warning: Authorised on-line use only. It is not allowed the use of these courses in companies or private teaching centres.
© aulaClic. All rights reserved. Reproduction in any form whatsoever is prohibited. April-2006.