15 Вопрос

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

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

Если вы будете создавать запрос без Мастера, то Access откроет окно Добавление таблицы, где надо будет выбрать Таблицы или Запросы с полями, которые будут включены в создаваемые запросы. Кроме этого, надо выбрать таблицы для установления косвенных связей между полями. Для включения таблицы или запроса в окно конструктора выделите окно и кнопка Добавить или дважды щелкните на значке объекта; после задания всех необходимых таблиц нажмите кнопку Закрыть.

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

4.Дополнительные возможности бланков запроса

Бланки запросов обладают некоторыми дополнительными возможностями по сравнению с командой Расширенный фильтр:

В бланке есть строка Вывод на экран, с помощью которой можно выводить или не выводить какие-то поля из запроса в итоговый режим таблицы (для вывода установить флажок в этой строке в нужном поле)

Как и при работе в Расширенном фильтре, в простом запросе можно сортировать записи по значению любого из полей запроса, выбрав значение По возрастанию или По убыванию в строке Сортировка. Поля сортируются слева направо. Поля, используемые при сортировке, могут не отображаться в итоге запроса.

Можно в любой момент добавить к окну конструктора новый список полей командой Запрос/Добавить таблицу или кнопкой Добавить таблицу на панели инструментов. На экране появится окно добавления таблицы (рассмотрено выше). При отображении нескольких полей в режиме конструктора Access рисует линии, изображающие связи между полями (такие же, как в окне Схема данных). Вы также можете самостоятельно добавить или изменить связь. При попытке выполнить запрос без существующих связей результат будет неверный.

ВНИМАНИЕ!

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

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

Чтобы удалить список полей из бланка выделите этот список и выполните команду Запрос/Удалить таблицу или кнопка Del.

5. Просмотр результатов запроса

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

Меню Вид – конструктор запросов или режим таблицы

В режиме конструктора нажать кнопку Запуск для просмотра результатов

Кнопка Вид на панели инструментов и из меню выбрать Конструктор или Режим таблицы

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

6.Запросы с выводом наборов значений

Access содержит одно изящное средство, которое позволяет выйти за пределы стандартной сортировки и просмотреть максимальные или минимальные значения поля

Например: При сортировке книг вашей коллекции вы захотели вывести информацию только о пяти самых дорогих книгах. Для этого:

Отобразите запрос в режиме конструктора

Отсортируйте записи по некоторому полю (в нашем примере по полю Цена). Для просмотра минимальных значений установите в строке Сортировка значение по возрастанию, для просмотра максимальных значений по убыванию

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

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

Для просмотра результатов запроса перейдите в режим таблиц.

7. Подведение итогов по записям

Кроме выборки записей, удовлетворяющих поставленному условию, запросы также применяются для обобщения информации. Это делается с помощью строки Групповая операция из бланка запроса.

Чтобы добавить строку Групповая операция в бланк, необходимо работать с запросом в режиме конструктора. Выполните команду Вид/Групповые операции или нажмите кнопку Групповые операции на панели инструментов.

После создания этой строки могут выполняться разнообразные операции для подведения итогов: группировка, SUM, Avg, Min, Max, Count, StDev, Var, First, Last, Условие, Выражение.

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

SUM - сумма

Avg – среднее арифметическое значений группы

Min – min значение в группе

Max – max значение в группе

Count – количество значений в группе

Stdev – стандартное отклонение для выборки, состоящей из значений группы

Var – дисперсия для выборки, состоящей из значений группы

First – первое значение в группе

Last – последнее значение в группе

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

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

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

в поле «Цена покупки» в строке «Групповая операция» должно быть Avg,

в поле «Тип обложки» в строке «Групповая операция» должна быть Группировка.

При выполнении данного запроса Access отбирает записи на основании поля Группировки и результаты выдает по второму полю.

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

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

8. Вычисление значений полей

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

Вычисляемое поле может быть создано в запросе, форме или отчете.

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

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

Т.к. Access не позволяет задавать величины в %, в формуле в нашем случае значение цены умножается на 1,15.

При просмотре запроса поле с вычислениями так и будет называться: «Выражение 1» .Цифра 1 показывает, каким по счету оно было создано. Результаты вычислений первоначально не отформатированы.

Можно изменить название поля и его формат. Для этого вернитесь в режим конструктора и в выражении «Выражение 1: [Цена Покупки]* 1,15» выделите текст до двоеточия и введите новое имя.

Чтобы отформатировать вычисляемое поле, щелкните правой кнопкой мыши в соответствующем столбце и команда Свойства/ вкладка Общие/ поле Формат поля. В данном случае – Денежный формат.

9. Перекрестные запросы

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

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

Для создания перекрестного запроса:

В окне БД перейти на вкладку Запросы – кнопки Создать – Перекрестный запрос. Подключается мастер.

Выберите таблицу или запрос, которые содержат поля для перекрестного запроса.

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

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

Укажите поле с обобщаемой информацией и функцию для проведения вычислений. Кроме того, можно указать, нужно ли привести в начале каждой строки значение, подводящее итог по всем значения, входящим в строку (например, среднее арифметическое для функции AVG, сумму для функции SUM и т.д.). Нажмите Далее, а затем Готово.

Совет:

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

10. Запрос на изменение

Рассмотрели: запросы на выборку перекрестные запросы

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

создание таблицы

обновление

добавление

удаление

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

При преобразовании запроса к другому типу состав строк бланка изменяется.

10.1. Удаление – это запрос, уничтожающий в таблице все записи, удовлетворяющие заданному критерию (например, «чистка» БД от устаревших записей). Но чтобы не лишиться нужных записей, надо сначала просто составить запрос на выбор записей, просмотреть их, и только после просмотра можно перейти к запросу на удаление.

10.2. Создание таблицы – запрос создает из выбранных записей новую таблицу (например, для создания резервной копии устаревших записей, которые потом надо будет убрать из БД).

10.3. Добавление – запрос копирует записи из одной таблицы (или из нескольких таблиц) в другое место (в другую существующую таблицу). Часто применяются для добавления новой группы записей или обновления существующей таблицы или БД.

10.4. Обновление – для изменения значения любого поля БД для записей, удовлетворяющих указанным критериям. При работе с данным типом в бланке запроса появляется еще одна строка Обновление, в которую надо ввести выражение для изменения поля (заполняется поле аналогично вычисляемым полям).