Упражнение № 29: Разработать запросы к БД

Запросы позволяют выбрать нужные данные из таблиц данных. Типы запросов, создаваемых в Microsoft Access:

• Запросы на выборку;
• Запросы с параметрами;
• Перекрестные запросы;
• Запросы на изменение (запросы на создание таблицы, удаление, обновление, добавление записей);
• Запросы SQL (запросы на объединение, запросы к серверу, управляющие запросы, подчиненные запросы).

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

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

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

Часто запросы в Microsoft Access создаются автоматически, и пользователю не приходится самостоятельно их создавать.

• Для создания запроса, являющегося основой формы или отчета, попытайтесь использовать мастер форм или мастер отчетов. Они служат для создания форм и отчетов. Если отчет или форма основаны на нескольких таблицах, то с помощью мастера также создаются их базовые инструкции SQL. При желании инструкции SQL можно сохранить в качестве запроса.
• Чтобы упростить создание запросов, которые можно выполнить независимо, либо использовать как базовые для нескольких форм или отчетов, пользуйтесь мастерами запросов. Мастера запросов автоматически выполняют основные действия в зависимости от ответов пользователя на поставленные вопросы. Если было создано несколько запросов, мастера можно также использовать для быстрого создания структуры запроса. Затем для его наладки переключитесь в режим конструктора.
• Для создания запросов на основе обычного фильтра, фильтра по выделенному фрагменту или фильтра для поля, сохраните фильтр как запрос.
• Если ни один из перечисленных методов не удовлетворяет требованиям, создайте самостоятельно запрос в режиме конструктора.

Окно конструктора запросов содержит список полей и бланк для создания запроса по образцу (QBE).

1. СОЗДАНИЕ ЗАПРОСА С ПОМОЩЬЮ КОНСТРУКТОРА

1.1 Создать запрос, включающий все поля таблицы ДОГОВОРА и поля таблицы ПОСТАВЩИКИ.

Здесь и далее приводятся названия объектов для выбора в диалоговых окнах.

• ЗАПРОСЫ, СОЗДАТЬ, КОНСТРУКТОР
• ДОГОВОРА, ДОБАВИТЬ и ПОСТАВЩИКИ, ДОБАВИТЬ и ЗАКРЫТЬ
• Мышью перетащить имя поля из верхней части окна в позицию ПОЛЕ нижней части окна (выполнить для каждого поля)
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос

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

• Сделать копию запроса
• Удалить лишние поля, оставив НОМЕР ДОГОВОРА, НАИМЕНОВАНИЕ ПОСТАВЩИКА, ДАТА ЗАКЛЮЧЕНИЯ ДОГОВОРА, ЦЕНА, КОЛИЧЕСТВО
• Добавить расчетное поле СУММА:[ЦЕНА]*[КОЛИЧЕСТВО], для этого в позиции ПОЛЕ вызовите контекстное меню (правой клавишей мыши), выберите ПОСТРОИТЬ
• В колонке СУММА в позиции СОРТИРОВКА поставьте значение по УБЫВАНИЮ
• В колонке ДАТА ЗАКЛЮЧЕНИЯ в позиции УСЛОВИЕ ОТБОРА постройте выражение >#01.01.99#And<#01.02.99# (или с другими датами) для отбора договоров только за один месяц
• На панели инструментов выберите объект НАБОР ЗНАЧЕНИЙ, введите значение 1
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос

1.3. Построить запрос по расчету общей суммы всех договоров с каждым поставщиком.

• Сделать копию предыдущего запроса
• Снять все ограничения по УСЛОВИЯМ ОТБОРА, СОРТИРОВКИ, НАБОРУ ЗНАЧЕНИЙ
• Снять галочки в позиции ВЫВОД НА ЭКРАН для всех полей кроме НАИМЕНОВАНИЕ ПОСТАВЩИКА, СУММА
• На панели инструментов выбрать объект ГРУППИРОВКА
• Для поля СУММА в позиции ГРУППИРОВКА вставьте функцию SUM
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос

2. ПЕРЕКРЕСТНЫЙ ЗАПРОС

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

• ЗАПРОСЫ, СОЗДАТЬ, ПЕРЕКРЕСТНЫЙ ЗАПРОС
• ДОГОВОРА, поле для заголовка строк КОД ПОСТАВЩИКА
• Поле для заголовка столбцов КОД ТОВАРА
• Для заполнения таблицы расчетными значения выберите поле КОЛИЧЕСТВО и функцию SUM
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос

3. ЗАПРОС НА ПОИСК ПОВТОРЯЮЩИХСЯ ЗАПИСЕЙ

Найти поставщиков, которыми заключено более одного договора

• ЗАПРОС, СОЗДАТЬ, ПОВТОРЯЮЩИЕСЯ ЗАПИСИ
• ДОГОВОРА
• КОД ПОСТАВЩИКА
• Для вывода дополнительных сведений выберите НОМЕР ДОГОВОРА
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос

4. ЗАПРОС НА ПОИСК ЗАПИСЕЙ БЕЗ ПОДЧИНЕННЫХ (ищет записи, для которых нет записей в связанной, подчиненной таблице)

Найти поставщиков, с которыми еще не заключили договора

• ЗАПРОС, СОЗДАТЬ, ЗАПИСИ БЕЗ ПОДЧИНЕННЫХ
• ПОСТАВЩИКИ
• ДОГОВОРА
• КОД ПОСТАВЩИКА
   КОД ПОСТАВЩИКА
• Для вывода дополнительных сведений выберите НАЗВАНИЕ ПОСТАВЩИКА, ТЕЛЕФОН
• Сохранить запрос
• Посмотрите результат в режиме таблицы
• Закрыть запрос