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

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

Для создания запроса с вычислениями используется бланк "запроса на выборку" в режиме Конструктора. Разница только в том, что в очередном свободном столбце вместо названия поля записывается формула. Формула начинается названием вычисляемой величины и двоеточием; это название в результирующей таблице запроса образует название поля с вычислениями. В формулу входят операнды - ограниченные квадратными скобками названия полей, принимающих участие в вычислениях, и знаки математических операций (таблица 13), например, Стоимость: [Сумма] / [Цена].

В узенький столбец сложно записать длинную формулу, но если нажать [Shift] + [F2], то открывается вспомогательное диалоговое окно Область ввода. В поле этого окна можно ввести формулу любой длины. После нажатия кнопки ОК формула переводится в ячейку бланка "запроса на выборку".

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

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

1) Для создания запроса относительно количества ДТП каждого водителя открыть бланк "запроса на выборку" в режиме Конструктор и добавить таблицы Информация и Учет ДТП из окна Добавление таблицы. Завершить выбор таблиц нажатием кнопки Закрыть.

2) Перетащить из таблицы Информация в нижнюю часть бланка поле ФИО, а из таблицы Учет ДТП - поле Ущерб.

3) Нажатием кнопки Групповые операции на панели инструментов Стандартная прибавить в нижней части бланка Конструктор строку Группировка.

4) В столбце Ущерб в строке Поле перед названием Ущерб ввести название вычисляемого параметра Количество ДТП:. Удалить в этом столбце функцию Группировка и ввести вместо него функцию Count(таблица 14), вычисляющую количество единиц в группе, то есть количество нарушений каждого водителя.

Таблица 13. Операнды MS Access

Оператор Пример Описание
+ [Итог] + [Надбавка] Добавляет два операнда
- Date( ) - 7 Вычисляет разницу двух операндов
* [Коробка] * [Цена коробки] Вычисляет произведение двух операндов
/ [Количество] / 12.55 Делит один операнд на второй
\ [Коробка] \ 2 Делит нацело один целый операнд на второй. При использовании деления нацело операнды с десятичными дробями округляются до целого, а дробная часть отбрасывается
Mod [Коробка] Mod 12 Возвращает остаток от деления нацело. Например, 15 Mod 12 равняется 3
^ Показатель ^ Возводит операнд (основу) в степень [показатель]
< 1 < 100 Меньше
<= 1 <= 1 Меньше или равно
= 1 = 100 Равняется
>= 100 >= 1 Больше или равно
> 100 > 100 Больше
<> 1 < > 100 Не равно
And   Конъюнкция (логическое И)
Or   Дизъюнкция (логическое ИЛИ)
Not   Логическое отрицание
Eqv   Логическая эквивалентность

Таблица 14. Функции MS Access

Функция Типы полей Описание
Avg( ) Все типы полей, кроме "Текстовый", "Поле Memo" и "Поле объекта OLE" Вычисляет арифметическое среднее набора чисел, находящихся в указанном поле запроса
Count( ) Все типы полей Вычисляет количество заполненных записей
Max( ) Все типы полей, кроме "Текстовый", "Поле Memo" и "Поле объекта OLE" Возвращает максимальное значение из данных указанного поля
Min( ) Все типы полей, кроме "Текстовый", "Поле Memo" и "Поле объекта OLE" Возвращает минимальное значение из данных указанного поля
Sum( ) Все типы полей, кроме "Текстовый", "Поле Memo" и "Поле объекта OLE" Возвращает сумму данных указанного поля

5) По окончании формирования бланка этого запроса нажать кнопку Закрыть (справа в строке заголовка бланка). На экран выводится окно для ввода названия запроса; удалить предложенное название, ввести название Количество ДТП 3 и нажать [Enter], что тождественно нажатию OK.

6) Для создания запроса относительно ущерба каждого водителя открыть бланк "запроса на выборку" в режиме Конструктор и добавить таблицы Информация и Учет ДТП из окна Добавление таблицы. Завершить выбор таблиц нажатием кнопки Закрыть.

7) Перетащить из таблицы Информация в нижнюю часть бланка поле ФИО, а из таблицы Учет ДТП - поле Ущерб.

8) Нажатием кнопки Групповые операции на панели инструментов Стандартная добавить в нижней части бланка Конструктора строку Группировка.

9) В столбце Ущерб в строке Поле перед названием Ущерб ввести название вычисляемого параметра Ущерб ДТП:. Удалить в этом столбце функцию Группировка и ввести вместо нее функцию Sum (таблица 14), вычисляющей сумму ущерба каждого водителя.

10) По окончании формирования бланка этого запроса нажать кнопку Закрыть. На экран выводится окно для ввода названия запроса; удалить предложенное название, ввести название Ущерб ДТП 3 и нажать [Enter].

11) Для создания запроса относительно общего количества ДТП, причиненных водителями, и общейсуммы ущерба открыть бланк "запрос на выборку" в режиме Конструктор. Из окна Добавление таблицы в верхнюю часть бланка добавить таблицу Ущерб ДТП; в нижнюю часть бланка дважды перетащить поле Ущерб.

12) В строке Поле в первом столбике название Ущерб заменить таким выражением: Количество ДТП:Count([Ущерб])

В строке Поле во втором столбце название Ущерб заменить таким выражением: Ущерб ДТП:Sum([Ущерб])

13) По окончании формирования бланк этого запроса нажать кнопку Закрыть. На экран выводится окно для ввода названия запроса; удалить предложенное название, ввести название Общий ущерб 3 и нажать [Enter]. Обратить внимание на то, что после закрытия последнего запроса в его строке Имя таблицы названия таблицы Учет ДТП скрываются средой Access.

14) Выполнить запросы для проверки выполненных действий.

Наши рекомендации