Типы соединений нескольких таблиц
Если запрос включает несколько таблиц, то необходимо указать все связанные поля этих таблиц и порядок действий, приводящий к конечному результату. MS Access поддерживает четыре типа соединений:
1. Внутреннее соединение для запроса на выборку, результат которого содержит записи таблицы, в которых есть совпадающие значения в связанных полях другой таблицы.
2. Внешнее соединение для запроса, выводящего данные таблицы независимо от того, имеются ли соответствующие записи в другой таблице.
3. Рекурсивное соединение получается добавлением в запрос копии таблицы и связывания полей идентичных таблиц.
4. Соединение по отношению связывает данные некоторым отношением (за исключением равенства).
Для создания соединения следует:
1. Перетащить поле одной таблицы на поле другой таблицы. Появляется линия с точками по обеим сторонам, означающая, что связь между соединенными полями в схеме данных не задана, их имена не совпадают, и они не являются первичными ключами.
2. Выполнить двойной щелчок мышью на линии связи.
Рис.6.6. Окно Параметры объединения при изменении связи в запросе
3. В появившемся диалоговом окне Параметры объединения (рис.6.6) выбрать вариант объединения: 1 – обычное внутреннее соединение; 2 – левое внешнее соединение (выводятся все записи родительской таблицы с уникальным значением первичного ключа вне зависимости от того, имеются ли соответствующие им записи в дочерней таблице); 3 – правое внешнее соединение (выводит все записи дочерней таблицы).
|
|
|
Упражнение 3. Создать запрос содержащий список заказчиков, у которых физический адрес и адрес доставки совпадают (внутреннее соединение по нескольким полям). Для решения необходимо:
1. Перейти к созданию запроса в режиме Конструктора.
2. С помощью диалогового окна Добавление таблицы внести в бланк запроса таблицы Доставка, Заказ, Заказчик.
3. В бланк запроса внести все необходимые поля согласно рис.6.7.
4. Изменить имя поля Адрес таблицы Доставка на Адрес_доставки. Для изменения нужно в бланке запроса в строке Поле перед старым именем вписать новое, разделив их знаком двоеточия: Новое_имя:Старое_имя.
5. Перетащить поле Адрес таблицы Доставка на поле Адрес таблицы Заказчик в Области запроса (внутреннее соединение).
6. Запретить вывод одинаковых строк (в диалоговом окне Свойства запроса в строке Уникальные значения поставить Да).
7. Сохранить запрос под именем Список совпадений адресов.
8. Запустить запрос на выполнение.
Рис.6.7. Внутреннее соединение по нескольким полям
Запросы с параметром
Запрос с параметрами используется для повторения запроса с другими значениями. При его выполнении выдается диалоговое окно «Введите значение параметра», в котором надо ввести ключевое слово/фразу отбора данных. Для создания запроса с параметром необходимо в режиме Конструктора:
|
|
|
1. Указать в Условии отбора ключевую фразу, заключенную в квадратные скобки (например, [Предмет]). Она будет выдаваться в виде приглашения при выполнении запроса.
2. Указать тип данных для проверки значений, вводимых в качестве параметра запроса командой основного меню Запрос→Параметры (по умолчанию – Текстовый). В столбец Параметр (рис.6.8) вводится название параметра без квадратных скобок, а в столбце Тип данных указывается тип значений.
Рис.6.8 Окно Параметры запроса
В одном запросе можно ввести несколько параметров. При его выполнении для каждого параметра будут выводиться диалоговые окна «Введите значение параметра» в том порядке записи параметров бланке запроса.
Упражнение 4. Создать запрос содержащий список заказчиков, номера заказов, оплату, даты выписки и исполнения. Добавить параметр указывающий название фирмы (левое внешнее соединение с параметром). Для этого надо:
1. Перейти к созданию запроса в режиме Конструктора.
2. Добавить в область таблиц запроса таблицы Заказ и Заказчик.
|
|
|
3. В бланк запроса внести все необходимые поля согласно рис.6.9.
4. Для добавления параметра в строку Условие отбора ввести текст сообщения в прямоугольных скобках: [Введите название фирмы заказчика]
5. Сохранить запрос под именем Заказчики и заказы.
6. Запустить запрос на выполнение.
Рис.6.9. Левое внешнее соединение
Упражнение 5. Создать запрос выбирающий заказы, у которых совпадают даты выписки и исполнения (рекурсивное соединение). Для получения рекурсивного соединения в запрос необходимо:
1. Добавить копию таблицы Заказ, а затем создать соединение между полями Дата_выписки и Дата_исполнения (рис.6.10).
2. Для запрета вывода одинаковых строк поставить Да в строке Уникальные значения в диалоговом окне Свойства запроса.
3. Запрос сохранить под именем Исполнение в день выписки.
4. Запустить запрос.
Рис.6.10. Рекурсивное соединение
Использование условий отбора
Условия отбора используются для выбора записи, состоят из выражений (формул фильтрации данных). При вводе выражений условий отбора следует пользоваться операторами. Примеры и назначение некоторых операторов:
| Оператор | Название |
| = , <> | равно / не равно |
| > , < | Больше / меньше |
| >= , <= | больше или равно / меньше или равно |
| Like | как (совпадение) |
| Not | Не (несовпадение) |
| Between | между (задание промежутка) |
| Or | или |
Упражнение 6. Составить запрос, выводящий список фирм-заказчиков, имеющих разный физический адрес и адрес доставки. Для этого следует:
|
|
|
1. Перейти к созданию запроса в режиме конструктора.
2. Добавить таблицы Заказчик, Заказ и Доставка.
3. В бланк запроса внести все необходимые поля согласно рис.6.11.
4. Изменить название поля Адрес таблицы Доставка на Адрес_доставки.
5. В строку Условие отбора поля Адрес_доставки ввести условие: <>[Заказчик].[Адрес]
6. Сохранить запрос под именем Несовпадение адресов.
7. Запретить вывод одинаковых строк в диалоговом окне Свойства запроса (значение Да в строке Уникальные значения).
8. Запустить запрос. Закрыть запрос.
Рис.6.11. Соединение по отношению
Построение выражений
Для построения выражений служит любой пустой столбец бланка запроса, в котором после имени поля записывается выражение (Имя_поля:Вычисляемое_выражение). В него входят заключенные в квадратные скобки названия таблиц (полей) и знаки математических операций. Для разделения имен таблиц и имен полей используется ! (например, Сумма:[Товар]![Цена]*[Заказ]![Количество]). Комбинация клавиш SHIFT+F2 открывает окно Область ввода для записи длинных формул.
Для создания сложных формул используется окно Построитель выражений, которое вызывается нажатием кнопки 
на панели инструментов (рис.6.12).
В нижней части окна содержатся три столбца: 1) список папок с таблицами, запросами, встроенными функциями; 2) объекты выделенной папки; 3) список значений выбранного объекта.
В средней части отображается панель инструментов, содержащая наиболее распространенные арифметические и логические операторы. Весь список доступных операторов находится в папке Операторы в нижней части окна.
Верхняя часть окна содержит поле ввода выражения, элементы которого либо записываются вручную, либо выбираются из папок (двойным щелчком).
Рис.6.12 Окно построителя выражений
В выражениях применяют следующие функции:
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. Запустить запрос. Закрыть запрос.
Упражнение 8. Создать запрос, получающий суммарную стоимость товаров, оформленных в заказах. Для этого:
1. Вызвать запрос, созданный в упражнении 7 и сохранить его под именем Суммарная стоимость командой Файл→Сохранить как.
2. Убрать поля: Название (таблица Заказчик) и Код_заказа (таблица Товар). Для этого надо выделить поле и нажать клавишу Delete.
3. Выполнить команду Вид®Групповые операции (кнопка 
на панели инструментов Конструктор запросов) для добавления поля Групповая операция в бланк запроса (рис.6.14).
Рис.6.14. Запрос с вычисляемым полем и групповой операцией
4. Установить для поля Групповая операция в бланке запроса следующие значения для соответствующих полей: Название – Группировка; Количество – Sum; Стоимость – Sum.
5. Запустить запрос. Закрыть запрос.
Запросы на создание таблиц
Запросы на создание таблицы позволяют создавать таблицы из результатов какого-либо запроса. Как правило, этот тип запросов используется для экспорта данных в другие приложения либо в другую БД.
Упражнение 9. Создать таблицу Адрес_доставки с информацией о клиентах, имеющих разные физический адрес и адрес доставки. Для этого:
1. Вызвать запрос на выборку, созданный в упражнении 6.
2. Сохранить его под именем Создание таблицы командой Файл→Сохранить как.
3. Воспользоваться командой Запрос®Создание таблицы основного меню (кнопкой 
Тип запроса на панели инструментов Конструктор запросов) и в появившемся ниспадающем меню выбрать 
4. В появившееся диалоговое окно Создание таблицы ввести имя новой таблицы – Адрес_доставки (рис.6.15).
Рис.6.15. Диалоговое окно Создание таблицы
5. Сохранить запрос. Запустить запрос (при этом в БД создаётся новая таблица). Закрыть запрос.
6. В Панели объектов БД выбрать объект Таблицы и убедиться в появлении новой таблицы с именем Адрес_доставки.
Запрос на добавление.
Запрос этого типа используется, если в таблицу требуется добавить новые строки данных. Все запросы на добавление помечаются в окне БД значком 
. Следует помнить, что для добавления данных необходимо, чтобы имена полей запроса и полей таблицы, в которую будут вноситься значения, были одинаковы и имели одинаковый тип данных.
Упражнение 10. Добавить в таблицу Адрес_доставки записи о совпадающих адресах заказчиков (запрос на добавление). Для решения необходимо выполнить следующие действия:
1. Вызвать запрос на выборку, созданный в упражнении 3 и сохранить его под именем Добавление в таблицу командой Файл→Сохранить как.
2. Выбрать команду Запрос®Добавление.
3. В открывшемся диалоговом окне Добавление указать таблицу, к которой будут добавлены записи – Адрес_доставки.
4. Сохранить запрос (рис.6.16) в БД.
Рис.6.16. Запрос на добавление
5. После запуска запроса на добавление данных к таблице в таблицу Адрес_доставки добавляются новые данные, которые можно просмотреть/отредактировать, перейдя к таблице Адрес_доставки в окне БД.
Запрос на удаление.
Запросы на удаление позволяют удалить из таблицы записи, соответствующие некоторым критериям. Перед удалением записей рекомендуется вывести их, используя запрос на выборку, а затем этот запрос преобразовать в запрос на удаление. Все запросы на удаление помечаются в окне БД значком 
При выполнении запросов на удаление следует помнить, что удаление записей в родительской таблице (со стороны «один»), которым соответствуют записи в дочерней таблице (со стороны «многие»), нарушает условие целостности данных, поэтому связанные записи в таблице со стороны «многие» становятся «висячими». Для уничтожения данных необходимо использовать каскадное удаление: сначала требуется удалить записи из таблицы с отношением «многие», а затем из таблицы с отношением «один».
Упражнение 11. Удалить из таблицы Адрес_доставки записи, которые удовлетворяют следующим критериям: название фирмы заказчика содержит первую букву М либо букву о в середине названия и в физическом адресе фирмы (запрос на удаление). Для создания запроса следует:
1. Выбрать создание запроса в режиме конструктора и добавить таблицу, из которой будут удаляться записи (в нашем случае Адрес_доставки).
2. Выбрать команду Запрос®Удаление.
3. При переходе к режиму на удаление в бланке запроса исчезают строки Сортировка и Вывод на экран, а появляется строка Удаление.
4. Заполнить бланк запроса (рис.6.17) и сохранить запрос под именем Удаление строк.
Рис.6.17. Запрос на удаление
5. После запуска запроса, записи в таблице удаляются навсегда. Для просмотра результата надо перейти к таблице Адрес_доставки в окне БД.
Запрос на обновление
Запрос на обновление изменяет значения существующих полей таблицы в соответствии с критериями запроса. Все запросы на обновление помечаются в окне БД значком 
. Перед запуском запросов на модификацию рекомендуется создать резервную копию таблицы во избежание потери необходимых данных.
Упражнение 12. Обновить данные в таблице Адрес_доставки в соответствии со следующими критериями: для фирмы «Восток» изменить физический адрес на «Кленовая, 10», адрес доставки – «Сосновая, 6». Для этого:
1. Выбрать создание запроса в режиме конструктора и добавить таблицу Адрес_доставки.
2. Выбрать команду Запрос®Обновление либо воспользоваться кнопкой 
(тип запроса) на панели инструментов Конструктор запросов.
3. При переходе к режиму на обновление в бланке запроса исчезают строки Сортировка и Вывод на экран, а появляется строка Обновление.
4. Заполнить бланк запроса с учетом изменений и условий отбора (рис.6.18) и сохранить запрос в БД под именем Обновление.
Рис.6.18. Запрос на обновление
5. После запуска запроса на обновление данных в таблице, записи в таблице Адрес_доставки обновляются в соответствии с требованиями запроса.
6. Просмотреть результат (перейти к таблице Адрес_доставки).
Перекрестные запросы
Перекрестные запросы (кросс-таблицы) представляют собой двумерные таблицы, в которых представлена некоторая вычисляемая статистическая информация. Перекрестный запрос всегда имеет три элемента: заголовки строк (до трех значений полей); заголовки столбцов (значение одного поля); значение (значение одного поля либо вычисляемое значение, располагаемое на пересечении строк и столбцов итоговой таблицы). Запросы могут содержать: условия отбора; групповые операции; сортировку; вычисляемые поля.
Перекрестные запросы позволяют: получить большой объем данных в компактном виде; формировать графики и диаграммы в MS Access; просматривать уровни детализации. На основе перекрестного запроса удобно создавать сводные таблицы и диаграммы в MS Access.
Для создания перекрестного запроса в бланк запроса добавляются Групповая операция и Перекрестная таблица. Все перекрестные запросы помечаются в окне БД значком 
.
Упражнение 13. Получить стоимость заказов с учетом товаров, приобретенных после 26 июня 2001 г. (перекрестный запрос). Для этого:
1. Выбрать создание запроса в режиме конструктора и добавить таблицы: Заказ, Артикул_заказа, Товар.
2. Выбрать команду Запрос®Перекрестный либо воспользоваться кнопкой 
на панели инструментов Конструктор запросов. При этом в бланк запроса добавляется строка перекрестная таблица.
3. Заполнить поля Код_заказа и Наименование_товара как на рис.6.19.
Рис.6.19. Бланк перекрестного запроса для поля Код_заказа
4. Заполнить поле вычисляемого значения стоимости товара, расположенного на пересечении строк и столбцов показано на рис.6.20.
Рис.6.20. Заполнение поля вычисляемого значения стоимости товара
5. Заполнить поле итогового значения стоимости заказа (рис.6.21).
Рис.6.21. Заполнение бланка для итогового значения стоимости заказа
6. Заполнить поле подсчета количества номенклатуры заказа (рис.6.22).
Рис.6.22. Бланка поля для подсчета количества номенклатуры заказа
7. Заполнить поле Дата_выписки как показано на рис.6.23.
Рис.6.23. Заполнение бланка поля Дата_выписки перекрестного запроса
8. Сохранить запрос в БД под именем Перекрестный запрос.
9. После запуска запроса на выполнение результаты выводятся в виде двумерной таблицы.
Дата добавления: 2020-01-07; просмотров: 264; Мы поможем в написании вашей работы! |
Мы поможем в написании ваших работ!