Урок 7. Запросы

Урок 7. Запросы

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

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

Рассмотрено обновление таблиц с помощью запроса. Кроме того, в конце раз- дела приведен пример решения задачи на основе нескольких запросов.

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

Рассмотрим процесс конструирования однотабличного запроса на выборку на примере получения информации из таблицы ПРЕДМЕТ базы данных «Учеб- ный процесс».

Использование логических операций в условии отбора

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

Результат должен содержать наименование предмета (НП), общее число часов по предмету (ЧАСЫ), количество лекционных часов (ЛЕК) и число семестров (ЧС).

Для создания запроса в режиме конструктора выберем вкладку Создание на панели быстрого доступа и нажмем кнопку Конструктор запросов.

После нажатия кнопки появляется окно запроса на выборку в режиме конст- руктора Запрос1 (рис. 7.1) и диалоговое окно Добавление таблицы. В диало- говом окне выберем таблицу ПРЕДМЕТ и нажмем кнопку Добавить.

Выбранная таблица будет отображена в области схемы данных запроса. За- кроем окно Добавление таблицы.

В окне конструктора (рис. 7.1) перетащим из списка полей таблицы ПРЕД- МЕТ поля НП, ЧАСЫ, ЛЕК и ЧС в столбцы бланка запроса в строку Поле.

Рис. 7.1. Окно конструктора запроса на выборку с логическими опера- циями в условии отбора.

Сформулированные в задаче условия требуют формирования следующего ло- гического выражения:

(ЧАСЫ<=100 AND ЛЕК<>0) OR (ЧАСЫ>150 AND ЧС<3)

Здесь ЛЕК<>0 (число лекций не равно нулю), соответствует заданному в зада- че условию выбрать предметы, в которых есть лекции.

Условия из первых скобок запишем в соответствующих полях ЧАСЫ и ЛЕК первой строки Условия отбора. Между условиями в разных полях одной строки выполняется логическая операция . Условия из вторых скобок запи- шем в соответствующих полях ЧАСЫ и ЧС второй строки Условие отбора. Между условиями, записанными в разных строках, выполняется логическая операция .

Выполним запрос, нажав на панели конструктора запросов кнопку Выпол- нить.

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

Сохраним запрос, нажав кнопку Сохранить на вкладке Файл и задав нужное имя запроса. Закроем текущий запрос нажав кнопку окна запроса Закрыть. Сохраненный запрос можно выполнить, выделив запрос в окне Все объекты Access, нажатием правой кнопки мыши и выбрав кнопку Открыть.

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

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

Пусть необходимо проверить правильность задания общих часов в таблице ПРЕДМЕТ. По запросу должны отбираться только те записи, в которых зна- чение в поле ЧАСЫ не равно значению, получаемому при сложении значений полей ПР и ЛЕК.

Рис.7.2. Окно запроса на выборку из таблицы ПРЕДМЕТ записей, в ко- торых количество часов не равно сумме часов лекций и практики

Конструирование многотабличного запроса на выборку

Рассмотрим технологию конструирования многотабличного запроса на вы- борку на примере получения информации об успеваемости студентов из БД

«Учебный процесс».

Запрос на основе нескольких взаимосвязанных таблиц

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

Для создания запроса на панели быстрого доступа выберем вкладку Создание

и нажмем кнопку Конструктор запросов.

Формирование схемы данных запроса

В окне Добавление таблицы выберем таблицы:

Ÿ  СТУДЕНТ- для выборки фамилия студента из поля ФИО

Ÿ  УСПЕВАЕМОСТЬ- для определения кодов предметов (поле КП), по кото- рым студент сдал экзамены, выборки оценок по предмету (из поля ОЦЕН- КА).

Ÿ  ПРЕДМЕТ- для выборки наименования предмета (из поля НП), представ- ленного кодом КП в таблице УСПЕВАЕМОСТЬ.

Закроем окно Добавление таблицы.

В окне конструктора запросов (рис. 7.3) представлена схема данных запроса, содержащая выбранные таблицы. Между таблицами автоматически установ- лены необходимые связи:

Ÿ  Одно-многозначная связь между таблицами СТУДЕНТ и УСПЕВАЕ- МОСТЬ по составному ключу НГ+НС в соответствии с построенной ранее схемой данных.

Ÿ 

Рис. 7.3. окно запроса об успеваемости студента с созданной схемой данных.

Подготовка бланка запроса

Поскольку в запросе используется несколько таблиц, в бланке запроса удобно видеть имя таблицы наряду с именем поля. Для отображения имен таблиц в бланке запроса (рис. 7.4) нажмем кнопку Имена таблиц на панели инстру-

Рис. 7.4. Запрос на получение информации о сдаче экзаменов сту- дентками Боярской Н.П. и Маковой.

Перетащим с помощью мыши поля, включаемые в результат выполнения за- проса, в строку бланка запроса Поле:

Ÿ  ФИО- из таблицы СТУДЕНТ

Ÿ  НП- из таблицы ПРЕДМЕТ

Ÿ  ОЦЕНКА- из таблицы УСПЕВАЕМОСТЬ

Ввод значений в условия отбора записей

Пусть необходимо получить информацию об успеваемости конкретных сту- дентов: Боярской Н.П. и Маковой.

Зададим в строке Условие отбора их фамилии. Запишем фамилии студентов в разных строках бланка запроса, поскольку необходимо выбрать записи со зна- чением в поле ФИО- Боярская или Макова. Поскольку инициалы студентки Маковой неизвестны, ее фамилию зададим с использованием символа шабло- на «*». Заметим, что фамилия с инициалами содержит точки, поэтому ее надо брать в кавычки. После ввода фамилии с символом шаблона система сама вставляет оператор Like, определяющий поиск по образцу. Заполненный бланк запроса представлен на рис. 7.4.

Выполним запрос, нажав на панели конструктора запросов кнопку Выпол- нить.

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

Формирование записей результата при выполнении запроса

По заданной фамилии студента- Боярская Н.П.- в таблице СТУДЕНТ отыски- вается запись. По значению ключа связи НГ+НС осуществляется выборка подчиненных записей из таблицы УСПЕВАЕМОСТЬ с оценками данного сту- дента по разным предметам (в поле ОЦЕНКА). Для каждой из этих записей по значению ключа связи КП выбирается одна запись с наименованием предмета (НП) из таблицы ПРЕДМЕТ. Таким образом, таблица с результатом запроса будет содержать по одной записи о каждом предмете, сданном студентом. Аналогично формируются записи для второго заданного в запросе студента- Маковой.

Ввод параметров в запрос

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

Пусть необходимо получить информацию об оценке студента по заданному предмету.

В условие отбора поля ФИО вместо конкретной фамилии введем название па- раметра, по которому будет запрашиваться фамилия при выполнении запроса. Название параметра введем как текст, заключенный в квадратные скобки:

[Фамилия и инициалы студента]

Этот текст Access воспринимает как имя параметра (рис. 7.5). В условие отбо- ра поля НП введем второй параметр запроса:

Рис. 7.5. Бланк запроса с параметрами для ввода ФИО и НП.

Рис. 7.6. Диалоговые окна для ввода параметров запроса.

При выполнении запроса Access выведет диалоговые окна, представленные на рис. 7.6, в которые пользователь сможет ввести нужные значения параметров.

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

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

Для решения этой задачи необходимо использовать таблицы:

Ÿ  ИЗУЧЕНИЕ, в которой содержатся сведения о плановых занятиях в группах (в текущем семестре), в том числе о продолжительности (поле ЧАСЫ) каждого вида занятия (поле ВИД3).

Ÿ  ПРЕДМЕТ, в которой содержатся сведения о наименовании (поле НП), общей продолжительности изучения предмета (поле ЧАСЫ), числа ча- сов практики (ПР) и числе семестров изучения (ЧС).

Для отбора записей о практических занятиях по информатике из таблицы ИЗУЧЕНИЕ надо в строке Условие отбора для поля НП (ТАБЛИЦА ПРЕД- МЕТ) задать значение «Информатика», а для поля ВИД3 (таблицы ИЗУЧЕ- НИЕ) задать значение «пр» (практическое занятие).

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

[ИЗУЧЕНИЕ] ! [ЧАСЫ]*[ЧС]

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

Рис. 7.7. Запрос с использованием в условиях отбора имен полей из разных таблиц.

        Рис.

7.8.    Записи о практических занятиях по информатике, в которых число часов не отвечает заданным условиям.

Создание вычисляемых полей в запросах

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

Рассмотрим технологию создания запроса с вычисляемым полем на примере таблицы ПРЕДМЕТ.

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

задачи рассчитаем разность между общим числом часов по предмету (поле ЧАСЫ) и суммой часов лекций (поле ЛЕК) и практики (поле ПР). в ответ включим только те записи, для которых эта разность не равна нулю.

Создадим запрос на выборку для таблицы ПРЕДМЕТ. Перетащим в бланк за- проса поля НП, ПР, ЛЕК, ЧАСЫ (рис.7.9).

Создание вычисляемого поля

Для получения разности создадим вычисляемое поле в пустой ячейке строки

Поле, записав туда выражение: [ЧАСЫ] - [ПР] - [ЛЕК]

Рис. 7.9. Запрос с вычисляемым полем и условием отбора по его зна- чению.

После ввода выражения система формирует имя вычисляемого поля по умол- чанию- «Выражение 1». Это имя вставится перед выражением. Для изменения имени установим курсор мыши в вычисляемом поле бланка запроса и нажмем правую кнопку мыши. В контекстно- зависимом меню выберем Свойства по- ля, а в строку Подпись введем новое имя поля- «ЧАСЫ не равны ПР+ЛЕК». Имя поля может быть исправлено также непосредственно в бланке запроса.

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

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

Вызовем построитель выражений, нажав команду Построить в контекстно- зависимом меню (курсор мыши должен быть установлен на строке Поле вы- числяемого поля).

Рис. 7.10. Окно построителя выражений при формировании вычисляе- мого поля.

Сохраним запрос под именем «Разность часов по предмету». Сохранить. Со- храненный запрос можно выполнить, выделив запрос в окне Все объекты Access, нажатием правой кнопки мыши и выбрав кнопку Открыть.

Построенный запрос может быть использован для проверки правильности за- полнения поля ЧАСЫ в таблице ПРЕДМЕТ.

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

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

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

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

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

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

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

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

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

дится группировка, и поля, для которых выполняются групповые функции.

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

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

числения.

Выполните команду Создать/Конструктор запросов и на панели инструмен- тов конструктора запросов нажмите кнопку Итоги.

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

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

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

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

Нажмем кнопку Итоги. Заменим слово "Группировка" в столбце НС на функ- цию Count. Для этого вызовем список и выберем эту функцию. Бланк запроса примет вид, показанный на рис. 7.11. Результат запроса показан на рис. 7.12.

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

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

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

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

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

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

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

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

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

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

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

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

4,7.

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

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

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

группы записей, например, группы студентов с заданным номером или с за- данным средним проходным баллом.

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

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

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

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

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

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

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

Упражнение. Преобразуйте запрос на выборку Средний проходной балл группы (см. рис. 7.14, 7.45) в запрос на создание таблицы, а создаваемую таб- лицу назовите “СРБАЛЛ”.

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

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

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

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

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

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

Запрос можно выполнить, не выходя из режима конструктора. Содержимое обновляемого поля КОЛ можно просмотреть в режиме таблицы до и после

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

Упражнение

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Заменим в бланке запроса поле КП на поле НП таблицы ПРЕДМЕТ. Для этого щелкнем правой кнопкой мыши на области отображения полей таблиц и вы- берем Имена таблиц, чтобы получить в бланке информацию о принадлежно- сти поля к таблице. Далее в поле КП в строке Имя таблицы нажмем кнопку списка и выберем поле ПРЕДМЕТ, а в строке Поле - поле НП.

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Упражнения

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Скачано с www.znanio.ru