Step by step exercise. Creating crosstab queries.



Practice the procedures to create a crosstab query.


Ejercise 1.


We are going to create a crosstab query to see how many students there are from each city in each particular course.

Firstly we need to think what will be the most practical way to display the data, we could put a row for each city and a column for each course, or the reverse, a row per course and a column per city.

In our case we have few cities whilst the number of courses could be higher, and therefore we select the one row per course and one column per city distribution.

1 Open the Classes.mdb database.

2 Position yourself in the Queries tab of the Database window.

3 Double click on the button and select the Crosstab Query Wizard option from the dialogue box that appears.

The next window of the wizard then appears where we need to indicate the data source.

4 Select the Students table.

All the information that we need is in this table. If you wish to extract the title of the course instead of the code, then you should previousely have created a query to obtain the students together with their course data (combining the two tables) and after this to select the query as the source of the query we are busy creating.

5 Click on the Next button. The window to introduce the header will appear.

6 Click on the Course field to select it.

7 Click on the button , observe the Sample you have in the window.

8 Double click on City, observe how the field is also sent to the list on the right and observe the Sample zone.

As it was a mistake we will send it back to the left side.

9 Click on the button, now you should only have the Course field on the right side.

10 Click on the Next button. The window to introduce the header will appear.

11 Click on City to select it. Observe how the example is taking shape.

12 Click on the Next button. The window will appear to define the value that will appear in the central cells. We want to count the students.

13 Click on the Student code field and click on the Count function.

14 Click on the Next button. The window to introduce the query name will appear.

15 Write Students by course and city.

16 Click on the Finish button. The result of the query will appear. If a column with a <> heading appears, this represents the students that have no value in the City field.

The first row has no value in Course column, this row indicates us how many students without course are in each city.

17 Close the query.


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.