ит

91 3. Запустить запрос командой Запрос→Запуск (кнопка панели инст-

рументов Конструктор запросов).

4. Сохранить запрос под именем Содержимое накладных.

Типы соединений нескольких таблиц

Если запрос включает несколько таблиц, то необходимо указать все свя-

занные поля этих таблиц и порядок действий, приводящий к конечному резуль-

тату. MS Access поддерживает четыре типа соединений:

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

2.Внешнее соединение для запроса, выводящего данные таблицы неза-

висимо от того, имеются ли соответствующие записи в другой таблице.

3. Рекурсивное соединение получается добавления в запрос копии таб-

лицы и связывания полей идентичных таблиц.

4. Соединение по отношению связывает данные некоторым отношени-

ем (за исключением равенства).

Для создания соединения следует:

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

ными полями в схеме данных не задана, их имена не совпадают, и они не явля-

ются первичными ключами.

2. Выполнить двойной щелчок мышью на линии связи.

Рис. 6.6. Окно Параметры объединения при изменении связи в запросе

3. В появившемся диалоговом окне Параметры объединения (рис. 6.6)

выбрать вариант объединения: 1 – обычное внутреннее соединение; 2 – левое

92

внешнее соединение (выводятся все записи родительской таблицы с уникаль-

ным значением первичного ключа вне зависимости от того, имеются ли соот-

ветствующие им записи в дочерней таблице); 3 – правое внешнее соединение

(выводит все записи дочерней таблицы).

Упражнение 3. Создать запрос содержащий список заказчиков, у кото-

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

1.Перейти к созданию запроса в режиме Конструктора.

2.С помощью диалогового окна Добавление таблицы внести в бланк запроса таблицы Доставка, Заказ, Заказчик.

3.В бланк запроса внести все необходимые поля согласно рис. 6.7.

4.Изменить имя поля Адрес таблицы Доставка на Адрес_доставки. Для изменения нужно в бланке запроса в строке Поле перед старым именем вписать новое, разделив их знаком двоеточия: Новое_имя:Старое_имя.

5.Перетащить поле Адрес таблицы Доставка на поле Адрес таблицы

Заказчик в Области запроса (внутреннее соединение).

6. Запретить вывод одинаковых строк (в диалоговом окне Свойства за-

проса в строке Уникальные значения поставить Да).

7. Сохранить запрос под именем Список совпадений адресов.

Рис. 6.7. Внутреннее соединение по нескольким полям

8. Запустить запрос на выполнение.

93

Запросы с параметром

Запрос с параметрами используется для повторения запроса с другими значениями. При его выполнении выдается диалоговое окно «Введите значение параметра», в котором надо ввести ключевое слово/фразу отбора данных. Для создания запроса с параметром необходимо в режиме Конструктора:

1. Указать в Условии отбора ключевую фразу, заключенную в квадрат-

ные скобки (например, [Предмет]). Она будет выдаваться в виде приглашения при выполнении запроса.

2. Указать тип данных для проверки значений, вводимых в качестве па-

раметра запроса командой основного меню Запрос→Параметры (по умолча-

нию – Текстовый). В столбец Параметр (рис.6.8) вводится название параметра без квадратных скобок, а в столбце Тип данных указывается тип значений.

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

В одном запросе можно ввести несколько параметров. При его выполне-

нии для каждого параметра будут выводиться диалоговые окна «Введите зна-

чение параметра» в том порядке записи параметров бланке запроса.

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

1.Перейти к созданию запроса в режиме Конструктора.

2.Добавить в область таблиц запроса таблицы Заказ и Заказчик.

3.В бланк запроса внести все необходимые поля согласно рис. 6.9.

4.Для добавления параметра в строку Условие отбора ввести текст со-

общения в прямоугольных скобках: [Введите название фирмы заказчика]

5.Сохранить запрос под именем Заказчики и заказы.

6.Запустить запрос на выполнение.

94

Рис. 6.9. Левое внешнее соединение

Упражнение 5. Создать запрос выбирающий заказы, у которых совпада-

ют даты выписки и исполнения (рекурсивное соединение). Для получения ре-

курсивного соединения в запрос необходимо:

1. Добавить копию таблицы Заказ, а затем создать соединение между полями Дата_выписки и Дата_исполнения (рис. 6.10).

Рис. 6.10. Рекурсивное соединение

2. Для запрета вывода одинаковых строк поставить Да в строке Уни-

кальные значения в диалоговом окне Свойства запроса.

3.Запрос сохранить под именем Исполнение в день выписки.

4.Запустить запрос.

95

Использование условий отбора

Условия отбора используются для выбора записи, состоят из выражений

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

Оператор	Название
= , <>	равно / не равно
> , <	Больше / меньше
>= , <=	больше или равно / меньше или равно
Like	как (совпадение)
Not	Не (несовпадение)
Between	между (задание промежутка)
Or	или

Упражнение 6. Составить запрос, выводящий список фирм-заказчиков,

имеющих разный физический адрес и адрес доставки. Для этого следует:

1.Перейти к созданию запроса в режиме конструктора.

2.Добавить таблицы Заказчик, Заказ и Доставка.

3.В бланк запроса внести все необходимые поля согласно рис. 6.11.

Рис. 6.11. Соединение по отношению

4.Изменить название поля Адрес таблицы Доставка на Адрес_доставки.

5.В строку Условие отбора поля Адрес_доставки ввести условие:

<>[Заказчик].[Адрес]

6. Сохранить запрос под именем Несовпадение адресов.

96 7. Запретить вывод одинаковых строк в диалоговом окне Свойства за-

проса (значение Да в строке Уникальные значения). 8. Запустить запрос. Закрыть запрос.

Построение выражений

Для построения выражений служит любой пустой столбец бланка запро-

са, в котором после имени поля записывается выражение

(Имя_поля:Вычисляемое_выражение). В него входят заключенные в квад-

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

ма:[Товар]![Цена]*[Заказ]![Количество]). Комбинация клавиш SHIFT+F2 от-

крывает окно Область ввода для записи длинных формул.

Для создания сложных формул используется окно Построитель выра-

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

Рис. 6.12 Окно построителя выражений В нижней части окна содержатся три столбца: 1) список папок с таблица-

ми, запросами, встроенными функциями; 2) объекты выделенной папки; 3) спи-

сок значений выбранного объекта.

В средней части отображается панель инструментов, содержащая наибо-

лее распространенные арифметические и логические операторы. Весь список доступных операторов находится в папке Операторы в нижней части окна.

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

97

В выражениях применяют следующие функции:

AVG() – среднее арифметическое чисел, содержащихся в указанном поле запроса (нельзя применять для текстовых полей, поля MEMO и объекта OLE.

COUNT() – Количество непустых записей запроса (все типы полей).

FIRST() – возвращает значение поля из первой записи результирующего набора (все типы полей)

LAST() – возвращает значение поля из последней записи результирую-

щего набора (все типы полей)

MAX() – находит максимальное из значений, содержащихся в указанном поле (нельзя применять для текстовых полей, поля MEMO и объекта OLE).

MIN() – Находит минимальное из значений, содержащихся в указанном поле (нельзя применять для текстовых полей, поля MEMO и объекта OLE).

STDEV(), STDEVP() – возвращает смещенное и несмещенное значение среднеквадратичного отклонения, вычисляемого по значениям, содержащимся в поле (нельзя применять для текстовых полей, поля MEMO и объекта OLE).

SUM() – сумма значений, содержащихся в указанном поле (нельзя при-

менять для текстовых полей, поля MEMO и объекта OLE)

VAR(), VARP() – возвращает значение смещенной и несмещенной дис-

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

Упражнение 7. Создать запрос, в котором вычисляется стоимость от-

дельного товара в заказах. Для этого необходимо:

1.Перейти к созданию запроса в режиме конструктора.

2.Добавить таблицы Заказ, Заказчик, Артикул_заказа и Товар.

3.В бланк запроса внести все необходимые поля согласно рис. 6.13.

Рис. 6.13. Запрос с вычисляемым полем

4. В пустом поле ввести формулу:

Стоимость: Товар!Цена*Артикул_заказа!Количество

5.Сохранить запрос под именем Стоимость товара по заказам.

6.Запустить запрос. Закрыть запрос.

98

Упражнение 8. Создать запрос, получающий суммарную стоимость то-

варов, оформленных в заказах. Для этого:

1. Вызвать запрос, созданный в упражнении 7 и сохранить его под име-

нем Суммарная стоимость командой Файл→Сохранить как.

2. Убрать поля: Название (таблица Заказчик) и Код_заказа (таблица

Товар). Для этого надо выделить поле и нажать клавишу Delete.

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

рация в бланк запроса (рис. 6.14).

Рис. 6.14. Запрос с вычисляемым полем и групповой операцией

4. Установить для поля Групповая операция в бланке запроса следую-

щие значения для соответствующих полей: Название – Группировка; Количе-

ство – Sum; Стоимость – Sum.

5. Запустить запрос. Закрыть запрос.

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

Запросы на создание таблицы позволяют создавать таблицы из результа-

тов какого-либо запроса. Как правило, этот тип запросов используется для экс-

порта данных в другие приложения либо в другую БД.

Упражнение 9. Создать таблицу Адрес_доставки с информацией о кли-

ентах, имеющих разные физический адрес и адрес доставки. Для этого:

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

2.Сохранить его под именем Создание таблицы командой

Файл→Сохранить как.

3. Воспользоваться командой Запрос Создание таблицы основного меню (кнопкой Тип запроса на панели инструментов Конструктор запро-

сов) и в появившемся ниспадающем меню выбрать

4. В появившееся диалоговое окно Создание таблицы ввести имя новой таблицы – Адрес_доставки (рис. 6.15).

99

Рис. 6.15. Диалоговое окно Создание таблицы

5.Сохранить запрос. Запустить запрос (при этом в БД создаётся новая таблица). Закрыть запрос.

6.В Панели объектов БД выбрать объект Таблицы и убедиться в по-

явлении новой таблицы с именем Адрес_доставки.

Запрос на добавление.

Запрос этого типа используется, если в таблицу требуется добавить новые строки данных. Все запросы на добавление помечаются в окне БД значком .

Следует помнить, что для добавления данных необходимо, чтобы имена полей запроса и полей таблицы, в которую будут вноситься значения, были одинако-

вы и имели одинаковый тип данных.

Упражнение 10. Добавить в таблицу Адрес_доставки записи о совпа-

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

1.Вызвать запрос на выборку, созданный в упражнении 3 и сохранить его под именем Добавление в таблицу командой Файл→Сохранить как.

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

3.В открывшемся диалоговом окне Добавление указать таблицу, к кото-

рой будут добавлены записи – Адрес_доставки. 4. Сохранить запрос (рис. 6.16) в БД.

Рис. 6.16. Запрос на добавление

100 5. После запуска запроса на добавление данных к таблице в таблицу Ад-

рес_доставки добавляются новые данные, которые можно просмот-

реть/отредактировать, перейдя к таблице Адрес_доставки в окне БД.

Запрос на удаление.

Запросы на удаление позволяют удалить из таблицы записи, соответст-

вующие некоторым критериям. Перед удалением записей рекомендуется вы-

вести их, используя запрос на выборку, а затем этот запрос преобразовать в за-

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

писей в родительской таблице (со стороны «один»), которым соответствуют за-

писи в дочерней таблице (со стороны «многие»), нарушает условие целостно-

сти данных, поэтому связанные записи в таблице со стороны «многие» стано-

вятся «висячими». Для уничтожения данных необходимо использовать каскад-

ное удаление: сначала требуется удалить записи из таблицы с отношением

«многие», а затем из таблицы с отношением «один».

Упражнение 11. Удалить из таблицы Адрес_доставки записи, которые удовлетворяют следующим критериям: название фирмы заказчика содержит первую букву М либо букву о в середине названия и в физическом адресе фир-

мы (запрос на удаление). Для создания запроса следует:

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

из которой будут удаляться записи (в нашем случае Адрес_доставки).

2.Выбрать команду Запрос Удаление.

3.При переходе к режиму на удаление в бланке запроса исчезают строки Сортировка и Вывод на экран, а появляется строка Удаление.

4.Заполнить бланк запроса (рис. 6.17) и сохранить запрос под именем

Удаление строк.

Рис. 6.17. Запрос на удаление

5. После запуска запроса, записи в таблице удаляются навсегда. Для про-

смотра результата надо перейти к таблицеАдрес_доставки в окне БД.