Использование групповых операций в запросах

Статистические функции используются в запросах главным образом для вычисления всевозможных итоговых значений, например для числового поля, можно вычислить среднее значение или сумму значений для всех или отобранных записей, можно посчитать количество записей, возвращаемых запросом и т.д. В отличие от запросов с применением вычисляемых полей, здесь вычисления проводятся внутри одного столбца («по вертикали»). Примеры функций приведены в таблице 2.1

Таблица 2.1 Примеры статистических функций

Функция	Типы полей	Описание
Avg	Все типы полей, исключая Текстовый, поле МЕМО, Поле объектов OLE.	Вычисляет среднее арифметическое значение чисел, содержащихся в указанном поле таблицы.
Count	Все типы полей	Вычисляет количество непустых записей в поле.
Sum	Все типы полей, исключая Текстовый, поле МЕМО, Поле объектов OLE.	Вычисляет сумму набора чисел, содержащихся в поле.
Min	Все типы полей, исключая Текстовый, поле МЕМО, Поле объектов OLE.	Находит минимальное из указанных в поле чисел.
Max	Все типы полей, исключая Текстовый, поле МЕМО, Поле объектов OLE.	Находит максимальное из указанных в поле чисел.

Для создания запроса с использованием групповых операций формируется запрос на выборку. В бланк запроса включаются поля, по которым надо произвести группировку, и поля, по которым надо произвести статистические вычисления. Затем курсор помещается в нижнюю часть конструктора запросов. Вызывается контекстное меню, и выбирается в нем пункт Групповые операции. В бланке запроса появляется строка Групповая Операция, в которой для всех полей записано «Группировка» (Рис. 2.40).

Р ис. 2.40 Вызов строки «Групповые операции» в конструкторе запросов

Для того чтобы выполнить вычисления в группе по некоторому полю, нужно заменить в нем слово «Группировка» на статистическую функцию. Выбрать необходимую функцию можно через раскрывающийся в поле список. При организации запросов с использованием групповых операций в бланк запроса надо отбирать только те поля, которые будут участвовать в вычислениях. Вычисления могут проводиться: на всем диапазоне записей; или на выделенных записях таблицы.

Имеется таблица 2.2, содержащая сведения о студентах:

Таблица 2.2 Сведения о студентах

фамилия	имя	отчество	дата рождения	курс	группа	семестр
Андреев	Алексей	Алексеевич	16.03.1988	4	а	7
Андреев	Анатолий	Сергеевич	26.01.1989	3	а	5
Анисимов	Руслан	Владимирович	06.03.1989	3	а	5
Баблакова	Вера	Александровна	10.06.1989	3	а	5
Бугера	Екатерина	Александровна	03.10.1989	3	б	5
Вестимая	Светлана	Николаевна	17.01.1990	3	б	5
Егоров	Михаил	Владимирович	06.06.1989	3	б	5
Бирчук	Алексей	Андреевич	29.08.1988	4	а	7
Боровкова	Анна	Владимировна	11.03.1989	4	а	7

Т ребуется получить сведения об общем количестве студентов. Для этого надо составить запрос (Рис. 2.41):

Рис. 2.41 Вычисления на всем диапазоне записей (по всей таблице)

В качестве поля можно взять любое поле таблицы, не содержащее пустых записей. Результатом выполнения такого запроса будет одно число, равное количеству непустых записей в выбранном поле, (т.е. количеству студентов). В приведенном примере 9.

Иногда расчеты надо проводить, разделив предварительно данные на группы (сгруппировав их). Например, подсчитать количество студентов на каждом курсе отдельно.

Для этого в запрос надо добавить на только поле, по которому будет производиться расчет, но и поле, по которому происходит группировка данных. В первом из указанных полей – выбрать нужную статистическую функцию, а во втором – оставить элемент «Группировка» (Рис. 2.42).

Рис. 2.42 Вычисления на выделенных записях (по курсам)

Аналогично можно подсчитать количество студентов в каждой группе каждого курса. Для этого надо произвести группировку данных сначала по курсу, а затем – по группе (Рис. 2.43).

Р ис. 2.43 Вычисления на выделенных записях (по курсам и группам)

Подобным образом можно найти количество товаров в магазине, количество товаров по отделам, общую стоимость товаров и стоимость товаров по отделам. Можно создать запрос на нахождение минимального, максимального, среднего размера оплаты труда или стажа работы. И тому подобные запросы для различных таблиц.

Статистические функции могут применяться и для одного поля таблицы. Например, можно создать запрос, в котором будет рассчитано минимальное, максимальное, среднее значение стипендии студентов, общая сумма стипендии и общее количество студентов, получающих стипендию (Рис. 2.44). Рассмотрим таблицу 2.3, содержащую сведения о студентах:

Таблица 2.3 Сведения о студентах

фамилия	имя	отчество	дата рождения	курс	группа	семестр	…	стипендия
Андреев	Алексей	Алексеевич	16.03.1988	4	а	7		315
Андреев	Анатолий	Сергеевич	26.01.1989	3	а	5		315
Анисимов	Руслан	Владимирович	06.03.1989	3	а	5
Баблакова	Вера	Александровна	10.06.1989	3	а	5		472
Бугера	Екатерина	Александровна	03.10.1989	3	б	5		315
Вестимая	Светлана	Николаевна	17.01.1990	3	б	5		472
Егоров	Михаил	Владимирович	06.06.1989	3	б	5
Бирчук	Алексей	Андреевич	29.08.1988	4	а	7		370
Боровкова	Анна	Владимировна	11.03.1989	4	а	7

Рис. 2.44 Применение групповых операций для одного поля таблицы

Помимо статистических функций, групповые операции содержат элементы: Группировка, Условие, Выражение. Элемент Группировка – применяется для создания групп данных.

Иногда в итоговые запросы необходимо ввести условие отбора. В этом случае в нужном поле вместо элемента «Группировка» выбирают элемент «Условие» и задать необходимые условия. При этом в поле, в котором введено условие, автоматически сбрасывается флажок «Вывод на экран», т.е. данное поле на экране не отображается (Рис. 2.45).

Например: найти количество студентов 4 курса группы «а»:

Рис. 2.45 Применение условий в групповых операциях

Если в итоговых запросах надо создать математическое выражение, то вместо групповых операций в нужном поле надо выбрать элемент «Выражение».

Редактирование и анализ данных с помощью запросов

Запрос на создание новой таблицы

С помощью этого типа запроса можно выбрать необходимые данные из уже существующей таблицы с помощью обычного запроса на выборку, а затем переместить их в новую таблицу. Структура новой таблицы определяется структурой запроса.

Процесс создания таблицы с помощью запроса состоит из трех шагов:

Шаг 1. Создается запрос на выборку данных. В нем указываются все поля, необходимые в новой таблице.

Шаг 2. Преобразуется запрос на выборку в запрос на создание новой таблицы. Преобразование можно провести в пункте Меню «Запрос» (Рис. 2.46) или нажав правую клавишу в верхней части бланка запросов и выбрав пункт «Тип запроса» (Рис. 2.47).

Рис.2.46 Преобразование запроса на выборку в запрос на создание новой таблицы (первый способ)

Р ис.2.47 Преобразование запроса на выборку в запрос на создание новой таблицы (второй способ)

Далее надо дать название новой таблице, которая будет создана в результате выполнения данного запроса. (Рис. 2.48) В результате этих действий будет создан запрос, снабженный значком .

Р ис.2.48 Задание имени создаваемой таблице

Шаг 3. Выполнить запрос, тем самым, поместив выбранные записи в новую таблицу. Созданную таблицу можно увидеть среди перечня имеющихся таблиц. При выполнении запроса появляется предупреждение о том, что предпринимается попытка изменения таблицы. Для выполнения запроса надо подтвердить эту изменения.

В отличие от запроса на выборку, в котором данные обновляются каждый раз, когда обновляются данные в таблице, запрос на создание новой таблицы формирует новую таблицу, данные в которой не меняются до тех пор, пока запрос не будет выполнен еще раз.

Запрос на добавление данных

С помощью этого типа запроса можно добавить необходимые данные в уже существующую таблицу. Процесс создания запроса на добавление аналогичен предыдущему и так же состоит из трех шагов.

Шаг 1. Создается запрос на выборку данных. В нем указываются все поля, которые необходимо добавить в уже существующую таблицу.

Шаг 2. Преобразуется запрос на выборку в запрос на добавление данных. Далее надо указать имя таблицы, в которую будут добавляться данные. Важно, чтобы поля в этой таблице соответствовали полям в запросе по типам данных и содержимому полей. В результате этих действий будет создан запрос, снабженный значком .

Шаг 3. Выполнить запрос, добавив тем самым выбранные записи в нужную таблицу.

Запрос на обновление данных

Запросы на обновление используются для того, чтобы изменить сразу все данные в выбранном поле таблицы. Процесс создания запроса на обновление состоит из двух шагов.

Шаг 1. В пустом бланке конструктора запросов выбрать таблицу, в которой будут обновляться данные. Выбрать тип запроса – «Обновление». При этом в нижней части бланка запросов появится строка Обновление. В ней необходимо указать условие (критерий изменения данных). Здесь можно в частности использовать логические функции. Если нужно указать формулу, то в данной строке вызывается построитель выражений. Например, чтобы изменить курс, на котором учатся студенты надо:

- выбрать таблицу «студенты»,

- указать тип запроса – обновление,

- в графе «Обновление» поставить курсор, вызвать из контекстного меню построитель выражений и ввести формулу: [Курс]+1. (Рис. 2.49). При выполнении запроса для студентов, у которых в поле «Курс» стояло число «2» - в нем окажется число «3» и т.д.

Рис. 2.49 Создание запроса на изменение: Увеличение курса на 1 год

В отличие от предыдущих запросов, в запрос на обновление надо выбирать лишь те поля, в которых будут происходить изменения. В результате этих действий будет создан запрос, снабженный значком .

Шаг 2. Выполнить запрос на изменение, в результате чего в имеющейся таблице значение выбранного поля изменится, согласно указанному критерию.

Запрос на удаление данных

Создается аналогично предыдущему. Применяется для быстрого удаления данных, для которых выполняется какое-либо условие. Процесс создания запроса на обновление состоит из двух шагов.

Шаг 1. В пустом бланке конструктора запросов выбрать таблицу, из которой будут удаляться данные. Выбрать тип запроса – «Удаление». При этом в нижней части бланка запросов появится строка Удаление, а ниже – Условия отбора. В строке Условия отбора необходимо указать условие (критерий удаления данных). Например, чтобы удалить сведения об отчисленных студентах, надо:

- выбрать таблицу «студенты»,

- указать тип запроса – удаление,

- в графе «Условия отбора» поставить курсор и указать условие: отчислен (или Да, - если в данном поле логический тип данных) (Рис. 2.50). При выполнении запроса из таблицы исчезнут все сведения об отчисленных студентах.

Рис. 2.50 Создание запроса на удаление: Удаление сведений об отчисленных студентах из основной таблицы

В запрос на удаление надо выбирать лишь те поля, в которых будут указаны условия отбора. Если условий не указать и выполнить запрос, исчезнет вся информация из таблицы. В результате этих действий будет создан запрос, снабженный значком .

Шаг 2. Выполнить запрос на удаление, в результате чего из имеющейся таблицы будут удалены данные, удовлетворяющие указанному условию.