III. Запрос с параметром
Запрос с параметром — это запрос, при выполнении отображающий в собственном диалоговом окне приглашение ввести данные, например условие для возвращения записей или значение, которое требуется вставить в поле.
Можно разработать запрос, выводящий приглашение на ввод нескольких единиц данных, например двух дат. Затем Microsoft Access может вернуть все записи, приходящиеся на интервал времени между этими датами.
Когда вы запускаете запрос с параметром, он, в отличие от обычного запроса на выборку, не выполняется сразу, а сначала в диалоговом окне просит вас уточнить некоторые условия отбора. Например, нам нужно получить информацию о поставщике с определенным названием.
ЗАДАНИЕ 3
Создадим запрос, который выводит информацию о заказах, проведенных определенным менеджером. Для этого:
Откройте Конструктор запросов.
В окне Добавить таблицу добавьте таблицы: Ассортимент, Заказы, Поставщики (рисунок 136).
Рисунок 136 – Добавление таблиц
В строку Поле добавьте поля «Менеджеры», «Автомобиль» из таблицы Заказы; «Стоимость» из таблицы Ассортимент; «Название» из таблицы Поставщики (рисунок 137).
Рисунок 137 – Бланк запроса
Для поля «Менеджер» создайте следующее условие отбора - [Укажите фамилию менеджера:]. Обратите внимание, что условие обязательно пишется в квадратных скобках. Данное условие представляет собой вопрос, который будет задан пользователю при запуске запроса на исполнение. Ответ пользователя будет подставлен в эту ячейку в качестве условия отбора. Результат представлен на рисунке 138.
Рисунок 138 – Условия отбора
Запустите запрос. В окне появилась ячейка с вопросом (рисунок 139). Введите фамилию Белоусова. Запрос выведет результат (см. рисунок 140)
Рисунок 139 – Параметр запроса
Рисунок 140 – Результат запроса
Сохраните запрос с названием «Менеджер».
ЗАДАНИЕ 4
По коду заказа осуществите выборку даты заказа, срока доставки, и наименования поставщика. Сохраните запрос под именем «Код заказа».
Выведите срок выполнения и срок доставки заказа, количество автомобилей, определенного поставщика (условие отбора). Сохраните запрос под именем «Заказ от поставщика».
Создайте запрос, выводящий информацию о заказах, сделанных за указанный период времени определенным менеджером. Сохраните запрос под именем «Период заказа».
Результат покажите преподавателю.
IV. Перекрестный запрос
Перекрестный запрос — это запрос, в котором происходит статистическая обработка данных, результаты которой выводятся в виде таблицы, очень похожей на сводную таблицу Excel.
Перекрестный запрос подсчитывает сумму, среднее, число значений или выполняет другие статистические расчеты, после чего результаты группируются в виде таблицы по двум наборам данных, один из которых определяет заголовки столбцов, а другой заголовки строк.
В качестве примера разберем следующую задачу, нам нужно вывести информацию для отчетности о стоимости автомобилей, проданных определенным менеджером. Таблица должна содержать поля «Код заказа», «Марка автомобиля», «Итоговая сумма», «Фамилия менеджера 1» …. «Фамилия менеджера N».
Для того чтобы создать перекрестный запрос, результатом которого будут данные взятые из нескольких таблиц, необходимо создать простой запрос, включающий поля необходимые для перекрестного запроса, а потом на основе простого запроса создать перекрестный.
ЗАДАНИЕ 5
Для создания простого запроса воспользуемся мастером запросов:
Щелкните по вкладке Создание.
В группе Другие нажмите кнопку Мастер запросов. На экране появится первое диалоговое окно Мастера запросов (рисунок 141).
Рисунок 141 – Первое окно Мастера запросов
Убедитесь, что в окне Мастера отмечен вариант Простой запрос.
Нажмите кнопку Ок. На экране появится следующее окно мастера (рисунок 142).
Рисунок 142 – Второе окно Мастера запросов
Раскройте список Таблицы и запросы и выберите источник данных для создания нового запроса. В нашем случае это таблица Заказы. Из этой таблицы нам необходимо выбрать поля «Код заказа» и «Менеджер» (рисунок 143). Для этого перенесите требуемые поля из списка Доступные поля в список Выбранные поля. Последовательно щелкайте по этим полям в списке Доступные поля и нажимайте кнопку
.
Рисунок 143 – Поля из таблицы Заказы
В связи с тем, что для решения поставленной перед нами задачи, необходимо чтобы в простом запросе фигурировали поля из двух таблиц, Заказы и Ассортимент, то добавьте из таблицы Ассортимент поля «Марка» и «Стоимость». Для этого повторите все действия из пункта 5, данного задания, но уже для таблицы Ассортимент (рисунок 144).
Рисунок 144 – Поля из таблицы Ассортимент
Нажмите кнопку Далее. На экране появится третье окно Мастера запросов (рисунок 145).
Рисунок 145 -Третье окно Мастера запросов
Так как необходимо вывести все поля каждой записи, выберите пункт «подробный (ввод каждого поля каждой записи)» и нажмите кнопку Далее.
Перед вами откроется последнее окно мастера. В поле Имя запроса введите название нового запроса «Отчетность по менеджерам». Установите переключатель Открыть запрос для просмотра данных (рисунок 146), чтобы просмотреть результат выполнения запроса.
Рисунок 145 – Последнее окно Мастера запросов
Нажмите кнопку Готово, чтобы завершить работу Мастера. Access 2007 создаст новый запрос с указанным именем и сохранит его в текущей базе данных. В области переходов появится название только что созданного запроса, а в главном окне Access 2007 отобразится таблица с выбранными по этому запросу данными.
ЗАДАНИЕ 6
На основе простого запроса «Отчетность по менеджерам» создадим перекрестный запрос, который будет решать, поставленную ранее задачу, т.е. выводить информацию для отчетности о стоимости автомобилей, проданных определенным менеджером.
Запустите Мастер запросов.
Выберите в первом окне мастера пункт Перекрестный запрос (рисунок 146) и нажмите кнопку Ок.
Рисунок 146 – Первое окно Мастера запросов
Перед вами откроется второе окно мастера запросов, в данном окне установите переключатель в позицию Запросы (рисунок 147).
Рисунок 147 – Второе окно мастера
В окне, где перечисляются, имеющиеся в вашей базе данных запросы, выберите запрос «Отчетность по менеджерам» двойным щелчком мыши.
Перед вами откроется третье окно мастера запросов. В данном окне вам предлагается выбрать поля, значения которых будут использоваться в качестве заголовков строк. В вашем случае необходимо выбрать поля «Код заказа» и «Марка». Обратите внимание на то, что в окне образец появились данные поля в качестве заголовков (рисунок 148). Нажмите кнопку Далее.
Рисунок 148 – Третье окно Мастера запросов
Перед вами откроется четвертое окно мастера. В данном окне вам предлагают выбрать поле в качестве заголовков столбцов. Вам необходимо одним щелчком левой клавиши мышки выбрать поле «Менеджеры». В результате после создания запроса заголовками столбцов будут фамилии менеджеров (рисунок 149). Нажмите кнопку Далее.
Рисунок 149 – Четвертое окно Мастера запросов
Перед вами откроется пятое окно мастера запросов. В данном окне вам предлагают выбрать, какие вычисления будут проводиться для каждой ячейки на пересечении столбцов и строк. Выберите пункт Сумма, если не стоит галочка у пункта Вычислять итоговые значения поставьте ее и нажмите Далее (рисунок 150).
Рисунок 150 – Пятое окно Мастера запросов
В шестом окне Мастера запросов, при необходимости поменяйте Имя запроса, выберите пункт Просмотреть результаты запроса и нажмите Готово (рисунок 151).
Рисунок 151 – Шестое окно Мастера запросов
Результат выполнения запроса представлен на рисунке 152.
Рисунок 152 - Результат выполнения запроса
ЗАДАНИЕ 7
Создайте перекрестный запрос, предоставляющий информацию о сумме стоимостей автомобилей каждой марки, которые были заказаны оптовыми и розничными клиентами. Сохраните запрос под именем «Оптовые и розничные клиенты (перекрестный)»
Создайте перекрестный запрос, выводящий среднюю стоимость автомобиля каждой марки, от разных поставщиков. Сохраните запрос под именем «Средняя стоимость автомобиля (перекрестный)».
Создайте перекрестный запрос, выводящий информацию о количестве заказанных автомобилей с типом кузова седан и хэчбэк с указанием фамилии менеджеров, оформлявших заказы. Сохраните запрос под именем «Седан и хэчбэк (перекрестный)».
Продемонстрируйте результаты преподавателю.