Г. Серова,
профессор РГГУ;
М. Белова, доцент РГГУ
Выполнение вычислений над полями таблицы
Для выполнения вычислений с любыми полями таблицы СУБД Access позволяет создать выражение и определить его в качестве нового (вычисляемого) поля запроса. При создании вычисляемого поля можно использовать операторы: логические, арифметические или операторы сравнения, а также встроенные функции. Создание вычисляемого поля осуществляется путем ввода выражения в пустую ячейку (столбец) строки Поле бланка запроса. Выражение включает имена полей, заключенные в квадратные скобки, операторы или встроенные функции. В качестве имени вычисляемого поля автоматически задается имя Выражение N, которое можно заменить более содержательным. При построении выражений целесообразно пользоваться Построителем выражений. Основные операторы и примеры встроенных функций даты и времени приведены в Таблицах 1 и 2 соответственно.
Таблица 1.
Оператор |
Назначение |
|
+ |
Складывает арифметические выражения |
|
— |
Вычитает арифметические выражения |
|
* |
Перемножает арифметические выражения |
|
/ |
Делит первое арифметическое выражение на второе |
|
MOD |
Округляет два арифметических выражения до целых значений и делит первое на второе, в качестве результата выдается остаток |
|
^ |
Возводит первое арифметическое выражение в степень, заданную вторым арифметическим выражением |
|
& |
Создает текстовую строку как результат присоединения второй строки к концу первой. Выражение в этом случае должно содержать первое поле, запятую, второе поле, соединенные символом &, и должно иметь вид: [Поле1]&», «&[Поле2] |
Задание 24. Сформировать запрос на базе таблицы Отдел, включив дополнительное вычисляемое поле ГодСоздания с использованием встроенной функции Year (number). Ввести условие отбора с целью определить отделы, созданные в 1992 году. Вид запроса в режиме конструктора показан на Рис.15.
Таблица 2.
Функция |
Пояснения |
|
Day (number) |
Возвращает значение дня месяца в диапазоне от 1 до 31; конкретное значение дня может быть введено как условие отбора для этого поля |
|
Month (number) |
Возвращает значение месяца года в диапазоне от 1 до 12; конкретный номер месяца может быть введен как условие отбора для этого поля |
|
Year (number) |
Возвращает значение года в диапазоне от 100 до 9 999; конкретное значение года может быть введено как условие отбора для этого поля |
|
DatePart (“yyyy”; [Поле]) |
Возвращает значение года в заданном поле; конкретное значение года может быть введено как условие отбора для этого поля |
|
DateDiff (“yyyy”; [Поле1]; [Поле2]) |
Возвращает разницу в годах между вторым и первым полем |
|
Date() |
Возвращает текущую дату |
|
Now() |
Возвращает текущую дату и время |
Задание 25. Самостоятельно сформировать запрос Стаж_сотр на базе таблиц Отдел и Сотрудник, включив поля: Наименование, Фамилия, Должность и ДатаНайма с целью определения стажа работы каждого из сотрудников в отделах данной фирмы. Далее, дополнительно включить в запрос вычисляемое поле СтажСотр, записав в нем выражение с использованием встроенной функции DatePart («yyyy»;[Поле]):
DatePart («yyyy»;
Date())DatePart («yyyy»;
[Сотрудник]![ДатаНайма]).
Проверить результат выполнения запроса, после чего заменить выражение дополнительного вычисляемого поля, используя функцию DateDiff (“yyyy”; [Поле1]; [Поле2]):
DateDiff ("yyyy"; [Сотрудник]![ДатаНайма]; Date()).
Проверить результат выполнения запроса и сравнить способы реализации поставленной задачи.
Создание запросов принятия решения
Запросы, рассмотренные выше, выполняют три основные операции: собственно поиск, сортировку и вычисления. Именно эти операции чаще всего используются на практике, и для их реализации СУБД Access предлагает целый ряд эффективных функций и средств. В то же время запросы являются удобным аналитическим механизмом, помогающим в принятии решений. В частности, запрос, приведенный в качестве примера в предыдущем разделе, определяет стаж работы сотрудников в фирме. На основании полученного стажа может быть принято решение о поощрении сотрудников: например, в виде выдачи премиальных пособий либо повышения в должности. Принятие решений осуществляется системой СУБД Access с помощью логических функций IIf и Switch.
Функция IIf (строчное «если») имеет следующий синтаксис:
IIf («Условие»; «Истина»; «Ложь»), где:
Условие – логическое выражение, возвращающее значения Истина или Ложь,
Истина - значение, возвращаемое функцией в случае принятия выражением «Условие» значения Истина,
Ложь - значение, возвращаемое функцией в случае принятия выражением «Условие» значения Ложь.
Функция IIf очень полезна, однако ее использование ограничено выполнением всего одного логического выражения (условия) и возможностью возвращения в качестве результата всего одного их двух значений. Несмотря на то, что логическую конструкцию выражения можно усложнить за счет использования логических операторов AND и OR, указанный недостаток сдерживает применение данной функции в более сложных ситуациях, при которых в зависимости от нескольких значений условия, функция возвращает ряд значений. Именно для таких ситуаций и предусмотрена функция Switch.
Функция Switch имеет следующий синтаксис:
Switch («Условие1»; «Значение1»; «Условие2»; «Значение2»), где:
Условие 1 - логическое выражение, возвращающее значения Истина или Ложь,
Значение 1 - значение, возвращаемое функцией в случае принятия выражением «Значение 1» значения Истина,
Условие 2 - логическое выражение, возвращающее значения Истина или Ложь,
Значение 2 - значение, возвращаемое функцией в случае принятия выражением «Значение 2» значения Истина.
При необходимости количество условий может быть увеличено.
Задание 26. Сформировать запрос принятия решения, обеспечивающий выплату денежной премии в размере 10 000 рублей тем сотрудникам, стаж работы которых не менее 15-и лет; запрос создать на базе запроса Стаж_сотр. С этой целью:
- ввести дополнительное поле Поощрение для записи выражения с использованием логической функции IIf:
IIf([Стаж_работы]>=15;10000;0);
-
- проверить результат выполнения запроса в табличном режиме;
- усложнить логическое выражение таким образом, чтобы денежное вознаграждение могли также получить сотрудники, занимающие должность «вед.эксперт» независимо от стажа работы; вид запроса в режиме конструктора изображен на Рис. 16;
- проверить результат выполнения запроса в табличном режиме;
- сохранить запрос, присвоив имя Прин_решен.
Рис. 16. Конструирование запроса с использованием функции IIf
Задание 27. Сформировать запрос принятия решения, обеспечивающий выплату денежных премий сотрудникам в зависимости от занимаемой должности; запрос создать на базе таблицы Сотрудник, включив поля: Фамилия и Должность. С этой целью:
- ввести дополнительное поле Премия (см. Рис. 17) для записи выражения с использованием логической функции Switch: Switch([Должность]="вед.эксперт";15000;[Должность]="эксперт";10000;[Должность]="инженер";8000);
- проверить результат выполнения запроса в табличном режиме; фрагмент результата выполнения запроса приведен на Рис. 18;
- сравнить результат выполнения данного запроса с предыдущим;
- сохранить запрос, присвоив имя Прин_решен_модиф.
Рис. 17. Конструирование запроса с использованием функции Switch
Рис. 18 Фрагмент результата выполнения запроса Прин_решен_модиф
Создание группового запроса
Групповой запрос позволяет группировать данные по определенным полям и выполнять необходимые вычисления. Для создания группового запроса следует:
- в режиме конструктора запроса щелкнуть по кнопке “Групповые операции”, расположенной на панели инструментов или воспользоваться командой Вид/Групповые операции; в результате в бланке запроса появится новая строка Групповая операция;
- включить в запрос требуемые поля, т.е. определить столбцы бланка запроса;
- задать функцию Группировка для тех полей, по которым будет осуществляться группировка записей;
- выбрать групповые функции для остальных полей из раскрывающегося списка в строке Групповая операция; перечень основных функций приведен в Таблице 3;
- задать, если требуется, условие отбора; с этой целью в строке Групповая операция соответствующего поля выбрать функцию Условие, а в строке Условие отбора задать требуемое значение.
Таблица 3
Функция |
Назначение |
|
Sum |
Вычисляет сумму всех значений заданного поля в каждой группе, используется для числовых и денежных полей |
|
Avg |
Вычисляет среднее арифметическое всех значений данного поля в каждой группе, используется для числовых и денежных полей |
|
Min (Max) |
Возвращает наименьшее (наибольшее) значение, найденное в этом поле внутри группы для числовых полей. Для текстовых полей возвращает наименьшее (наибольшее) из символьных значений независимо от регистра, значение Null игнорируется |
|
Count |
Возвращает число записей, в которых значения данного поля отличны от Null |
|
StDev |
Среднеквадратичное отклонение от среднего значения |
|
Var |
Дисперсия значений |
В списке групповых операций можно также установить функции First и Last, которые используются для возвращения первой или последней записи. Кроме того, в списке групповых операций имеются элементы:
- Группировка - определяет группы, для которых выполняются вычисления,
- Выражение - создает вычисляемое поле с помощью функции, включенной в выражение;
- Условие - определяет условия отбора для поля, не участвующего в группировке; при этом автоматически снимается флажок Вывод на экран и поле не выводится на экран при выполнении запроса.
Задание 28. Создать групповой запрос Вакансии_отд на базе таблиц Отдел и Сотрудник с использованием групповой функции Count, позволяющей определить количество занятых должностей в отделах; вид запроса в режиме конструктора приведен на Рис. 19, результат выполнения запроса показан на Рис. 20.
Рис. 19. Вид группового запроса в режиме конструктора
Рис. 20. Результат выполнения группового запроса
Задание 29. Изменить запрос Вакансии, введя дополнительное вычисляемое поле, позволяющее определить количество свободных должностей в отделах. Подсчет произвести по формуле: [Вакансии]![Штат]-[Вакансии]![Count-Должность].
В строке Групповая операция дополнительного поля выбрать групповую функцию Выражение. Вид вычисляемого запроса показан на Рис. 21, а результат его выполнения – на Рис. 22.
Рис. 21. Вид вычисляемого группового запроса в режиме конструктора
Рис. 22. Результат выполнения вычисляемого группового запроса
Задание 30. Изменить запрос Вакансии_отд с целью определить количество свободных должностей в отделах только для должности «эксперт»; для этого ввести условие отбора для поля Должность, выбрав соответственно групповую функцию Условие, как показано на Рис. 23. Проверить результат выполнения запроса.
Рис. 23. Групповой запрос с условием
Создание запросов с помощью Мастера
С помощью Мастера запросов можно создать четыре типа запросов, не открывая окно конструктора. Каждый из запросов создается путем отображения последовательности окон Мастера, в каждом из которых необходимо выполнить определенное действие. Для запуска Мастера требуется:
-
- в окне базы данных выбрать объект Запрос;
- щелкнуть по кнопке “Создать” или воспользоваться кнопкой “Новый объект: запрос”, расположенной на панели инструментов;
- в появившемся диалоговом окне Новый запрос, показанном на Рис. 1, выбрать тип запроса, создаваемого Мастером: «Простой запрос», «Перекрестный запрос», «Повторяющиеся записи» или «Записи без подчиненных»; и щелкнуть по кнопке «ОК»;
- в результате запустится выбранный Мастер, указания которого следует выполнять; по окончании работы Мастера будет сгенерирован нужный запрос.
Примечание. Диалоговое окно Новый запрос можно также вызвать командой Вставка/Запрос.
Примечание. Для создания запроса типа «Простой запрос» достаточно дважды щелкнуть по ярлыку окна БД «Создание запроса с помощью мастера».
Создание перекрестного запроса
Перекрестные запросы обычно работают с большими объемами данных, подводя итоги по одному или нескольким критериям и организуя информацию в табличном виде. Перекрестный запрос представляет собой таблицу, в которой информация группируется в соответствии со значениями одного или нескольких полей и фактически приводится к виду, аналогичному электронной таблице. Построение перекрестного запроса с помощью Мастера представляется достаточно простой задачей, для выполнения которой требуется выполнить следующую последовательность действий:
- в диалоговом окне Новый запрос (см Рис. 1) выбрать «Перекрестный запрос», в результате запустится Мастер;
- определить таблицу (или запрос), поля которой будут выведены в перекрестном запросе, и щелкнуть по кнопке «Далее»;
- определить поле, значения которого будут использоваться в качестве заголовков строк, и щелкнуть по кнопке «Далее»;
- определить поле, значения которого будут использоваться для заголовков столбцов, и щелкнуть по кнопке «Далее»;
- определить поле, по которому будут проводиться вычисления, выбрав из предлагаемого списка одну из итоговых функций, и щелкнуть по кнопке «Далее»;
- ввести имя запроса и щелкнуть по кнопке «Готово».
Задание 31. Создать перекрестный запрос на базе таблицы Сотрудник, определив суммарные оклады сотрудников для каждого из отделов. Результат выполнения перекрестного запроса должен соответствовать виду, показанному на Рис. 24.
Рис. 24. Результат выполнения перекрестного запроса
Задание 32. Видоизмените перекрестный запрос предыдущего задания, исключив из рассмотрения должность «инженер». Для этого:
- введите дополнительное поле Должность, как показано на Рис. 25;
- в строке Групповая операция дополнительного поля задайте функцию Условие, а в строке Перекрестная таблица: «Не отображается»;
- введите условие отбора: Not инженер;
- проверьте результат выполнения перекрестного запроса.
Рис. 25. Вид перекрестного запроса с условием в режиме конструктора
Создание запроса поиска повторяющихся записей
Для поиска повторяющихся значений в записях таблиц служит Мастер построения соответствующего типа запроса. Результатом выполнения данного запроса является список дублирующихся записей таблицы (если такие существуют). Впоследствии в динамическом наборе данных найденные дубликаты можно при необходимости удалить. Мастер построения запроса поиска записей с повторяющимися значениями предлагает выполнить следующие действия:
- определить таблицу, в которой будет проводиться поиск записей с повторяющимися значениями, и щелкнуть по кнопке «Далее»;
- определить поля, которые будут входить в состав уникальных для поиска дубликатов, и щелкнуть по кнопке «Далее»;
- выбрать дополнительные поля для отображения в динамическом наборе данных вместе с полями, имеющими повторяющиеся значения, и щелкнуть по кнопке «Далее»;
- ввести имя запроса и щелкнуть по кнопке «Готово».
Задание 33. Создать запрос поиска повторяющихся значений в таблице Сотрудник; в качестве полей для поиска повторяющихся значений задать поля Должность и Образование; в качестве дополнительных полей выбрать Фамилия и Номер отдела. Результат выполнения запроса приведен на Рис. 26.
Рис.26. Выполнение запроса поиска повторяющихся значений
Создание запроса поиска записей, не имеющих подчиненных
Для поиска в одной таблице (главной) записей, для которых не существует подчиненных записей в другой таблице (подчиненной), служит Мастер построения соответствующего типа запроса. Выполнение данного запроса предполагает наличие двух таблиц, связанных между собой отношением «один – ко многим». Таблица Отдел в рассматриваемой базе данных является главной, таблица Сотрудник – подчиненной. Эти таблицы имеют общее поле Номер отдела, с помощью которого и осуществляется связь. Результатом выполнения данного запроса является перечень записей главной таблицы (если такие существуют), не имеющих подчиненных записей. В рассматриваемой базе данных это может быть поиск отдела, в котором нет сотрудников. Мастер построения запроса поиска записей, не имеющих подчиненных, предлагает выполнить следующие действия:
- определить таблицу, для которой не существует связанных (подчиненных) записей (в рассматриваемом примере таблица Отдел), и щелкнуть по кнопке «Далее»;
- определить таблицу, содержащую подчиненные записи (в рассматриваемом примере таблица Сотрудник), и щелкнуть по кнопке «Далее»;
- указать поле, содержащееся в обеих таблицах (поле НомерОтдела), и щелкнуть по кнопке «Далее»;
- определить поля для отображения в результате выполнения запроса и щелкнуть по кнопке «Далее»;
- ввести имя запроса и щелкнуть по кнопке «Готово».
Задание 34. Самостоятельно создайте запрос поиска записей в таблице Отдел, не имеющих подчиненных записей в таблице Сотрудник. Для выполнения поставленной задачи необходимо ввести дополнительную запись в таблицу Отдел, не вводя при этом новых записей в таблицу Сотрудник и не изменяя количества сотрудников, работающих в других отделах. Провести результат выполнения запроса и убедиться, что Мастер отобразит именно тот отдел, дополнительная запись о котором была сделана.