Пошаговое выполнение работы
Теоретические сведения
1. Самым простым и часто используемым типом запроса является Запрос на выборку, который является производной таблицей и позволяет указать, какие поля из исходных таблиц будут отображаться, ввести критерий отбора записей и определять порядок сортировки записей.
2. При создании запроса надо выполнить в общем случае четыре операции:
- указать, Какие поля и из каких таблиц надо Включить в запрос;
- Описать вычисляемые поля, т. е. поля, значения которых являются функциями значений существующих полей;
- Описать групповые операции над записями исходных таблиц (выполнить объединение набора записей в группу по какому-либо признаку);
- Указать условие отбора, т. е. логическое выражение, которое позволит включить в выборку только определенные записи.
3. После выполнения запроса на выборку создается таблица, содержащая отобранные данные. Но эта таблица не сохраняется в БД, а существует только во время выполнения запроса.
4. При изменении данных в запросе будут внесены соответствующие изменения и в исходную таблицу.
5. Запрос может быть создан не только по таблицам, но и по другим запросам.
6. Создание запроса можно осуществить В режиме конструктора или с использованием Мастера запросов.
Пошаговое выполнение работы
1. Загрузитe Microsoft Access.
2. На вкладке Файл Выберите команду открытия базы данных – Открыть Базу данных, созданную в практической работе № 2.
3. Создадим Простой запрос по одной таблице В режиме конструктора. Предположим, что требуется выдать всю информацию о продуктах, имеющихся на складе фирмы. Для этого следует в меню Создание Выбрать – Конструктор запросов Таблиц.
4. На экране появится Бланк запроса. В верхней части бланка должны размещаться списки полей таблиц или запросов, на основе которых создается новый запрос. В окне Добавление таблиц поочередно выбираем из списка имена нужных таблиц, нажимая кнопку 
, и закрываем окно Добавление таблиц. Исходя из нашей задачи, выберите в качестве исходной таблицу Продукты.
5. В нижней части бланка выполняется вся работа по созданию запроса. Каждый столбец бланка соответствует одному полю запроса. Чаще всего поле выбирается из исходной таблицы. Имя поля задается из предложенного списка в ячейке строки Поле или перетаскивается из верхней части бланка. Поочередно укажите из таблицы Продукты все поля, кроме поля Код продукта.
Вторая строка Нижней части бланка запроса содержит имя таблицы, из которой выбрано поле. Оно устанавливается автоматически при выборе поля.
Третья строка – сортировка, Указывает способ упорядочивания записей по данному полю. Укажите сортировку по алфавиту поля Наименование.
Четвертая строка Определяет, будут ли значения данного поля Выведены на экран при выполнении запроса. Все поля следует вывести.
Условие отбора Пока не задаем.
| Верхняя часть бланка. |
| Нижняя часть бланка. |
Рис. 1. Бланк запроса
6. В результате получится заполненный бланк запроса следующего вида (см. рис. 1). Сохраните запрос под именем Информация о продуктах, закройте запрос.
7. Создадим Простой запрос по одной таблице С помощью Мастера. Предположим, что требуется просмотреть список всех клиентов фирмы без указания их кодов. Для этого следует в меню Создание Выбрать – Мастер запросов. В первом окне Мастера (рис. 2) из списка поля «Таблицы и запросы» надо выбрать таблицу Клиенты, по которой будет строиться запрос. В списке Доступные поля отобразится перечень полей выбранной таблицы. Необходимые для построения запроса поля поочередно следует переместить в список Выбранные поля с помощью кнопки 
. В нашем случае это поля Наименование и Адрес. Затем нажать кнопку 
. На заключительном шаге Мастер позволяет задать имя запроса – Информация о клиентах и указать на дальнейшие действия: просмотреть результат запроса или заняться изменением его структуры. Выберем первый вариант и нажмем кнопку 
. После просмотра результатов работы запроса, закройте его.
Рис. 2. Мастер запросов
8. Создадим Запрос с вычислениями. Допустим, требуется выдать список имеющихся на складе продуктов и их стоимость. Откроем запрос Информация о продуктах в режиме конструктора. Нас интересует наименование продукта и его стоимость. Стоимость определяется как произведение цены на количество продукта. Поля Стоимость нет в исходной таблице, по которой был построен запрос, его следует сформировать как вычисляемое. Для этого надо установить курсор в первую строку пустого столбца нижней части бланка запроса и ввести выражение:
Стоимость: [цена]*[кол-во на складе]
9. Можно ввести выражение с помощью Построителя выражений, который вызывается кнопкой 
Построить Панели инструментов. В верхней части окна построителя (рис. 3) формируется выражение, в нижней части окна выбираются поля, участвующие в создании выражения. Список слева в нижней части окна представляет папки всех имеющихся в БД таблиц, запросов, форм и отчетов. Первая папка представляет текущий объект (у нас это запрос Информация о продуктах). После выделения любого из объектов в средней панели отображаются его поля и другие элементы управления в средней панели. При выборе одного из этих элементов в правой панели отображаются его свойства. В нашем примере можно указать на исходную таблицу Продукты и двойным щелчком поочередно выбрать нужные для построения выражения поля. После подготовки выражения нажмите 
. В поле Вывод на экран текущего столбца бланка запроса надо установить флажок, а в столбцах Цена и Кол-во на складе можно снять флажки. Сохраните созданный запрос под именем Стоимость (Файл / Сохранить объект как), выполните созданный запрос и закройте его.
Рис. 3. Построителя выражений
10. Создадим запрос С условием отбора. По таблице Продукты отберем список продуктов, количество которых на складе не менее 2000 единиц. Для этого надо открыть запрос Информация о продуктах в режиме Конструктора, в поле Условие отбора столбца Кол-во на складе задать условие >= 2000. Можно задать несколько условий отбора по нескольким полям. Если все условия отбора будут заданы в одной строке, то Microsoft Access воспримет это как логическое "И", если в разных – то как логическое "ИЛИ". В поле Вывод на экран текущего столбца бланка запроса надо установить флажок. Сохранив под именем Кол-во не меньше 2000, Выполните созданный запрос и закройте его.
11. Создайте запрос с условием отбора для Определения списка продуктов, цена которых не меньше 20 р. и количество 1000 единиц, и сохраните его под именем Дороже 20 рублей. Выполните созданный запрос и закройте его.
12. Требуется сформировать запрос Для определения списка продуктов, цена которых ниже средней и сохранить его под именем Дешевые продукты.
Если при формировании запроса необходимо указать значения больше/меньше среднего по полю, то в качестве условия отбора в соответствующем столбце записывается выражение:
Оператор отношения(SELECT AVG ([имя поля]) FROM [имя таблицы])
13. Создадим Запрос с параметром по условию отбора. Такой запрос позволяет задавать конкретное условие отбора непосредственно при выполнении этого запроса. Условие в общем виде выглядит так:
Оператор отношения [текст сообщения].
14. По таблице Продукты надо выдать список товаров, количество которых на складе не менее заданного пользователем в запросе. Сначала требуется создать простой запрос, включающий все поля таблицы Продукты, затем открыть его в режиме конструктора и в качестве условия отбора столбца Кол-во на складе записать выражение: >=[на складе не менее]. Сохраните запрос под именем Продукты по количеству и выполните его. При выполнении этого запроса программа будет запрашивать интересующее Вас количество продуктов.
15. Задание Параметра в диапазоне. По таблице Заказы определите объем продаж за задаваемый период времени. Следует построить простой запрос по таблице Заказы, содержащий поля Номер заказа, Код продукта, Кол-во, Дата поставки. Затем в режиме конструктора надо записать условие отбора в столбце Дата поставки следующего вида:
Between [задайте начальную дату] and [задайте конечную дату].
Условие можно сформировать с помощью Построителя Выражений (between – это оператор сравнения). Сохраните запрос под именем Объем продаж и выполните его. При выполнении этого запроса программа будет запрашивать начальную и конечную дату интересующего Вас периода.
16. Рассмотрим создание итогового запроса. Итоговый запрос Позволяет получить итоговые данные по всей таблице или сгруппировать данные по общим признакам и выполнить групповые операции. Требуется определить По таблице Заказы – Объем продаж каждого вида товара (сколько заказов каждого вида товара и в каком количестве товар заказан). Можно строить итоговый запрос в режиме конструктора или с помощью Мастера. Выберем первый способ. В режиме конструктора создадим простой запрос по таблице Заказы, выбрав поля Номер заказа, Код продукта и Кол-во. Затем нажмем кнопку Итоги 
. В нижней части бланка запроса (рис. 4) появится строка Групповая операция.
Выпадающий список в ячейке Групповая операция содержит двенадцать функций. Вот некоторые из них:
- Группировка, Означает, что данное поле будет основой для группировки данных. В нашем примере это поле Код продукта.
- Sum позволяет суммировать все значения данного поля. У нас это поле Кол-во.
- Count определяет общее количество записей, отобранных по запросу. Установим эту функцию в поле Номер заказа, чтобы подсчитать количество заказов по каждой группе продуктов.
Сохраните запрос под именем Итоги по продуктам. Выполните запрос.
Рис. 4. Нижняя часть бланка запроса
17. С помощью Мастера создайте итоговый запрос для решения той же задачи. Обратите внимание на второе окно Мастера: надо не только установить параметр Итоговый, но и нажать кнопку Итоги, чтобы задать вычисляемые поля. Сохраните запрос под именем Итоги по продуктам1. Выполните запрос.
18. С помощью Мастера создайте Перекрестный запрос. В результате выполнения такого запроса создается сводная таблица. По таблице Заказы требуется создать таблицу Для просмотра дат исполнения заказов по конкретным продуктам с указанием их количества. Во вкладке Создание à Мастер запросов выбрать строку Перекрестный запрос. На первом шаге работы Мастера перекрестных запросов следует выбрать исходную таблицу Заказы и нажать кнопку . В следующем окне требуется указать поле, значения которого будут использованы как заголовки строк Код продукта и нажать кнопку 
. На следующем шаге надо указать поле для задания заголовков столбцов Дата поставки и нажать кнопку . В следующем окне необходимо указать временной интервал Дата для группировки данных и нажать кнопку 
. После этого указывается, какие вычисления будут производиться на пересечении строк и столбцов (Кол-во, функция Сумма) (рис. 5). Если требуется, можно вычислить итоговые значения. В последнем окне задается название запроса Поставка заказов и выбирается режим выполнения запроса. Затем надо нажать кнопку 
(рис. 6).
Рис. 5 Создание Перекрестного запроса
Рис. 6 выполнения Перекрестного запроса
19. Запрос Записи без подчиненных. Такой запрос позволяет в одной таблице найти записи, которым нет соответствия в другой таблице, связанной с первой. Например, Требуется выдать список продуктов, не востребованных ни в одном заказе. Таблица, где объекты обязательно существуют, считается главной. В нашем случае это таблица Продукты. Таблица, в которой некоторых объектов может не быть, называется подчиненной. У нас это таблица Заказы. Во вкладке Создание à Мастер запросов выбрать строку Записи без подчиненных. На первом шаге Мастера надо указать главную таблицу: Продукты и нажать кнопку . Затем указывается подчиненная таблица – Заказы и нажимается кнопка 
. В следующем окне задается связующее эти таблицы поле Код продукта и нажимается кнопка . После этого выбираются поля с необходимой информацией о не заказанном продукте (рис. 7) и нажимается кнопка . В последнем окне задается название запроса Не востребованные продукты И выбирается режим выполнения запроса. Затем надо нажать кнопку .
Рис. 7 Запрос записи без подчиненных
20. Все рассмотренные выше виды запросов могут комбинироваться. Кроме того, можно создавать запросы по нескольким таблицам и по запросам.
21. Создадим Запрос по нескольким таблицам. Требуется выдать список всех заказов, отсортированных по клиентам, с указанием стоимости заказа, имени клиента и наименования продукта. Запрос может быть создан в режиме конструктора или с помощью Мастера. Если создавать такой запрос В режиме конструктора, то требуется добавить в бланк запроса все три таблицы, а затем разместить в нижней части бланка все нужные поля:
- Из таблицы Заказы – Номер заказа, Дата поставки, Кол-во.
- Из таблицы Клиенты – Наименование.
- Из таблицы Продукты – Наименование, Цена.
Затем следует создать новое Вычисляемое поле (вручную или с помощью Построителя выражений):
Стоимость:[Продукты]![Цена]*[Заказы]![Кол-во]
После этого надо сохранить запрос под именем Список заказов и выполнить его.
22. Если создавать такой запрос с помощью Мастера, то на первом шаге требуется поочередно указывать таблицы и выбирать из них нужные поля. Остальные шаги выполняются как обычно. На последнем шаге надо указать, что требуется перейти в режим конструктора. В режиме конструктора следует создать вычисляемое поле Стоимость. После этого надо сохранить запрос под именем Список заказов_1 и выполнить его.
23. Построим запрос по запросу. Требуется подготовить список продуктов, не востребованных за определенный месяц. Сначала по таблице Заказы строится запрос Даты заказов с условием или параметром по дате (задание диапазона). Затем строится запрос записи без подчиненных по таблице Продукты (главная) и запросу Даты заказов (подчиненный). После этого запрос надо сохранить под именем Не востребованные за месяц продукты и выполнить его.
Предъявите преподавателю:
Запросы Даты заказов, Дешевые продукты, Дороже 20 рублей, Информация о клиентах, Информация о продуктах, Итоги по продуктам, Итоги по прощдуктам_1, Количество не меньше 2000, Невостребованные за месяц продукты, Невостребованные продукты, Объем продаж, Поставка заказов, Продукты по количеству, Список заказов, Список заказов_1, Стоимость.
Контрольные вопросы
1. Для чего используются запросы.
2. Какие операции выполняются при создании запроса.
3. Как создается вычисляемое поле в запросе.
4. Как задать условия отбора в запросе.
5. Для чего используется итоговый запрос.
6. Как формируется перекрестный запрос.
7. Для чего используется запрос записи без подчиненных.