Диго С.М. Базы данных проектирование и использование (1084447), страница 42
Текст из файла (страница 42)
При реализации запросов на связанных таблицах по ER-модели следует уточнить характер связи между соответствующими объектами (это может повлиять на формулировку запроса) и определить, какие должны быть заданы параметры объединения для данного запроса (в случае необходимости изменить эти параметры).
6.2.12. Выполнение агрегирующих операторов
Термина «обобщенный» или «агрегирующий» оператор в Access нет. Есть просто понятие «встроенные функции Microsoft Access», a среди них - «статистические функции» и «статистические функции по подмножеству».
Статистические функции - это Sum (сумма), Count (количество записей, возвращаемых запросом), Avg (среднее), Var (дисперсия) и другие, используемые для расчета итоговых значений. Статистическая функция, с помощью которой в запросе обрабатываются значения поля, может быть выбрана в ячейке строки «Групповая операция» в бланке запроса. Первоначально эта строка в бланке запроса отсутствует. Чтобы она появилась, необходимо выбрать позицию «Групповые операции» меню Вид или щелкнуть по кнопке со знаком «
» на панели инструментов.
Первым полем, выводимым в ответ, должно быть поле, по которому проводится группировка, а затем - поля, над которыми проводятся вычисления. Группировка может осуществляться не только по одному полю, но и по совокупности полей. Все групповые операции, кроме Count, могут выполняться только над числовыми полями.
В строке «Групповая операция» щелчком мыши можно открыть список доступных функций и в нем сделать выбор нужной статистической функции для выполняемых над полем вычислений. На рис. 6.25 приведен пример использования групповых операций в запросе (осуществляется подсчет числа сотрудников, работающих на каждой кафедре).
В Access предварительно упорядочивать таблицу по полю (полям), по которому ведется группировка, не обязательно.
В бланке запроса задают также условия отбора, с помощью которых определяются группы, для которых вычисляются итоговые значения, записи, включаемые в вычисления, или результаты, отображаемые после выполнения расчетов. На рис. 6.26 изображен запрос, в котором условия отбора применены к вычисляемому полю («Выдать список кафедр, на которых работает меньше трех человек»).
Если предположить (а это практически всегда так), что нет кафедр, на которых не работает ни одного человека, то результат запроса будет верен и если задано внутреннее соединение, и если задано левое соединение. Но, предположим, что задается аналогичный по существу запрос «Выдать список сотрудников, имеющих меньше двух детей» на двух связанных таблицах: «Сотрудники» и «Дети». Всегда есть вероятность, что имеются сотрудники, которые не имеют детей.
Если будет использовано внутреннее соединение (а оно задается по умолчанию), то такие сотрудники не попадут в ответ (т.е. результат ответа будет не соответствовать действительности). На результат запроса «Выдать список сотрудников, имеющих больше двух детей» параметры объединения таблиц не окажут влияния.
Следует быть внимательными при формулировании запросов, поскольку запросы, на первый взгляд кажущиеся однотипными, могут потребовать разной реализации.
Выражения, определяющие вычисляемые поля, использующие статистические функции, могут создаваться и с помощью Мастера простых запросов, а также при создании перекрестных запросов.
6.2.13. Вычисляемые поля
При формулировании запросов можно обеспечить вывод в ответ полей, которые в исходных таблицах отсутствуют, т. е. вывод вычисляемых полей.
Например, можно перемножить значения двух полей: «Количество» и «Цена», хранящихся в БД о поставке продукции, и получить величину «Сумма», которая в БД не хранится; или вычислить дату, отстоящую на три месяца от текущей даты и т.п.
Выражения, определяемые пользователем, дают возможность выполнять действия с числами, датами и текстовыми значениями в каждой записи с использованием данных из одного или нескольких полей. Допустимые операции будут зависеть от типа полей, участвующих в выражении.
Так, для текстовых полей возможно использование оператора «+», который в этом случае воспринимается как конкатенация (соединение) строк.
Предположим, что в таблице «Сотрудник 1» имеются три отдельных поля: «Фамилия», «Имя» и «Отчество», а нужно вывести эту информацию в одном столбце. Тогда можно использовать выражение
[Фамилия] + " " + [Имя] + " " + [Отчество].
Следует обратить внимание, что, если хотя бы одно из этих трех полей будет не заполнено (причем безразлично, будет это пустое поле или там будут введены пробелы), вся строка будет пустой. В связи с этим рекомендуется вместо операции «+» использовать операцию «&». В этом случае выражение будет иметь вид
[Сотрудник1]! [Фамилия] & " " & [Сотрудник1]![Имя] & " " &
[Сотрудник 1]! [Отчество].
Для расчетов с использованием формул, определяемых пользователем, требуется создать новое вычисляемое поле прямо в бланке запроса. Вычисляемое поле создается путем ввода выражения в пустую ячейку поля в бланке запроса (рис. 6.27).
Для получения списка, включающего фамилии и инициалы сотрудников, можно использовать следующее выражение:
Фамилияинициалы: [Сотрудник1]! [Фамилия] & " " &
Left([Сотрудник1]![Имя];1) & "." & " " &
Left ([Сотрудник1]! [Отчество]; 1) & "."
В этом выражении использована функция Left. Второй аргумент этой функции определяет число возвращаемых символов.
Для числовых значений можно использовать любые арифметические операторы. Например, можно создать поле, в котором значение поля «Цена» умножается на значение поля «Количество», чтобы получить «Сумму к оплате».
Результаты вычислений не обязательно должны отображаться в ответе. Их можно использовать, например, в условиях отбора для определения записей, которые выводятся в ответ, но само вычисляемое поле в ответ при этом не выводится. Для того чтобы поле в ответ не выводилось, необходимо для него снять значок «V» в строке «Вывод на экран» бланка запроса. Так, на рис. 6.28 изображен запрос: «Выдать список сотрудников, работающих меньше 11 месяцев».
6.2.14. Перекрестные запросы
Перекрестные запросы служат для более компактного отображения информации (схожего с изображением в электронных таблицах). В перекрестном запросе отображаются результаты статистических расчетов (такие, как суммы, количество записей, средние значения), выполненных по данным из одного поля. Эти результаты группируются по двум наборам данных в формате перекрестной таблицы. Пер-: вый набор выводится в левом столбце и образует заголовки строк, а второй - выводится в верхней строке и образует заголовки столбцов.
Например, в таблице «Сотрудники» имеются сведения о зарплате каждого сотрудника, а также признаки, на какой кафедре и в какой должности работает каждый сотрудник. Требуется для каждой кафедры определить общий фонд зарплаты, а по каждой должности - среднюю по каждой кафедре зарплату.
Для создания перекрестного запроса следует воспользоваться позицией «Перекрестный запрос» в окне Новый запрос {см. рис. 6.5) или выбрать соответствующую строку в меню Запрос. Далее необходимо выполнить ряд шагов, предлагаемых мастером по созданию перекрестных запросов.
При создании перекрестного запроса в качестве источника данных можно задать только одну таблицу (рис. 6.29). Если для реализации запроса требуются поля из разных таблиц, то надо предварительно создать вспомогательный запрос, который будет включать все требуемые поля. Так, если в создаваемом нами запросе требуется выводить название кафедры, то следует создать запрос, базирующийся на таблицах «Кафедра» и «Сотрудник», и этот запрос выбрать в качестве источника для создаваемого перекрестного запроса. Будем выводить в ответ «Код_кафедры» и поэтому выберем таблицу «Сотрудник».
В качестве заголовков строк будет использоваться «Код_кафед-ры». Для того чтобы задать это, нужно позиционироваться на этом поле и щелкнуть по кнопке, после чего экран примет вид, представленный на рис. 6.30.
В качестве заголовков столбцов следует выбрать поле «Должность» (рис. 6.31).
Далее следует определить поле на «перекрестке» строки и столбца и выбрать функцию, которая будет применена к этому полю (рис. 6.32). На этом шаге можно выбрать только одну какую-то функцию. В том запросе, который мы реализуем, по кафедре требуется определить общий фонд зарплаты (т.е. использовать функцию Sum), а по каждой должности вычислить среднюю зарплату на каждой кафедре (т.е. использовать функцию Avg). Такой запрос с использованием Мастера задать нельзя. Выберем на этом шаге функцию Avg, а потом в режиме Конструктор видоизменим полученный с помощью Мастера запрос. В результате получим перекрестный запрос, который определил среднюю зарплату по каждой кафедре и по каждой должности в пределах каждой кафедры.
На последнем шаге создания перекрестного запроса необходимо задать имя этого запроса.
Запрос, полученный в результате использования Мастера в режиме Конструктор (рис.6.33), не совсем соответствует тому, что мы хотели иметь в ответе, так как в результате для каждой кафедры будет посчитана средняя зарплата, а не общий фонд, как требовалось. Для того чтобы определить требуемую сумму, необходимо сделать соответствующее изменение в «Конструкторе запросов» (рис. 6.34).
Результат выполнения этого запроса представлен на рис. 6.35.
6.2.15. Создание запроса с параметрами
Если приходится часто выполнять однотипный запрос на выборку или перекрестный запрос, изменяя при этом значение какого-либо атрибута в условии отбора, то можно использовать запрос с параметрами. Запрос с параметрами не требует каждый раз вносить изменения в бланк запроса; вместо этого выводится приглашение пользователю ввести условия отбора.
Запрос с параметрами особенно удобно использовать, когда конечные пользователи, для которых создается система, не очень квалифицированные и желательно, чтобы они сами никакие элементы системы не изменяли. Запросы с параметрами также удобно использовать в качестве основы для форм и отчетов. Например, на основе запроса с параметрами можно создать ежемесячные отчеты. При выводе такого отчета на экране появится приглашение ввести месяц, за который должен быть получен отчет.
Для каждого поля, которое предполагается использовать как параметр, в «Конструкторе запросов» необходимо ввести в ячейку строки «Условие отбора» текст приглашения, заключенный в квадратные скобки. Это приглашение будет выводиться при запуске запроса. Текст подсказки должен отличаться от имени поля, но может включать его.
![](https://s.studizba.com/z.php?f=/templates/si/i/noavatar.png&w=100&h=100&t=1"){kind=avatar}
