Запросы являются основным средством просмотра, отбора, изменения и анализа информации, которая содержится в одной или нескольких таблицах базы данных.
Существуют различные виды запросов, но наиболее распространенными являются запросы на выборку.
1. Откройте базу данных «Фирма», созданную заранее.
2. Выполните команду: вкладка ленты Создание – Мастер запросов – Простой запрос.
3. В появившемся диалоговом окне (рисунок 3) укажите таблицу Сотрудники и выберите поля Фамилия, Имя, Телефон. Нажмите кнопку Далее.
Рисунок 3. Создание простого запроса
4. Введите имя запроса – Телефоны – и нажмите кнопку Готово. Перед вами появится запрос, в котором можно просмотреть телефоны сотрудников.
5. Следующий запрос попробуйте создать с помощью Конструктора, для этого выполните команду: вкладка ленты Создание – Конструктор запросов. В диалоговом окне Добавление таблиц выберите таблицу Клиенты и щелкните на кнопке Добавить, а затем – на кнопке Закрыть.
6. Чтобы перенести нужные поля в бланк запроса, необходимо по ним дважды щелкнуть левой кнопкой мыши (рисунок 4).
Рисунок 4. Создание запроса в режиме Конструктора
7. Чтобы отсортировать записи в поле Название компании в алфавитном порядке, необходимо в раскрывающемся списке строки Сортировка выбрать пункт по возрастанию. Сохраните запрос с именем «Адреса клиентов».
8. Самостоятельно создайте запрос «Дни рождения», в котором можно будет просмотреть дни рождения сотрудников.
9. Допустим, мы хотим узнать, у кого из сотрудников день рождения в текущем месяце, например в апреле. Для этого откройте запрос в режиме Конструктора. В строке Условие отбора для поля «Дата рождения» введите значение *.04.* . В данной записи * означают, что дата и год рождения могут быть любыми, а месяц 4-ым (т.е. апрель). После этого окно запроса должно выглядеть так, кА оно представлено на рисунке 5.
Рисунок 5. Создание запроса
10. Закройте Конструктор и просмотрите полученный результат. Если в запросе дни рождения нет ни одной записи, значит, в таблице сотрудники нет ни одного человека, родившегося в апреле. Добавьте в таблицу Сотрудники несколько человек, родившихся в апреле, и просмотрите, как изменится запрос. Запросы автоматически обновляются при каждом открытии.
11. Если нам нужно узнать, кто из сотрудников родился в мае, то придется создавать новый запрос или изменить условие в существующем запросе Дни рождения. Данная процедура является неудобной и занимает много времени. Если приходится часто выполнять запрос, но каждый раз с новыми значениями условий используется запрос с параметром. При запуске такого запроса на экран выводится окно для ввода значения в качестве условия отбора. Чтобы создать запрос с параметром, пользователю необходимо ввести текст сообщения в строке Условие отбора бланка запроса. (рисунок 6).
Рисунок 6. Создание запроса с параметром
12. Запись Like[Введите дату] означает, что при открытии запроса появится диалоговое окно (рисунок 7) с текстом «Введите дату» и полем для ввода условия отбора. Если ввести условие *.04.*, то в запросе появится список сотрудников родившихся в апреле. Запустите запрос еще раз и введите значение *.05.*, посмотрите, как изменился запрос.
Рисунок 7. Окно для ввода условия отбора
13. Измените запрос «Телефоны» так, чтобы при его запуске выводилось диалоговое окно с сообщением «Введите фамилию». Поскольку в запросе нужно ввести конкретную фамилию, в условии отбора слово Like писать не надо.
14. Измените запрос «Телефоны» так, чтобы при его запуске запрашивались не только фамилия, но и имя сотрудника.
15. Самостоятельно создайте запрос «Выполненные заказы», содержащий следующие сведения: фамилия и имена сотрудника, название компании, с которой он работает, отметка о выполнении и сумма заказа. Данные запроса возьмите в нескольких таблицах. В условии отбора для логического поля Отметка о выполнении введите Да, чтобы в запросе отражались только выполненные заказы.
16. Сделайте так, чтобы столбец Отметка о выполнении не выводился на экран.
17. Создайте запрос Сумма заказа, в котором будут отображаться заказы на сумму более 50 000 руб.
18. Измените запрос, чтобы сумма заказа была от 20 000 до 50 000 руб. для данных запросов в условии отбора можно использовать операторы сравнения >, =, и логические операторы And, Or, Not и другие.
19. Иногда в запросах требуется произвести некоторые вычисления, например посчитать подоходный налог 13% для каждой сделки. Для этого откройте запрос Сумма заказа в режиме Конструктора. В пустом столбце бланка запроса щелкните правой кнопкой мыши на ячейке поле и в появившемся контекстном меню выберите команду Построить. Перед вами появится окно Построитель выражений (рисунок 8), который состоит из трех областей: поля выражения, кнопок операторов и элементов выражения. Сверху располагается поле выражения, в котором оно и создается. Вводимые в это поле элементы выбираются в двух других областях окна Построителя.
20. В левом списке откройте папку Запросы и выделите запрос Сумма заказа. В среднем списке выделите поле Сумма и нажмите кнопку Вставить. Идентификатор этого поля появится в поле Построителя.
21. Щелкните на кнопке * и введите 0,13 (рисунок 8). Таким образом мы посчитаем подоходный налог 13%.
Рисунок 8. Построитель выражений
22. Нажмите кнопку ОК, после чего в ячейке свойства Поле появится значение «Выражение1:[Сумма]*0,13».Замените Выражение1 на Налог и закройте Конструктор. Откройте запрос и посмотрите, что у вас получилось.
23. Используя Построитель выражений, добавьте в запрос Сумма заказа поле Прибыль, в котором будет вычисляться доход от заказа (т.е. сумма минус налог).
24. Создайте запрос Менеджеры, с помощью которого в таблице сотрудники найдите всех менеджеров фирмы.
1. Вычисления в запросах
1.1. Создать запрос Фамилия_СтоимостьЗаказа с полями Производитель, Модель, ДатаЗаказа, Цена, СтоимостьДоставки, Стоимость без доставки (=Цена*Количество), Стоимость с доставкой (=Цена*Количество+СтоимостьДоставки).
1.2. Создать запрос с параметром Фамилия_Цены в Евро ( в котором цены переведены в евро, а курс евро вводится как параметр) с полями Производитель, Модель, ДатаЗаказа, Цена, Цена в евро (=Цена/Курс), СтоимостьДоставки, СтоимостьДоставки в евро. Установить для полей Цена в евро и СтоимостьДоставки в евро установить соответсвующий денежный формат.
2. Использование в запросах групповых операций
Создать запрос Фамилия_Итоги1 (Фамилия_Итоги2. ) на основе таблиц : Модели, Склад
2.1. Получить данные о количестве моделей и количестве экземпляров на складе каждого производителя
2.2. Количество моделей с цветным дисплеем по группам производителей и количество экземпляров на складе
2.3. Сгруппировать данные по производителям и типам батареи, узнать максимальную емкость для каждой группы
2.4. Для каждого производителя получить минимальный, максимальный и средний вес телефона
2.5. Для каждого производителя получить максимум режима ожидания, максимум режима разговора
2.6. Для каждого производителя узнать количество моделей с цветным дисплеем
2.7. Узнать количество моделей каждого производителя с фотокамерой и диктофоном
2.8. Узнать количество моделей каждого производителя с фотокамерой или диктофоном
2.9. Узнать количество аппаратов каждого типа корпуса, имеющихся на складе
3. Перекрестные запросы
3.1. Создать запрос Фамилия_Перекрестный1, который бы позволил узнать количество моделей у каждого производителя каждого типа корпуса (тип корпуса должен иметь словесное обозначение, для чего можно воспользоваться функцией IIf() или Switch())
3.2. Создать запрос Фамилия_Перекрестный2, который бы позволил узнать количество заказанных моделей за каждый месяц для каждого поставщика
(для отображения названия месяца можно воспользоваться функциями MonthName() и Month())
4. Запросы на изменение:
4.1. Запросы по созданию таблицы
Создать таблицу Временная на основе таблицы Заказы. Отобрать в таблицу Временная те записи, у которых цена не превышает 300 000 рублей.
4.2. Запрос на обновление данных
Обновить записи в таблице Заказы с учетом того, что СтоимостьДоставки из Украины возросла в 1,2 раза (в запросе используются данные из таблиц Заказы и Поставщики)
4.3. Запрос на добавление данных
Добавить в таблицу Временная записи из таблицы Заказы с ценой более 300 000 рублей
4.4. Запрос на удаление данных
Удалить из таблицы Временная Заказы 2004 года
10.1. Создание запросов.
Существует четыре типа запросов на изменение: на удаление, на обновление и добавление записей, а также на создание таблицы.
Запрос на удаление удаляет группу записей из одной или нескольких таблиц. Например, запрос на удаление позволяет удалить записи о товарах, поставки которых прекращены или на которые нет заказов. С помощью запроса на удаление можно удалять только всю запись, а не отдельные поля внутри нее.
Запрос на обновление записей вносит общие изменения в группу записей одной или нескольких таблиц. Например, на 10 процентов поднимаются цены на все молочные продукты или на 5 процентов увеличивается зарплата сотрудников определенной категории. Запрос на обновление записей позволяет изменять данные в существующих таблицах.
Запрос на добавление добавляет группу записей из одной или нескольких таблиц в конец одной или нескольких таблиц. Запрос на добавление также полезен при выполнении следующих действий:
— добавление полей на основе условий отбора;
— добавление записей, если некоторые поля из одной таблицы не существуют в другой. Запрос на добавление добавит данные в совпадающие поля и пропустит остальные.
Запрос на создание таблицы создает новую таблицу на основе всех или части данных из одной или нескольких таблиц. Запрос на создание таблицы полезен для выполнения следующих действий:
— создание таблицы для экспорта в другую базу данных Microsoft Access;
— создание отчетов, содержащих данные нескольких таблиц;
— создание резервной копии таблицы.
— создание архивной таблицы, содержащей старые записи;
— повышение быстродействия форм и отчетов, базирующихся на многотабличных запросах или выражениях SQL.
Выбор данных из одной таблицы
В режиме таблицы можно проводить различные операции с данными этой таблицы: просмотр, сортировка, фильтрация и др. Одним из преимуществ запросов является то, что они позволяют достаточно быстро отобрать необходимые данные из нескольких связанных таблиц. При этом все приемы, используемые при работе с одной таблицей, годятся и для сложных многотабличных запросов.
После выполнения запроса на выборку Microsoft Access создает набор записей, содержащий отобранные данные, с которыми можно работать также как и с таблицей.
Проще всего создать запрос на основе одной таблицы так: открыть окно базы данных, выбрать в окне базы данных вкладку Запросы, нажать кнопку Создать, в новом окне выбрать режим Конструктор и Ok. В следующем окне "Добавление таблицы" выбрать нужную таблицу, а затем нажать кнопки Добавить и Закрыть.
Окно конструктора запросов (рис.10.1) разделено на две части. В верхней части находятся списки полей таблиц или запросов, на основе которых создается новый запрос. В нижней — располагается бланк QBE (запрос по образцу), в котором выполняется работа по созданию запроса. Каждый столбец бланка представляет одно поле, используемое в запросе.
Первая строка бланка запроса служит для выбора полей, которым можно присвоить имена, используемые при выводе записей запросов. Во второй строке бланка запроса выводится имя таблицы, из которой выбрано поле. В третьей строке бланка можно указать, для каких столбцов нужно проводить сортировку. Флажки в строке бланка Вывод на экран отвечают за вывод полей в наборе записей. По умолчанию выводятся все поля, включенные в бланк запроса. Для ввода условия отбора записей используется строка Условие отбора.
Р
ис.10.1. Окно конструктора запросов
Включение полей в запрос. Чтобы включить поле в бланк запроса, нужно его выделить в таблице и мышью перетащить в соответствующее поле бланка запроса.
Установка свойств полей. В общем случае поля в запросе имеют те же свойства, что и в таблице, из которой они перенесены. Однако можно задать другие значения свойств. Для этого нужно щелкнуть по любой ячейке соответствующего столбца в бланке запроса и нажать кнопку Свойства на панели инструментов. После этого вводятся свойства полей.
Ввод условий отбора. Если нужно отобрать записи с конкретным значением поля, его нужно ввести в ячейку Условие отбора этого поля. Текстовое значение в качестве условия заключается в кавычки. При задании условия отбора можно пользоваться знаками отношений , >=, и логическими операциями or, and.
Кроме этого Access предоставляет специальные операторы для отбора данных, выводимых в запросе:
between — определяет диапазон значений. Between 10 and 20 означает то же самое, что и выражение >=10 and 10. В этом случае выбираются все записи поля, вычисляемое поле которых >10;
month(дата) — возвращает значение месяца года в диапазоне от 1 до 12;
year(дата) — возвращает значение года в диапазоне от 100 до 9999;
weekday(дата) — возвращает целое число от 1(Воскресенье) до 7(Суббота), соответствующее дню недели;
date() — возвращает текущую системную дату.
Вычисляемые поля. Можно выполнить вычисления с любыми полями таблицы и сделать вычисляемое выражение новым полем в наборе записей. При этом можно использовать любые функции, встроенные в Access, и выполнять над полями таблицы арифметические операции с помощью операторов: +, -, *, /, , ^, mod, &. Например, пусть имеется имя поля с именем “Количество”, где записано количество единиц товара и поле “Цена”, где записана стоимость единицы товара. Тогда для подсчета стоимости товара в пустое поле бланка запроса нужно ввести выражение Количество*Цена и значения этих полей будет перемножено.
Задание имен вычисляемых полей. При создании любого выражения в бланке запроса Access помещает стандартное имя поля “Выражение1:”. Можно изменить или назначить имена полей, что является важным, если их нужно использовать в отчете или других запросах. Это делается с помощью окна свойств. Для этого нужно щелкнуть по любой ячейке соответствующего столбца, нажать кнопку Свойства на панели инструментов и выбрать Подпись.
Параметрические запросы. Условия запроса могут быть включены непосредственно в бланк запроса, но для того чтобы сделать его более универсальным, можно вместо конкретного значения отбора включить в запрос параметр, т.е. создать параметрический запрос.
Для этого в строку "Условие отбора" вводится фраза в квадратных скобках, которая будет выводиться в качестве "подсказки" в процессе диалога, например [Введите фамилию]. Таких параметров может быть несколько, каждый для своего поля, при этом имя каждого параметра должно быть уникальным.
Сортировка данных. Обычно Access выводит записи в том порядке, в каком они выбираются из базы данных. Можно изменить последовательность вывода данных, задав порядок сортировки По возрастанию или По убыванию.
Итоговые запросы. Иногда нас интересуют не отдельные записи таблицы, а итоговые значения по группам данных. Например, нужно узнать средний объем продаж по каждому месяцу отдельно. Это можно сделать с помощью итогового запроса. Для этого нужно нажать на кнопку Групповые операции на панели инструментов и в бланке появится новая строка с этим именем. При этом ведется группировка по всем занесенным в бланк полям, но итог не подводится. Для получения итогов нужно заменить Группировка в строке Групповая операция на конкретные итоговые функции.
Access предоставляет несколько функций для обеспечения групповых операций. Основные из них:
sum — вычисляет сумму всех значений заданного поля в каждой группе. Используется только для числовых и денежных полей;
avg — Вычисляет среднее арифметическое значение всех значений данного поля в каждой группе;
min, max — вычисляет наименьшее (наибольшее) значение поля внутри группы;
count — вычисляет число записей, в которых значения данного поля отличны от Null.
Выбор записей, формирующих группы. В группы итогового запроса можно не включать некоторые записи. Для этого нужно добавить в бланк запроса одно или несколько полей для фильтра. Для создания фильтра в строке Групповая операция выбирают установку Условие, снимают флажок Вывод на экран для этого поля и вводят условие отбора.
