РАБОТА 3. ФОРМИРОВАНИЕ ЗАПРОСОВ

Цель работы: научиться: создавать запросы выборки, отбирать записи по заданным условиям, создавать вычисляемые поля в запросах, формировать специальные виды запросов, такие как: запросы с параметрами, итоговые (групповые) запросы, запросы на изменение данных.

Одним из основных инструментов обработки данных в СУБД являются Запросы. Запрос позволяет выбрать необходимые данные из одной или нескольких взаимосвязанных таблиц, произвести вычисления и получить результат в виде таблицы. Через запрос можно производить обновление данных в таблицах, добавления и удаления записей.

Окно запроса может находиться в одном из четырех режимов: конструктора; таблицы; предварительного просмотра и SQL, который служит для создания нестандартных запросов.

Технология конструирования запроса.

При создании запросов в режиме конструктора используется механизм запросов по образцу (QBE).

ACCESS позволяет объединить в одном запросе данные из нескольких таблиц. Если в запрос включено несколько таблиц, необходимо указать, как именно следует объединить данные их этих таблиц. В большинстве случаев объединение устанавливается автоматически. Предусмотрено самостоятельная установка объединения, которое будет применяться только к данному запросу.

Например, используя запрос-выборку, можно определить,каким организациям была поставлена продукция. Для этого выполнить последовательно команды:

ü Установите объект Запросы.Навкладке Созданиев группе элементовДругие, выберите опцию Конструктор запросов

ü Из окна добавление таблицы включите в бланк QBE таблицы Накладная и Справочник организаций, (рис.14)

ü Перенесите в бланк QBE поля КодОрганизации и Название.

ü Просмотрите результат выполнения запроса, для этого нажмите кнопку Выполнить.

ü Сохраните запрос с именем Грузополучатели.

Рисунок 14 - внешний вид бланка QBE при формирование запроса.

Задание 9.

ü Сформируйте динамический набор записей ДатаОтгрузки на основе таблиц Накладная, Товарный раздел и Справочник продукции, включив поля Наименование и ДатаТНН.

ü Сформируйте запрос, выводящий информацию об отгруженном товаре и его количестве, добавив поле Количество в запрос ДатаОтгрузки.

Определение условий отбора.

Умение определять условия отбора нужных данных является ключевым в процессе разработки запросов. Вы можете отобрать записи, введя в строку Условие отбора бланка QBE любое из следующих условий:

Точное совпадение. Задайте буквенное выражение, которое должно точно совпадать со значением полей отбираемых записей.

Поиск с использованием подстановочных символов шаблона. Для задания условия используйте комбинацию букв со знакомыми вам подстановочными символами (*,?).

Поиск несовпадений. Используйте операторNOT для исключения из области действия запроса тех записей, которые удовлетворяют заданному условию отбора.

Поиск по дате. Для задания условия используйте фиксированную дату или оператор DATE(), заменяющий текущую дату (на основании системных часов компьютера). Независимо от способа форматирования даты в поле в качестве условия отбора можно указать дату в самом простейшем формате 1/1/03.

Поиск пустых полей. Используйте оператор NULL, чтобы указать, что Вам нужны только записи, заданные поля которых пусты. Для задания, наоборот, не пустых полей - NOT NULL.

Операторы сравнения. Используйте любые операторы сравнения (<, >, <=, >=, <>, =) для сопоставления значений полей записей по особым правилам.

Значения Да/Нет. Используйте Да, Истина, Включено или -1, чтобы задать значение Да, а для задания значения Нет - Нет, Ложь, Отключено или 0.

Записи, лежащие в определенном диапазоне. Для определения диапазона значений следует использовать операторBetween...AndНапример: Between#01.03.79#And#01.12.79#

Сложное условие отбора. Воспользуйтесь логическими операторами для задания сложного условия для одного и того же поля операторами (таблица2)

Таблица 2 - Основные логические операторы

Задание 10.

ü Создайте копию запроса ДатаОтгрузкиприсвоив имяДата22. Сформируйте запрос, выводящий список отгруженной продукции за 22 октября.

ü Выведите список организаций, которым был реализован картофель (запрос формируется на основании всех таблиц). Сохраните запрос с именем Картофель.

ü Сформируйте запрос по продукции, цена которой больше 30 руб. за 1 кг (запрос формируется на основании таблицы Справочник продукции). Сохраните запрос с именем Продукция30.

ü Сформируйте запрос, выводящий список продукции реализованной между 20.10 и 22.10. Сохраните запрос с именем Между20-22.

ü Создайте запрос, показывающий, каким организациям была реализована продукция в количестве >100. Список продукции отсортируйте по возрастанию. Сохраните запрос с именем Реализация от 100.

Запросы с параметрами

Часто встречается ситуация, когда перед каждым выполнением запроса приходится изменять условия отбора. В таком случае удобнее создавать запросы с параметрами.

Запросы с параметрами особенно удобны, когда они являются базовыми для форм и отчетов.

Для создания запроса с параметрами необходимо в ячейкуУсловие отбора бланка QBE, соответствующую каждому полю, которое планируется использовать в качестве параметра, ввести приглашение,заключенное в квадратные скобки.

Задание 11.

ü Выведите информацию поконкретному виду продукции, с указанием количества и даты отгрузки. Запрос выполняется по данным всех таблиц. В строку Условие отбора вводится следующее приглашение [Введите наименование продукции]. Сохраните запрос с именем ПоПродукции.

ü Выведите информацию о реализованном товаре заопределенную дату. Запрос выполняется по аналогии с запросом Дата22, только в поле Условие отбора вводится требуемый параметр. Сохраните запрос с именем ПоДате.

Создание вычисляемого поля

1. Бланк запроса должен содержать все поля, над которыми планируете выполнить вычисления;

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

3. Нажмите клавишу ENTER, после чего вычисляемому полю будет присвоено стандартное имя «Выражение1:». Если необходимо изменить имя вычисляемого поля «Выражение1:», поверх него запишите требуемое имя.

Задание 12.

ü Создайте динамический набор записей Стоимость продукции, представив в нем список реализованной продукции с расчетом ее стоимости. Бланк приведен на рис. 15.

Рисунок 15 -внешний вид бланка QBE при конструировании запроса Стоимость продукции

ü Дополните запрос Стоимость продукции,включив в него расчет стоимости с учетом НДС.

ü Сформируйте динамический набор записей на основе запроса Стоимость продукции, включив в него список продукции, стоимость которой без учета НДС превысила 1 000 руб.

ü Выведите информацию о реализованной продукции в зависимости от введенного грузополучателя, включив расчет стоимости без учета и с учетом НДС. Внешний вид бланка QBE при конструировании запроса приведен на рис.16. Сохраните запрос с именем По организации.

Рисунок 16 - внешний вид бланка QBE при конструировании запроса По организации

ü Создайте запрос Для книги продаж выводящий динамический набор записей представленный на рис. 17.

Рисунок 17 -внешний вид запросаДля книги продаж

Групповые запросы

Для выполнения вычислений над группами записей следует использовать итоговые запросы, они еще называются групповыми запросами.

В ACCESS предусматривается девять итоговых функций, перечень основных приведен в таблице 3.

Таблица 3 - Основные итоговые функции

Создание группового запроса

1. Создайте запрос-выборку, перенесите поля по которым следует выполнить вычисления.

2. На вкладке Конструктор в группе элементов Показать или скрыть выберите Итоги.В бланке появится строка “Групповые операции”.Все ячейки по умолчанию будут содержать надпись группировка.

3. Необходимо указать, какие поля следует использовать для группирования, а какие для групповых вычислений. Каждое поле, включенное в итоговый запрос, должно быть либо группирующим, либо итоговым

Записи могут быть сгруппированы по нескольким полям. ACCESS группирует поля в порядке включения слева направо.

Задание 13.

ü Выведите информацию о количестве сделок с каждым грузополучателем, назвав запрос Количество сделок. Вид группового запроса представлен на рис. 18.

Рисунок 18 - внешний вид бланка QBE при конструировании запроса Количество сделок

ü Создайте запрос, позволяющий получить сведения об объёмах реализации каждого вида продукции. Сохраните запрос с именем Группировка по продукции.

ü На основе запроса Группировка по продукции создайте запрос, позволяющий получить сведения о реализации каждого вида продукции на определенную дату. Сохраните запрос с именем Объём реализации на дату.

ü Отредактируйте запрос По организации, удалив параметр [Введите название организации]. Сохраните запрос под именем Базовый. На основе запроса Базовый создайте групповой запрос, выводящий на экран данные о стоимости реализованной продукции (с учетом и без учета НДС) по каждому грузополучателю. Сохраните запрос с именем Сумма по организации.