Creating a crosstab query

We use these queries to calculate and restructure data for easier analysis. Crosstab queries calculate a count, average, sum, or other type of total for data that is grouped by two types of information (two fields), one down the left side of the datasheet and another across the top.

Let’s create a crosstab query about an average mark by disciplines in groups. To create a crosstab query you need to create a query, contained fields: № group, Name of the discipline and Marks.

1. Double click on Create query in Design view (СОЗДАНИЕ ЗАПРОСОВ В РЕЖИМЕ КОНСТРУКТОРА);

2. Add tables: Students, Disciplines, Marks;

3. In window of Design view double click on field «№ group» in the table «Students», double click on field «Name of the discipline» in the table «Disciplines», double click on field «Marks» in the table «Marks» (Figure 6);

4. Save the query as «Disciplines Marks Groups» and close Design view.

Figure 6 Creating «Disciplines Marks Groups» query

Now you can create a crosstab query.

5. In the Database window in the Queries (ЗАПРОСЫ) object click on the «New» (СОЗДАТЬ);

6. Select the Crosstab Query Wizard (ПЕРЕКРЕСТНЫЙ ЗАПРОС) option in the dialogue box that appears and press OK (Figure 7);

Figure 7 Creating a crosstab query

1. In the View section (Показать) you can select whether you want to see the list of all the Tables (Таблицы), all the Queries (Запросы), or Both (Таблицы и запросы);
2. Also select the needed query «Disciplines Marks Groups» and press Next (ДАЛЕЕ) (Figure 8);

1. Select queries (Запросы)

3. press Next (Далее)

2.select the needed query

Figure 8 Creating a crosstab query

1. Select «Name of the discipline» for the row heading and press «Next» (ДАЛЕЕ)

(Figure 9);

1. Also select «№group» for the column heading and press «Next» (ДАЛЕЕ);

Рисунок 9 Choice rows and columns

1. Select a function AVG (average) and press «Next» (Далее) (Figure 10);

Figure 10 Select function.

1. Save as «Average» and press «Finish» (Готово);
2. The result of the query will appear.

II. Forms

Forms generally serve to define screens with which to edit the records of a table or query.

1. Create a form «Students» using the Form Wizard (Создание формы с помощью мастера).

III. Reports

Reports are generally used to present the data of a table or query in order to print them.

At first create query:

1 Click on the Queries object (ЗАПРОСЫ);

2 Double click on the Create query by using wizard option;

Add following fields«Last_name», «First_name», «Patronymic», «№ group» from the table «Students», also «Name of the discipline» from the table «Disciplines» and «Marks» from the table «Marks». Press «Next»;

3 Save as «Session» and press «Finish»;

4 Close this query.

Then you can create a report:

1. From the Database window with the Reports object (Отчеты) selected to double click on the Create report by using wizard option (Создание с помощью мастера);

2. Select the query «Session» and select all field of this table, press Next (Далее) (Figure 11);

Figure 11 Select fields for report

3. Select the grouping levels within the report: №group (Figure 12); To add a grouping level click on the field «№group» and click on the button (or double click directly on the field);

Figure 12 Grouping on № group

4. Press button «Summary options» (ИТОГИ), select AVG (average) and press OK (Figure 13);

Рисунок 13 Summary options

5. Select the type of data lay out within the report and style;

6. Save as «Resume of session» and press «Finish» (Готово).

Дата добавления: 2015-11-14