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

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

Назначение групповых операций

Групповые операции позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для этих групп одну из статистических функций. В Access предусмотрено девять статистических функций:

q Sum - сумма значений некоторого поля для группы

q Avg - среднее от всех значений поля в группе

q Max, Min - максимальное, минимальное значение поля в группе

q Count - число значений поля в группе без учета пустых значений

q Stdev - среднеквадратичное отклонение от среднего значения поля в группе

q Var - дисперсия значений поля в группе

q First и Last - значение поля из первой или последней записи в группе

Внимание

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

Порядок создания запроса с использованием групповых операций

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

Выполните команду Вид/Групповые операции (View/Totals) или на панели инструментов конструктора запросов нажмите кнопку Групповые операции (Totals). Можно также нажать правую кнопку мыши и выбрать в контекстно-зависимом меню опцию Групповая операция (Total), в которой для всех полей записано Группировка (Group by).

Для групповых вычислений по некоторому полю нужно заменить в нем слово Группировка (Group by) на нужную статистическую функцию. Выбрать нужную функцию можно через раскрывающийся в поле список.

Конструирование однотабличного запроса с групповой операцией

Рассмотрим технологию конструирования однотабличного запроса с групповой операцией на примере таблицы СТУДЕНТ.

Запрос с функцией Count

Определим фактическое число студентов в группе. Создадим запрос на выборку из таблицы СТУДЕНТ. Из списка таблицы СТУДЕНТЫ перетащим в бланк запроса поле НГ (номер группы). Таким образом мы укажем, что по этому полю должна производиться группировка. Перетащим в бланк запроса поле НС, по которому будет вычисляться функция Count для подсчета числа студентов в группе.

Нажмем кнопку Групповые операции (Totals). Заменим слово "Группировка" (Group by) в столбце НС на функцию Count. Для этого вызовем список и выберем эту функцию. Бланк запроса примет вид, показанный на рис. 5.41.

Результат запроса показан на рис. 5.42.

Рис. 5.41. Запрос с групповой операцией подсчета числа студентов в группе

Рис. 5.42. Результат подсчета числа студентов в группе

Подпись поля "Count_HC" можно заменить на "Фактическое число студентов". Для ввода этой подписи в бланке запроса установим на поле НС курсор мыши и нажмем правую кнопку. В контекстно-зависимом меню выберем команду Свойства (Properties). В окне Свойства поля (Field properties) наберем в строке Подпись (Caption) "Фактическое число студентов".

Таблица результата после доработки запроса показана на рис. 5.43.

Рис. 5.43. Таблица результата с измененной подписью поля

Сохраним запрос на выборку под именем "Число студентов в группах".

Запрос с функцией Avg

Подсчитаем средний проходной балл в группе. Сформируем запрос на выборку для таблицы СТУДЕНТ с функцией Avg для поля ПБАЛЛ (проходной балл студента). В бланке запроса заполним поля, как показано на рис. 5.44.

Рис. 5.44. Запрос с групповой операцией подсчета среднего значения

Для ограничения точности результата двумя знаками выберем в окне Свойства поля (Field properties) в строке Формат поля (Format) значение Фиксированный (Fixed). Результат выполнения запроса представлен на рис. 5.45.

Рис. 5.45. Результат подсчета среднего значения

Сохраним этот запрос под именем "Средний проходной балл группы"

Запрос с несколькими групповыми функциями

Выполним расчет числа студентов и среднего проходного балла в группе в одном запросе. Это возможно, т. к. группы записей в обоих случаях формируются одинаково (рис. 5.46). Сохраним этот запрос под именем "Число студентов и средний ПБАЛЛ группы".

Задание условий отбора в запросах с групповыми операциями

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

Рис. 5.46. Запрос с одновременным использованием двух групповых функций

Подсчитаем число студентов в каждой из групп с проходным баллом больше 4,5.

Для этого в запрос Число студентов и средний ПБАЛЛ группы вторично включим поле ПБАЛЛ и в строке Групповые операции (Totals) заменим значение Группировка (Group by) на значение Условие (Where), выбрав его из списка. После этого введем в строку Условие отбора (Criteria) ">4,5" (рис. 5.47).

Рис. 5.47. Запрос с групповыми операциями и полем, введенным для определения условий отбора записей из таблицы СТУДЕНТ

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

Внимание

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

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

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

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

Сформируем запрос на создание таблицы на примере ранее полученного запроса на выборку с групповыми вычислениями Число студентов в группах (см. рис. 5.41-5.43).

В окне базы данных вызовем названный запрос в режиме конструктора запросов. Преобразуем этот запрос в запрос на создание таблицы, выбрав тип запроса на панели конструктора Создание таблицы (Make-Table Query). В окне Создание таблицы (Make Table) введем имя создаваемой таблицы "Число студентов" (рис. 5.48).

Рис. 5.48. Определение имени таблицы, создаваемой в запросе

Для того, чтобы просмотреть, какие записи будут помещены в новую таблицу, щелкните по кнопке панели инструментов Вид (View). Выполните запрос, чтобы таблица ЧИСЛО СТУДЕНТОВ была сохранена в базе данных. Терперь эту таблицу можно увидеть в списке таблиц окна БД.

Упражнение

Преобразуйте запрос на выборку Средний проходной балл группы (см. рис. 5.44,5.45) в запрос на создание таблицы, а создаваемую таблицу назовите "СРБАЛЛ".

Конструирование запроса на обновление

Обновление полей значениями, рассчитанными с использованием групповых операций

Рассмотрим технологию создания запроса на обновление на примере оьновления поля КОЛ (количество студентов группы в таблице ГРУППА).

Количество студентов в группах ранее было подсчитано в запросе на выборку Число студентов в группах (см. рис. 5.41-5.43) с использованием статистической функции Count. Запрос на обновление непосредственно на таком запросе построить нельзя. Поэтому используем для обновления не сам запрос, а таблицу ЧИСЛО СТУДЕНТОВ, полученную по запросу на создание таблицы в предыдущем пункте.

Для формирования запроса на обновление сначала создадим запрос на выборку на основе двух таблиц: обновляемой таблицы ГРУППА и таблицы ЧИСЛО СТУДЕНТОВ, содержащей данные для обновления. Заметим, что в подсхеме данных запроса автоматически устанавливается связь этих таблиц по полю с именем НГ. Для преобразования запроса на выборку в запрос на обновление (рис. 5.49) выберем на панели конструктора тип запроса Обновление (Update Query).

Рис. 5.49. Запрос на обновление таблицы ГРУППА

Заполним бланк запроса. Перетащим обновляемое поле КОЛ из списка таблицы ГРУППА. В строке Обновление (Update To) введем имя поля "Count_HC" (таблицы ЧИСЛО СТУДЕНТОВ), из которого выбираются значения для обновления. Имя поля ввлдится в квадратных скобках.

Запрос можно выполнить, не выходя из режима конструктора. Содержимое обновляемого поля КОЛ можно просмотреть в режиме таблицы до и после выполнения запроса. Для последующего использования подготовленного запроса сохраним его под именем "Обновление ГРУППА_КОЛ".

Упражнение

1. Произведите обновление поля ПБАЛЛ - средний проходной балл в таблице ГРУППА значениями из ранее созданной таблицы СРБАЛЛ.

2. Произведите обновление поля СРБАЛЛ-ГР - средняя оценка в группе по предмету в таблице ИЗУЧЕНИЕ. Для выполнения задания:

· создайте запрос к таблице УСПЕВАЕМОСТЬ для расчета средней оценки в группе по предмету и сохраните результат в таблице, для чего группировку произведите по двум полям: НГ - номер группы и КП - код предмета:

· обновите поле СРБАЛЛ-ГР в таблице ИЗУЧЕНИЕ, используя сохраненный результат.

Использование выражений в запросе на обновление

Рассмотрим формирование запроса на обновление с использованием выражения на примере заполнения поля ЧАСЫ для лекционных занятий в таблице ИЗУЧЕНИЕ. Пусть поле ЧАСЫ должно обновляться данными, вычисляемыми на основе полей ЛЕК (часы лекций) и ЧС (число семестров) из таблицы ПРЕДМЕТ. Расчетное число часов по лекциям определим по формуле ЛЕК/ЧС.

В соответсвии с задачей в записях лекционных занятий таблицы ИЗУЧЕНИЕ необходимо обновить поле ЧАСЫ расчетным числом часов. Записи о лекционных занятиях можно выбрать по значению поля ВИДЗ этой таблицы, т. к. в нем указан вид занятия. Данные для расчета среднего числа часов содержатся в таблице ПРЕДМЕТ. Таким образом запрос должен строиться на основе таблиц ИЗУЧЕНИЕ и ПРЕДМЕТ.

Создадим сначала запрос на выборку на основе таблиц ИЗУЧЕНИЕ и ПРЕДМЕТ. Затем преобразуем его в запрос на обновление,нажав соответствующую кнопку панели инструментов.

Включим в бланк запроса обновляемое поле ЧАСЫ таблицы ИЗУЧЕНИЕ. В строке Обновление (Update Query) для этого поля введем выражение [ЛЕК]/[ЧС]. Для отбора в таблице ИЗУЧЕНИЕ обновляемых записей о лекционных занятиях в бланк запроса включим поле ВИДЗ и укажем в поле Условия отбора (Criteria) значение "лек".

Окончательно сформированный запрос показан на рис. 5.50.

Выполним запрос, нажав кнопку Запуск (Run) или выполнив команду Запрос/Запуск (Query/Run). В диалоговом окне появится сообщение о числе обновляемых записей. Содержимое поля ЧАСЫ до и после обновления можно просмотреть, нажав кнопку Вид (View).

Чтобы видеть результаты обновления в таблице ИЗУЧЕНИЕ, откройте ее одновременно с запросом.

Рис. 5.50. Запрос на обновление поля значениями, вычисляемыми по данным из другой таблицы

Конструирование перекрестного запроса

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

Построение запроса начинается как обычно, например, двойным щелчком мыши на строке Создание запроса в режиме конструктора (Create query in Design view) в окне базы данных. В окне конструктора начинается создание запроса на выборку. В любой момент строящийся запрос на выборку может быть преобразован в перекрестный запрос. Для этого надо выбрать тип запроса Перекрестный (Crosstab Query) на панели инструментов или выполнить команду меню Запрос/Перекрестный (Query/Crosstab Query).

Ниже рассмотрим действия пользователя при работе с перекрестным запросом в режиме конструктора на примере.

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

Для выполнения перечисленных преобразований откроем перекрестный запрос Изучение предметов группами в режиме конструктора, выбрав в окне базы данных запрос и нажав кнопку Конструктор (Design).

Поле НП (наименование предмета) размещено в таблице ПРЕДМЕТ, поэтому ее нужно добавить к разрабатываемому запросу. Для этого, находясь в окне конструктора, нажмем кнопку Добавить таблицу (Show Table) или выполним пункт меню Запрос/Добавить таблицу (Query/Show Table).

Теперь схема данных запроса состоит из таблиц ПРЕДМЕТ и ИЗУЧЕНИЕ, связанных по полю КП (код предмета) отношением один-ко-многим (рис. 5.51).

Заменим в бланке запроса поле КП на поле НП таблицы ПРЕДМЕТ. Для этого выберем пункт меню Вид/Имена таблиц (View/Table Names), чтобы получить в бланке информацию о принадлежности поля к таблице. Далее в поле КП в строке Имя таблицы (Table) нажмем кнопку списка и выберем поле ПРЕДМЕТ, а в строке Поле (Field) - поле НП.

Для изменения подписи поля ИТОГОВОЕ ЗНАЧЕНИЕ, содержащего сумму по строкам, щелкнем правой кнопкой мыши, находясь в зоне этого поля. В открывшемся контекстно-зависимом меню выберем пункт Свойства (Properties). В окне Свойства поля (Field Properties) введем в строку Подпись поля (Caption) "Всего часов". Окончательно сформированный перекрестный запрос приведен на рис. 5.51.

Рис. 5.51. Перекрестный запрос в режиме конструктора

Результат выполнения полученного перекрестного запроса приведен на рис. 5.52.

Рис. 5.52. Результат выполнения перекрестного запроса

Решение задач на основе нескольких запросов

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

Рис. 5.53. Функционально-технологическая схема задачи из двух последовательно выполняемых запросов

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

Более сложные задачи требуют последовательного выполнения нескольких запросов. Каждый из запросов имеет свои входные и выходные данные. В простейшем случае выходные данные предшествующего запроса являются входными для следующегопостроенного на нем запроса, и, только выполнив последний запрос в цепочке запросов построенных друг на друге, вы инициируете последовательное выполнение всех запросов цепочки и полное решение задачи. Функционально-технологическая схема задачи, решаемой с помощью двух последовательно выполняемых запросов, в общем виде приведена на рис. 5.53.

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

Построение запроса на основе другого запроса

Выполним анализ оценок, полученных студентами по различным предметам. Например, подсчитаем число оценок (2,3,4,5) по каждому из предметов.

Создадим сначала многотабличный запрос на выборку на основе таблиц СТУДЕНТ, УСПЕВАЕМОСТЬ, ПРЕДМЕТ, ПРЕПОДАВАТЕЛЬ, формирующий сведения об оценках, полученных студентами по различным предметам. Для этого в режиме конструктора создадим схему данных запроса и бланк, как показано на рис. 5.54. Сохраним этот запрос с именем "оценки".

В результате выполнения этого запроса будет получена таблица, источником записей которой является таблица УСПЕВАЕМОСТЬ, а расшифровывающие данные выбираются из таблиц: ПРЕДМЕТ, СТУДЕНТ, ПРЕПОДАВАТЕЛЬ. Таким образом, каждая строка результата будет содержать информацию об одной оценке, полученной студентом по указанному в строке предмету. Число строк в таблице запроса будет равно числу строк в таблице УСПЕВАЕМОСТЬ.

Для подсчета числа различных оценок (2,3,4,5) по каждому из предметов на основе этого запроса создадим новый запрос - Число оценок. При создании нового запроса в окне Добавление таблицы (Show Table) на вкладке Запросы (Queries) выберем из списка запрос оценки. Заполним бланк запроса, как показано на рис. 5.55.

Результат выполнения запроса Число оценок приведен на рис. 5.56, где в столбце Выражение 1 (Expr1) отображено количество оценок, полученных по каждому предмету.

Рис. 5.54. Многотабличный запрос об оценках студентов

Рис. 5.55. Построение запроса на основе запроса оценки

Замечание

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

Рис. 5.56. Результаты запроса, построенного на другом запросе

Упражнение

· Создайте на основе запроса оценки, приведенного на рис. 5.54, запрос для анализа оценок, выставленных каждым из преподавателей. Результат запроса должен содержать количество оценок (2,3,4,5), выставленных каждым преподавателем. Подпись столбца с результатами выполнения групповой операции Count Выражение1 (Expr1) замените на Количество оценок

· Создайте на основе запроса оценки запрос для определения числа студентов, получивших 2,3,4 или 5 по предмету, задаваемому в диалоге с пользователем

· Создайте на основе запроса оценки запрос для подсчета числа студентов в группе, получивших 2 (или другую заданную оценку) по каждому предмету. Предусмотрите ввод номера группы и оценки в диалоге с пользователем

· Создайте на основе запроса оценки запрос для подсчета средней оценки в группе по каждому предмету; средней величины оценок, выставленных преподавателем; средней успеваемости по каждому предмету

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

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

Подготовим и последовательно выполним соответствующие запросы.

Первый запрос. Создадим первый запрос на выборку, в котором по таблице ПРЕПОДАВАТЕЛЬ с помощью функции Count подсчитаем число преподавателей по кафедрам. Сохраним запрос под именем "Число преподавателей кафедры".

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

Этот запрос построим на базе таблиц ПРЕПОДАВАТЕЛЬ, ИЗУЧЕНИЕ, КАФЕДРА и запроса Число преподавателей кафедры (рис. 5.57). Таблицы ПРЕПОДАВАТЕЛЬ и ИЗУЧЕНИЕ нужны для суммирования числа часов занятий, проводимых преподавателями каждой кафедры. Таблица КАФЕДРА необходима для включения в результат наименования кафедры, а запрос Число преподавателей кафедры - для включения в результат числа преподавателей на кафедре.

Записи этого запроса формируются на основе записей таблицы ИЗУЧЕНИЕ, причем число записей до проведения группировки равно числу записей в этой таблице. В результате запроса к каждой записи добавляется наименование кафедры НКАФ, по которому и производится группировка. Число преподавателей кафедры Count_TAБH никак не нарушает требуемого объединения записей в группы, посеольку для каждой кафедры является единственным. Число записей в таблице результата запроса равно числу кафедр.

Рис. 5.57. Запрос для подсчета общего числа часов занятий, проводимых каждой кафедрой

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

Чтобы сохранить полученные результаты, преобразуем второй запрос на выборку в запрос на создание таблицы. Таблице, которая будет создана запросом, присвоим имя "Нагрузка". Запрос сохраним под именем " Нагрузка на кафедре" (см. рис. 5.57).

Второй запрос в режиме таблицы приведен на рис. 5.58. Таблица НАГРУЗКА, сохраняемая в базе данных, содержащая результат решения этого запроса, приведена на рис. 5.59.

Рис. 5.58. Запрос Нагрузка на кафедре в режиме таблицы

Рис. 5.59. Сохраненная таблица НАГРУЗКА с результатами второго запроса

Третий запрос. Для окончательного решения задачи расчета средней нагрузки преподавателя кафедры подготовимна базе таблицы НАГРУЗКА третий запрос на выборку с вычисляемым полем. Для создания вычисляемого поля, расчитывающего среднюю нагрузку преподавателя, в строку Поле (Field) пустого столбца введем выражение [Sum_ЧАСЫ]/[Count_ТАБН]. Третий запрос на выборку с вычисляемым полем представлен на рис. 5.60.

В таблице результата следует изменить заголовок столбца Выражение1 (Expr1), формируемый по умолчанию для вычисляемого поля, и его формат (для получения результата с округлением до целого). Для этого вызовем свойства поля с помощью контекстно-зависимого меню. Зададим в качестве подписи поля (Caption) значение "Средняя нагрузка преподавателя", формат поля (Format) определим как фиксированный (Fixed), а параметру Число десятичных знаков (Decimal Places) присвоим значение "0"

Рис. 5.60. Запрос, завершающий задачу расчета средней нагрузки

Результаты выполнения запроса после изменения подписей полей в свойствах приведены на рис. 5.61.

Рис. 5.61. Результаты расчета средней нагрузки преподавателей по кафедрам

Процесс решения задачи. Для решения поставленной задачи необходимо последовательно выполнить второй запрос - Нагрузка на кафедре (см. рис. 5.57), а затем третий запрос - (см. рис. 5.60). Первый запрос Число преподавателей кафедры выполнится автоматически при выполнении запроса Нагрузка на кафедре. Для того чтобы автоматизировать решение задачи, требующей выполнения нескольких запросов, нужно использовать средства разработки приложения пользователя, например, написать макрос. Технология подготовки макроса, который позволяет выполнить последовательность запросов, рассмотрена ниже.