2. Использование нескольких таблиц в запросе

ПРИМЕР 3. Можно организовать просмотр объединенных данных из нескольких таблиц. Для этого:

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

2. В бланк запроса надо включить поле НазваниеОтдела из таблицы Отделы и поля Должность и Оклад из таблицы Должности (рис. 2.127).

3. Сохранить полученный запрос под именем Отделы и должности. После выполнения запроса будет открыт набор записей, изображенный на рис. 2.128.

Рис. 2.127. Поля, используемые в запросе

Рис. 2.128. Набор записей

3. Создание запроса с помощью мастера запросов

Для построения некоторых типов сложных запросов используют мастер запросов.

ПРИМЕР 4. Чтобы создать запрос на основе таблиц Должности и Отделы, необходимо:

1. Выбрать объект Запрос и на правой панели – команду Создание запроса с помощью мастера.

2. В открывшемся окне в пункте Таблицы и запросы выбрать таблицу Отделы. Из пункта Доступные поля: выбрать поле НазваниеОтдела. Из таблицы Должности выбрать поля для запроса: Должность и Оклад, нажать кнопку Далее (рис. 2.133).

Рис. 2.133. Окно мастера запросов

3. В следующем окне выбор типа отчета (подробный или итоговый отчет). Выбрать пункт подробный отчет и нажать кнопку Далее.

4. Следующим шагом задать имя запроса Должности_запрос и открыть запрос для просмотра данных (рис. 2.134). Нажать кнопку Готово.

Рис. 2.134. Окно для ввода имени запроса

Результат созданного запроса отображен на рис. 2.135.

Рис. 2.135. Результат запроса, созданного с помощью мастера

4. Выполнение вычислений в запросе

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

В выражениях используют следующие операторы:

+ – складывает два арифметических выражения;

- – вычитает из первого арифметического выражения второе;

* – перемножает два арифметических выражения;

/ – делит первое арифметическое выражение на второе;

\ – округляет два арифметических выражения до целых значений и делит первое число на второе. Результат округляется до целого;

^ – возводит первое арифметическое выражение в степень, задаваемую вторым арифметическим выражением;

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

& – создает текстовую строку как результат присоединения второй строки к концу первой.

ПРИМЕР 5. Создание запроса под именем Запрос три поля в одном.

Чтобы создать запрос, используя таблицы Должности, Отделы и Сотрудники, в котором будет отображаться поле, содержащее значение полей Фамилия, Имя, Отчество, надо:

1. Выбрать пункт Создание запроса в режиме конструктора.

2. В окне Добавление таблиц выбрать нужные таблицы.

3. Выбрать поля: первое поле запроса – НазваниеОтдела из таблицы Отделы; второе поле запроса – Должность из таблицы Должности.

4. В третье поле надо ввести выражение (рис. 2.142):

Сотрудник: [Фамилия]&" "&[Имя]&" "&[Отчество]

Рис. 2.142. Окно запроса с вычисляемым текстовым полем

5. Сохранить полученный запрос под именем Запрос три поля в одном.

6. Результат запроса представлен на рис. 2.143.

Рис. 2.143. Результат запроса, содержащего вычисляемое текстовое поле

При построении сложных выражений используют утилиту, которая называется Построитель выражений.

ПРИМЕР 6. Создание запроса под именем Запрос Всего начислено.

Чтобы посмотреть, как используется построитель выражений, создать новый запрос на основе таблиц Зарплата, Сотрудники, Должности, Отделы. Для этого надо:

1. Создать запрос в режиме конструктора и добавить таблицы Зарплата, Сотрудники, Должности, Отделы. Из этих таблиц добавить в запрос поля Фамилия, Должность, НазваниеОтдела.

2. Щелкнуть по пустому полю в бланке и нажать кнопку Построить на панели инструментов либо правую кнопку мыши и выбрать команду Построить.

3. Откроется окно Построитель выражений (рис. 2.144). В верхней части окна расположена пустая область ввода, в которой создают выражения. Можно вручную вводить выражение или использовать различные кнопки операторов, которые расположены прямо под областью ввода. В нижней части окна расположены три списка, которыми можно пользоваться, чтобы найти необходимые для создания выражения имена полей и функций.

Рис. 2.144. Окно построителя выражений

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

5. Заполнить поле ВсегоНачислено. Для этого в нижней левой панели дважды щелкнуть по папке Таблицы, в которой находятся папки Зарплата, Сотрудники, Должности, Отделы. Открыть папку Должности. В нижней средней панели выбрать поле Оклад и вставить его в выражение (кнопка Вставить или двойной щелчок мышью). Затем вставить знак операции сложения и добавить из таблицы Зарплата поля Почасово, Премия, ОплатаДоговор, соединяя их операцией суммирования (рис. 2.145). Сохранить запрос под именем Запрос Всего начислено. Если при вводе будет появляться служебное слово «Выражение» – удалить его.

Рис. 2.145. Выражение для расчета суммы часов работы

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

Итоговые запросы

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

Групповые операции

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

Рис. 2.146. Групповые операции

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

Таблица 2.18

Функции групповых операций

Функция	Описание
Sum	Вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых или денежных полей
Avg	Вычисляет среднее арифметическое всех значений заданного поля в каждой группе. Используется только для числовых или денежных полей
Min	Возвращает наименьшее значение, найденное в этом поле внутри каждой группы. Для числовых полей возвращается наименьшее значение. Для текстовых полей – наименьшее из символьных значений независимо от регистра. Игнорируется значение Null
Max	Возвращает наибольшее значение, найденное в этом поле внутри каждой группы. Для числовых полей возвращается наибольшее значение. Для текстовых полей – наибольшее из символьных значений. Игнорируется значение Null
Count	Возвращает число записей, в которых значения данного поля отличны от Null. Для того чтобы посчитать число записей в каждой группе с учетом и значений Null, надо ввести выражение Count(*) в строку Поле
StDev	Подсчитывает статистическое стандартное отклонение для всех значений данного поля в каждой группе. Эту функцию можно применять только к числовым или денежным полям. Если в группе менее двух строк, то возвращается значение Null
Var	Подсчитывает статистическую дисперсию для всех значений данного поля в каждой группе. Эту функцию можно применять только к числовым полям или денежным полям. Если в группе менее двух строк, то возвращается значение Null
First	Возвращает первое значение этого поля в группе
Last	Возвращает последнее значение этого поля в группе

ПРИМЕР 7. Чтобы вывести с помощью запроса сумму окладов всех должностей по одному отделу, надо:

1. Создать запрос с помощью конструктора и выбрать таблицы Должности и Отделы.

2. Выбрать из таблицы Отделы поле НазваниеОтдела.

3. Добавить в бланк запросов поле Групповая операция (Вид–Групповые операции).

4. Указать, для какого отдела выполнять вычисления – НазваниеОтдела. В строке групповая операция выбрать установку Группировка, а в строке Условие отбора указать название отдела в кавычках – «Бухгалтерия».

5. В следующем пустом поле ввести выражение для суммы, используя построитель выражений: Сумма: Sum([Должности]![Оклад]), в строке Групповая операция выбрать установку Выражение (рис. 2.147).

Для ввода функции Sum можно использовать списки из нижних панелей построителя выражений (Функции–Встроенные функции–>Статистические–>Sum).

Рис. 2.147. Запрос на выборку всех окладов из одного отдела

6. Сохранить запрос под именем Запрос по отделу Бухгалтерия. Результат запроса изображен на рис. 2.148.

Рис. 2.148. Набор записей запроса, показанного на рис. 2.147

Использование параметров запроса

До сих пор условия отбора вводились непосредственно в бланк запроса в режиме конструктора. Но не всегда можно решить на этапе создания запроса, какие именно значения необходимо отыскать. Вместо этого можно включить в запрос параметр, и в этом случае перед выполнением запроса MS Access каждый раз будет запрашивать конкретные условия отбора.

Для этого следует ввести в строку Условие отбора фразу или имя, заключенные в квадратные скобки. Все, что заключено внутри квадратных скобок, MS Access рассматривает как имя параметра.

ПРИМЕР 8. Чтобы изменить Запрос по отделу Бухгалтерия так, чтобы каждый раз при выполнении запроса запрашивалось название конкретного отдела для условия отбора, надо:

1. Скопировать Запрос по отделу Бухгалтерия и переименовать копию в Запрос по отделам.

2. Открыть Запрос по отделам в режиме конструктора.

3. Для поля НазваниеОтдела указать свойства, показанные на рис. 2.149.

Рис. 2.149. Свойства поля «НазваниеОтдела»

4. Для каждого параметра запроса можно указать тип данных. Это используется для проверки введенного значения. По умолчанию присваивается параметрам запроса текстовый тип данных. Если необходимо изменить тип данных параметра, надо выполнить команду Параметры из пункта меню Запрос. При этом откроется окно, где можно ввести имя каждого параметра и тип данных (рис. 2.150).

Введите имя параметра – Введите название отдела, установите тип данных Текстовый.

Рис. 2.150. Окно «Параметры запроса»

5. При выполнении запроса будет выведено окно, в которое ввести подходящее значение для параметра. Например, ввести название отдела Кадры (рис. 2.151), результат запроса изображен на рис. 2.152.

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

Рис. 2.152. Результат запроса для отдела «Кадры»