Вычисления в запросах на выборку данных
Запросы на выборку данных
Цель
Научиться создавать и использовать запросы к данным, хранящимся в связанных таблицах базы данных.
Графический инструментарий для разработки запросов. Ввод условий отбора записей.
Запросы являются одним из основных инструментов выборки и обработки данных в таблицах базы данных. Запросы используются для анализа, просмотра и изменения данных. Запросы могут служить источниками записей для форм, отчетов и страниц доступа к данным.
Результат выполнения запроса – это новая, обычно временная, таблица, которая существует до закрытия запроса. Структура такой таблицы определяется полями, выбранными из одной или нескольких таблиц. Записи формируются путем объединения записей таблиц, участвующих в запросе.
Существует несколько видов запросов (рис.1). Основным является запрос на выборку. С его помощью выбираются данные из взаимосвязанных таблиц и других запросов. На основе запроса этого типа могут быть построены запросы других типов.
Рис.1. Классификация запросов в MS Access
Запрос строится на основе одной или нескольких взаимосвязанных таблиц. При этом могут быть использованы таблицы самой базы данных и таблицы, полученные в результате выполнения запросов и сохраненные для дальнейшего применения.
Кроме того, запросы могут формироваться на основе других запросов с использование временных таблиц, получаемых сразу при выполнении этих запросов.
Задание 1. Графический инструментарий для разработки запросов
Запросы достаточно сложного уровня можно создавать в режиме КОНСТРУКТОРА. СУБД Microsoft Access предоставляет удобное для пользователя диалоговое графическое средство формирования запросов по образцу – QBE (Query by Example). Инструментарий QBE располагает встроенными интеллектуальными компонентами, с помощью которых легко может быть построен достаточно сложный запрос.
Окно КОНСТРУКТОРА запросов QBE имеет следующую организацию (рис.2).
Рис.2. Окно КОНСТРУКТОРА запросов QBE
С отдельными компонентами КОНСТРУКТОРА запросов мы познакомимся, создавая запросы к базе данных.
Создадим запрос на выборку заказов, которые были сделаны после 01/01/2008 года.
1. Выберите в главном меню базы данных СОЗДАНИЕ – КОНСТРУКТОР ЗАПРОСОВ.
2. Для выполнения запроса нам достаточно данных из одной таблицы – ЗАКАЗ, поэтому в открывшемся списке таблиц щелкните дважды только по таблице ЗАКАЗ. Закройте окно ДОБАВЛЕНИЕ ТАБЛИЦ.
Если вам потребуется добавить таблицу уже после того, как вы закрыли список таблиц, нажмите на панели инструментов на кнопку 
ОТОБРАЗИТЬ ТАБЛИЦУ.
3. Переносить поля в бланк запроса можно двумя способами: перетаскивать поля непосредственно из таблицы или выбирать поля из раскрывающегося списка в заголовке пустого поля (рис.3).
Если перетащить символ * из таблицы в поле запроса, то все поля таблицы будут активными полями для запроса.
Рис.3. Выбор поля для запроса
4. Перетащите поля НОМЕР_ЗАКАЗА и ДАТА_ЗАКАЗА в пустые поля бланка запроса.
5. Введите условие отбора записей как показано на рис.4.
Рис.4. Ввод критериев отбора записей базы данных
6. Для просмотра временной таблицы с найденными записями, соответствующими критерию отбора, нажмите кнопку 
ВЫПОЛНИТЬ на панели инструментов или перейдите в режим ВИД (кнопка 
на панели инструментов).
Результаты поиска представлены во временной таблице (рис.5).
Рис.5. Временная таблица с результатами поиска
7. Сохраните запрос командой СОХРАНИТЬ из контекстного меню бланка запроса с именем ДАТЫ_ЗАКАЗОВ[3].
8. Вернитесь в режим КОНСТРУКТОРА запросов и внесите следующие изменения: снимите флажок вывода на экран для поля ДАТА_ЗАКАЗА и установите сортировку по убыванию для поля НОМЕР_ЗАКАЗА (рис.6).
9. Запустите запрос на исполнение и сравните результаты вывода с предыдущим случаем (рис. 5).
Рис.6. Режимы сортировки и вывода на экран
Обратите внимание, что автоматически изменился синтаксис записи даты в поле условия – появились знаки #.
Создайте запрос для поиска товаров, поставленных на склад в текущем году. Отсортируйте список товаров по возрастанию наименований.
Задание 2. Ввод условий отбора записей
Условия, записанные в одной строке слева направо, объединены логической операцией И. Условия, записанные в разных строках бланка запроса, объединены логической операцией ИЛИ.
1. Добавьте в бланк запроса Даты_Заказов еще одну таблицу – ПОКУПАТЕЛЬ.
2. Для значений своих данных сформируйте условие отбора на использование логики ИЛИ, например, как показано на рис.7.
Рис.7. Условия отбора, объединенные логической операцией ИЛИ
3. Сформируйте условия отбора, объединенные логической операцией И.
4. Сформируйте условия отбора, объединенные логическими операциями И и ИЛИ.
Для полей, имеющих логический тип данных, условиями отбора являются значения «да/нет» (рис.8).
5. Найдите все оплаченные заказы.
Рис.8. Отбор записей по логическому полю
Можно выбирать строки, принадлежащие указанному диапазону значений (рис.9). Эту же выборку можно создать, используя выражение Between #01.01.2007# And #20.01.2007#.
Рис.9. Выбор диапазона значений
6. Отберите записи, принадлежащие определенному вами диапазону. Диапазон может быть указан и для текстового поля, например, для поля ПОКУПАТЕЛЬ: >Г And <М. Такой критерий позволит отобрать все записи о покупателях, фамилия которых лежит в диапазоне букв Г и М.[4]
Чтобы выбрать записи с пустыми значениями в определенном поле[5], в бланке запроса введите в строке УСЛОВИЕ ОТБОРА значение null.
Перед запуском запроса Access изменит введенное значение на выражение Is Null.
7. Отберите записи о типах покупателей, для которых не сформулировано дополнительное описание (поле ПРИМЕЧАНИЕ).
8. Измените запрос на противоположный: найдите типы покупателей, для которых есть дополнительные сведения (рис.10).
MS Access позволяет для задания условий отбора использовать функции Visual Basic, например, как Date(). Более того, можно объединять в выражении функции, математические операторы, постоянные значения и ссылки на поля. На рис. 11 приведен пример использования функций для формирования условий отбора записей.
Вводить функции в поле условия отбора удобнее с помощью ПОСТРОИТЕЛЯ ВЫРАЖЕНИЙ (рис.11). Для вызова ПОСТРОИТЕЛЯ необходимо выполнить следующие действия:
Установите курсор в поле для ввода условия отбора.
Нажмите на панели инструментов кнопку 
ПОСТРОИТЕЛЬ.
Откройте списки: ФУНКЦИИ, ВСТРОЕННЫЕ ФУНКЦИИ, укажите требуемую КАТЕГОРИЮ, выберите нужную функцию в окна ПОСТРОИТЕЛЯ (рис.11).
Рис.10. Выборка записей с непустыми значениями
Рис.11. Использование ПОСТРОИТЕЛЯ ВЫРАЖЕНИЙ для ввода условий отбора
9. Выполните поиск заказов, сделанных в течение последних 3 месяцев.
10. Найдите покупателей, у которых указан номер городского телефона (условно, пусть это будет номер, имеющий определенное количество цифр).
Текстовая функция LEN(строка) возвращает число символов в указанной строке.
11. Найдите всех поставщиков, у которых указан телефон с кодом города Санкт-Петербурга (опять же, условно, пусть у телефона первые три цифры 812).
Текстовая функция LEFT(строка, число символов) возвращает указанное количество символов строки, взятых слева.
12. Найдите все заказы, сделанные в прошлом году. Функция YEAR(дата) возвращает год из указанной даты.
При формировании условий отбора можно использовать подстановочные знаки: * и ?. Например, вы можете использовать для поиска покупателей в качестве условия отбора выражение ??????, которое обеспечит выборку записей, в поле ПОКУПАТЕЛЬ которых присутствуют значения типа Иванов, Петров.
При использовании подстановочных символов следует помнить о том, что символ * соответствует любому одном (или нескольким) символам или цифре, а символ ? соответствует любому текстовому символу. Поэтому, обратите внимание на оператор LIKE, который необходимо использовать при поиске в числовом поле.
13. Создайте запросы с использование подстановочных символов.
Задание 3
1. Найдите все заказы, в которых не указано количество заказанных партий определенного товара. Проведите анализ возможности подобной ситуации в нашей базе данных.
2. Найдите всех поставщиков, товары которых были заказаны в прошлом месяце.
3. Просмотрите список товаров на полке №1.
4. Выведите список товаров, поставки которых выполняются партиями по 100 единиц.
5. Определите перечень товаров, стоимость которых более 1000 рублей.
6. Определите типы товаров, стоимость которых менее 1000 рублей.
7. Определите поставщиков товаров, стоимость которых находится в диапазоне от 1000 до 2000 рублей.
8. Найдите товары, заказ которых оплачен по безналичному расчету.
9. Определите список покупателей, оплачивающих заказа наличными деньгами.
10. Определите заказы прошлого месяца, оплаченные по безналичному расчету.
11. Найдите заказы прошлого года, оплаченные наличными деньгами.
12. Выполните анализ, какие типы покупателей покупают товар определенной категории.
13. Сформируйте список товаров, минимальный запас для которых меньше 100 единиц.
14. Найдите типы товаров, минимальный запас для которых больше 100 единиц.
15. Определите список поставщиков товаров, для которых минимальный запас от 100 до 200 единиц.
16. Найдите полки, количество мест которых более 3.
17. Выведите список товаров, цена реализации которых менее 500 рублей.
18. Определите категории, цена реализации которых от 2000 до 3000 рублей.
19. Найдите поставщиков, у которых мобильные телефоны определенного провайдера (условно, любые первые цифры кода, например, 921).
20. Перечислите товары, партии которых имеются в наличии на складе.
Вычисления в запросах на выборку данных
Цель
Научиться создавать и использовать запросы к данным, хранящимся в связанных таблицах базы данных.
Создание запросов с вычисляемыми полями. Групповые операции в запросах.
Задание 1. Создание запросов с вычисляемыми полями
При составлении запроса на выборку можно создавать в бланке запроса собственное поле для вычисления тех или иных значений. Например, можно создать поле СТОИМОСТЬ, в котором вычислять выражение КОЛИЧЕСТВО * ЦЕНА_РЕАЛИЗАЦИИ для всех строк таблицы ПУНКТ_ЗАКАЗА.
Все, что необходимо сделать для создания вычисляемого поля – ввести в свободный столбец бланка запроса имя нового поля и после двоеточия – выражение для расчета. Вычисляемое выражение удобно вводить с помощью ПОСТРОИТЕЛЯ.
Для таблицы ПУНКТ_ЗАКАЗА создадим запрос, в котором будем вычислять итоговую стоимость каждой позиции заказа.
1. Создайте запрос для таблицы ПУНКТ_ЗАКАЗА в режиме КОНСТРУКТОРА (рис.1).
2. Перетащите элемент * из таблицы ПУНКТ_ЗАКАЗА в пустое поле бланка запроса (рис.1).
3. Щелкните мышью в следующем пустом поле и нажмите кнопку 
ПОСТРОИТЕЛЬ на панели инструментов (рис.1).
Рис.1. Создание вычисляемого поля в запросе
При создании вычисляемого выражения используйте списки полей, это удобнее и надежнее, чем непосредственно вводить названия полей (рис.2).
Можно сразу в ПОСТРОИТЕЛЕ выражений изменить название ВЫРАЖЕНИЕ1 вычисляемого поля, например, на ИТОГО (рис.2).
Осторожно, не удалите разделительный символ двоеточие (:), это служебный символ, он отделяет название поля от его значения.
Рис.2. Окно ПОСТРОИТЕЛЯ выражений
4. Запустите запрос на исполнение и проверьте правильность вычислений в нем.
Задание 2. Групповые операции в запросах
При использовании запросов часто требуется видеть не каждую строку таблицы, а только итоговые значения по группам данных.
Групповые операции (группировка) позволяют выделить группы записей с одинаковыми значениями в указанных полях и использовать для некоторых полей этих групп одну из статистических функций.
В MS Access предусмотрено девять статистических функций:
SUM – сумма значений некоторого поля для группы;
AVG – среднее от всех значений поля в группе;
MAX, MIN – максимальное, минимальное значение поля в группе;
COUNT – число значений поля в группе без учета пустых значений;
StDev – среднеквадратичное отклонение от среднего значения поля в группе;
VAR – дисперсия значений поля в группе;
First, Last – значение поля из первой или последней записи в группе.
Результат запроса с использование групповых операций содержит по одной записи для каждой группы.
В запрос, как правило, включаются поля, по которым производится группировка, и поля, для которых выполняются групповые функции.
Рассмотрим технологию создания запроса с группировкой записей и применения статистической функции к сгруппированным записям.
Создадим запрос для вычисления количества (COUNT) различных товаров в каждой группе товаров (типе товаров).
Из формулировки запроса видно, что товары следует группировать по названию типа товара, а для каждой полученной группы следует применить статистическую функцию COUNT.
1. В режиме КОНСТРУКТОРА создайте запрос для таблиц ТОВАР и ТИП_ТОВАРА. Таблица ТИП_ТОВАРА нам потребуется для расшифровки значений поля КОД_ТИПА (рис.3).
2. Перенесите в бланк запроса поля ТИП_ТОВАРА из таблицы ТИП_ТОВАРА и НАИМЕНОВАНИЕ из таблицы ТОВАР (рис.3).
3. Нажмите кнопку на панели инструментов 
ИТОГИ.
4. Для поля НАИМЕНОВАНИЕ в строке ГРУППОВАЯ ОПЕРАЦИЯ щелкните мышью в правом углу поля (должен появиться элемент управления список) и выберите из раскрывающегося списка операцию COUNT (рис.3).
Рис.3. Групповые операции в запросах
Запустите запрос на исполнение и убедитесь в появлении групповых итогов (рис.4).
Рис.4. Итоги групповой операции
Можно изменить название поля Count_НаименованиеТовара на любое другое, например, КОЛИЧЕСТВО_НАИМЕНОВАНИЙ.
5. Вернитесь в режим КОНСТРУКТОРА, щелкните мышью в названии поля НАИМЕНОВАНИЕ_ТОВАРА и введите КОЛИЧЕСТВО_НАИМЕНОВАНИЙ до названия поля, поставьте знак двоеточие (:) для разделения позиций (мы обсуждали с вами этот вопрос).
6. Запустите запрос на исполнение и убедитесь в появлении нового названия поля.
Можно применять несколько статистических функций одновременно.
7. Добавьте в бланк запроса еще одно поле – МИНИМАЛЬНЫЙ_ЗАПАС.
8. Примените (выберите из списка) для этого поля функцию AVG(), вычисляющую среднее от всех значений поля в группе.
9. Измените название поля и просмотрите результаты выполнения запроса (рис.5).
Рис.5. Запрос с несколькими статистическими функциями
В запросах с групповыми операциями можно задавать условия отбора, также как, и в обычных запросах на выборку.
10. Измените для поля МИНИМАЛЬНЫЙ_ЗАПАС функцию AVG() на значение УСЛОВИЕ в том же раскрывающемся списке (рис.6).
11. Введите значение условия. Запустите запрос на исполнение.
Обратите внимание на отсутствие флажка ВЫВОД НА ЭКРАН, это особенность задания условий в групповых запросах.
Рис.6. Использование условий отбора в запросах с групповыми операциями
В запросах с группировкой можно создавать вычисляемые поля, так же, как и в обычных запросах на выборку.
Создадим запрос, вычисляющий стоимость всего заказа и внедрим его в форму ЗАКАЗ.
12. В режиме КОНСТРУКТОР создайте запрос для таблицы ПУНКТ_ЗАКАЗА.
13. Переместите поле НОМЕР_ЗАКАЗА в бланк запроса и включите для этого поля операцию ГРУППИРОВКА.
14. В пустом поле щелкните указателем мыши и вызовите ПОСТРОИТЕЛЬ.
15. Используйте встроенные функции для ввода вычисляемого выражения (рис.7).
Рис.7. Создание вычисляемого поля
16. Переименуйте Выражение1, например, в СТОИМОСТЬ_ЗАКАЗА.
17. Нажмите ОК в ПОСТРОИТЕЛЕ выражений.
18. Выберите в поле ГРУППОВАЯ ОПЕРАЦИЯ для нового поля строку ВЫРАЖЕНИЕ из раскрывающегося списка (рис.8).
Рис.8. Создание вычисляемого поля в запросе с группировкой
19. Запустите запрос на исполнение и убедитесь в выполнении группировки и вычислений (рис.9).
Рис.9. Результат выполнения запроса
20. Сохраните запрос, назовите его, например, Стоимость_заказа. Закройте созданный запрос.
21. Раскройте форму ЗАКАЗ в режиме КОНСТРУКТОРА, определите место расположения для внедренного запроса.
22. Возьмите указателем мыши запрос и перетащите его в приготовленное место в форме ЗАКАЗ.
23. В открывшемся диалоговом окне МАСТЕРА подчиненных форм (рис. 10) укажите режим САМОСТОЯТЕЛЬНОЕ ОПРЕДЕЛЕНИЕ для установления связи между формой и внедренным в нее запросом.
24. В раскрывающихся списках выберите поля для связи (рис.10).
25. Нажмите кнопку ДАЛЕЕ, дайте подчиненной форме (запрос становится подчиненной формой) осмысленное название и нажмите ГОТОВО.
Рис.10. Первый шаг МАСТЕРА подчиненных форм
Рис.11. Второй шаг МАСТЕРА подчиненных форм
26. Откройте форму ЗАКАЗ в режиме ВИД (рис.12).
27. С помощью СВОЙСТВ измените внешний вид внедренной формы (запроса). Можно, например, выключить изображения линии прокрутки, элемента выбора и так далее (рис. 12).
28. Поле НОМЕР_ЗАКАЗА можно свернуть.
29. Проверьте работоспособность измененной формы: введите несколько новых позиций в имеющиеся заказы и создайте несколько новых заказов.
Рис.12. Окончательный вид формы ЗАКАЗ с внедренным запросом
Задание 3
Создать запрос, определяющий занятость полок и внедрить его в виде кнопки в форму ТОВАР.
В запросе необходимо создать вычисляемое поле, в котором сравнивается объем полки и количество партий, размещенных на этой полке. Если на полке есть свободные места, то можно выдать сообщение о количестве свободных мест.
Категория логических функций, с которыми мы познакомились в Excel, в Access называется категорией УПРАВЛЕНИЯ. В этой категории имеется три функции (рис. 13): Switch(), IIF() и Choose(). Для выполнения запроса необходимо рассмотреть синтаксис функции IIF().
IIF(логическое условие; операторы если условие true; операторы если условие false)
На рис.13 приведено окно ПОСТРОИТЕЛЯ выражений, в котором вычисляется занятость полок с использование функции IIF().
Рис.13. Построение вычисляемого поля для запроса о занятости полок
На рис.14 показан результат выполнения запроса. В случае если полка занята, в поле Свободных мест на полке появится сообщение «0 свободных мест». В случае неполной загрузки полки (на рис.14) вычисляется количество свободных мест.
Рис.14. Выполнение запроса на определение занятости полок
Запрос готов к работе. Сохраните запрос, например, как СВОБОДНЫЕ МЕСТА НА ПОЛКАХ и внедрите в структуру формы ТОВАР.
1. Откройте форму ТОВАР в режиме КОНСТРУКТОРА.
2. Нажмите на панели инструментов элемент КНОПКА и щелкните в области формы.
Автоматически откроется диалоговое окно МАСТЕРА создания кнопок.
3. На первом шаге выберите категорию РАЗНОЕ и действие – ВЫПОЛНИТЬ ЗАПРОС.
4. На втором шаге МАСТЕРА укажите название запроса – СВОБОДНЫЕ МЕСТА НА ПОЛКАХ.
5. Следующий шаг МАСТЕРА требует указания или названия кнопки, или картинки на кнопке.
6. Проверьте работоспособность формы с кнопкой. Имейте в виду, что полки, которых нет в списке занятых полок (в списке, который выдает запрос) – свободны. Перечень всех полок вы видите в раскрывающемся списке поля НОМЕР_ПОЛКИ.
Задание 4
Создайте запросы, отвечающие на следующие вопросы к базе данных.
1. Сколько заказов было сделано в прошлом году?
2. Сколько заказов было оплачено по безналичному расчету в прошлом месяце?
3. На какую сумму в прошлом году были оплачены заказы наличными деньгами?
4. Сколько товаров каждого типа имеется в наличие на складе?
5. Выведите список покупателей, которые заказывали более 3-х партий одного наименования в одном заказе.
6. На какую сумму не оплачены заказы на сегодняшний день?
7. На какую сумму каждый поставщик поставил товаров в прошлом году?
8. Какую прибыль склад получил в прошлом месяце?
В случае отсутствия необходимых данных, добавьте эти данные с помощью форм в таблицы базы данных.
Все запросы должны быть не только сконструированы, но и выдавать конкретные результаты при выполнении.
Запросы-действия
Цель
Научиться создавать и использовать запросы к данным, хранящимся в связанных таблицах базы данных.
Создание новой таблицы с помощью запроса. Обновление группы записей. Обновление поля таблицы вычисляемым значением. Добавление данных из другой таблицы.
Задание 1. Создание новой таблицы с помощью запроса
Перейдем к рассмотрению запросов-действий, которые позволяют изменять, создавать, удалять или добавлять наборы данных в таблицы базы данных.
Запрос на создание таблицы используется для сохранения результатов выполненного запроса в виде таблицы базы данных.
Этот тип запроса основан на запросе на выборку, но в отличие от него сохраняет таблицу, содержащую результаты поиска.
Необходимость в сохранении результатов выполнения запроса возникает, например, в том случае, когда один и тот же запрос выполняется неоднократно, а исходные данные для него не меняются. Используя вместо запроса таблицу, можно значительно ускорить доступ к данным, особенно если запрос построен на нескольких таблицах большого объема.
Сохранение таблицы запроса полезно и в том случае, когда необходимо накапливать итоговые данные на протяжении длительного времени, а данные, на которых базируется запрос можно удалить.
Создадим запрос на создание таблицы, хранящей сведения о проданных партиях товаров; сохраним полученную выборку в виде архивной таблицы. В последующем, удалим эти записи из основной таблицы[6].
1. Сконструируйте запрос на выборку для поиска проданных партий товаров (рис.1).
2. Убедитесь в его работоспособности и сохраните.
3. В режиме КОНСТРУКТОРА преобразуйте этот запрос из запроса на выборку в запрос на создание таблицы. Нажмите на панели конструктора кнопку СОЗДАНИЕ ТАБЛИЦЫ.
4. В диалоговом окне укажите имя будущей таблицы (рис.2).
5. Чтобы просмотреть, какие записи будут помещены в новую таблицу, запустите запрос на выполнение еще раз.
6. Теперь осталось убедиться, что именно эти записи помещены в новую таблицу. Закройте запрос. В окне базы данных в разделе ТАБЛИЦЫ найдите и просмотрите новую таблицу (рис.3).
Рис.1. Запрос для поиска проданных партий товаров
Рис.2. Именование таблицы для сохранения найденных данных
Рис.3. Таблица, созданная в результате выполнения запроса
7. Убедитесь, что записи, помещенные в новую таблицу, не удалены из исходных таблиц.
Задание 2. Обновление группы записей
Запрос на обновление используется для обновления данных в полях таблицы базы данных.
Изменения вносятся в поля целой группы записей, отбираемых с помощью указанных пользователем условий отбора, а не отдельной записи. В результате одним запросом можно выполнить необходимые изменения во многих записях.
Создадим запрос, который будет изменять значение поля НАЛИЧИЕ для всех записей в таблице ПАРТИЯ_ТОВАРА, в случае заказа этих партий товаров.
1. В режиме КОНСТРУКТОРА создадим обычный запрос на выборку для поиска всех заказанных партий товаров, но для которых еще не отмечен факт их продажи (рис.4).
Рис.4. Запрос на поиск всех проданных партий товаров, для которых еще не отмечен факт их продажи
2. Преобразуйте этот запрос на ОБНОВЛЕНИЕ с помощью соответствующей кнопки на панели инструментов.
3. Добавьте в условии ОБНОВЛЕНИЕ для поля НАЛИЧИЕ данные для изменения (рис.5).
4. Запустите запрос на исполнение и убедитесь в выполненных им обновлениях.
5. Обратите внимание на значок, который появляется у запроса в окне базы данных.
Рис.5. Изменение типа запроса на ОБНОВЛЕНИЕ
Задание 3. Обновление поля таблицы вычисляемым значением
В процессе проектирования базы данных мы с вами уже решали проблему вычисления ЦЕНЫ_РЕАЛИЗАЦИИ (или ЦЕНЫ_ПРОДАЖИ), связывая эту величину с ЦЕНОЙ_ПОСТАВКИ. Для этого мы в форме ПАРТИЯ_ТОВАРА использовали вычисляемое поле. Попробуем решить эту же проблему другим способом.
1. В режиме КОНСТРУКТОРА создайте все тот же запрос для поиска партий товаров, вписанных в бланк заказа (рис.6).
2. Измените тип запроса на ОБНОВЛЕНИЕ.
Рис.6. Запрос на обновление вычисляемым значением
3. Для поля ЦЕНА_РЕАЛИЗАЦИИ в строке ОБНОВЛЕНИЕ введите вычисляемое выражение (рис.6).
4. Запустите запрос на исполнение и убедитесь, что происходит изменение поля ЦЕНА_РЕАЛИЗАЦИИ в таблице ПУНКТ_ЗАКАЗА.
5. Сохраните созданный запрос с именем ЦЕНА_РЕАЛИЗАЦИИ.
Позже мы внесем все созданные запросы в соответствующие формы в виде командных кнопок.
Задание 4. Добавление данных из другой таблицы
С помощью запроса на добавление производится добавление записей из таблицы с результатами запроса в таблицу базы данных. Поэтому в запросе должны быть сформированы записи с полями, соответствующими полям в дополняемой записями таблице базы данных.
Структура записи таблицы запроса может не полностью совпадать со структурой записи таблицы, в которую добавляются записи. В записи запроса может быть меньше полей, если поля в таблице, куда добавляются записи, не наложено требование по обязательности их заполнения. Допускается несоответствие типов полей, если возможно преобразование типа данных одного поля в тип данных другого поля. Кроме того, в бланк запроса могут быть включены поля, по которым задаются условия отбора.
Мы с вами создали таблицу ПРОДАННЫЕ_ПАРТИИ_ТОВАРОВ для хранения архивных данных о проданных партиях. В эту таблицу необходимо добавлять записи по мере продажи других партий товара.
1. Создадим еще раз запрос на выборку для поиска проданных партий товаров (рис.7).
2. В окне КОНСТРУКТОРА преобразуйте его в запрос на ДОБАВЛЕНИЕ. В диалоговом окне МАСТЕРА укажите имя таблицы, в которую должны добавляться записи (ПРОДАННЫЕ_ПАРТИИ_ТОВАРОВ) (рис.8).
3. Для формирования добавляемых записей обязательно включите в бланк запроса поля, составляющие ключ таблицы, в которую будет производиться добавление.
4. Поскольку в нашем случае имена полей в таблице и запросе совпадают, можно запускать запрос на исполнение. Сохраните запрос с именем АРХИВ_ПРОДАННОГО.
5. Обратите внимание на повторное занесение записей в таблицу ПРОДАННЫЕ_ПАРТИИ_ТОВАРОВ (рис.9). Конечно, так не должно быть, но мы еще не умеем удалять записи из таблицы ПАРТИЯ_ТОВАРА.
6. Обратите внимание также на значок созданного запроса АРХИВ_ПРОДАННОГО в окне базы данных.
Рис.7. Запрос на ДОБАВЛЕНИЕ
Рис.8. Выбор таблицы для добавления записей
Рис.9. Таблица для архивации записей о проданных товарах
Запросы обязательно сохраняйте, они потребуются нам для автоматизации форм.
Задание 5. Удаление группы записей
Запрос на удаление позволяет удалить записи из одной таблицы или из нескольких взаимосвязанных таблиц, для которых установлен параметр обеспечения целостности данных каскадное удаление связанных записей. Нам необходимо удалить записи о проданных париях товаров из таблицы ПАРТИЯ_ТОВАРА.
1. Сначала, как обычно, сконструируйте запрос на выборку (рис.10).
2. В режиме КОНСТРУКТОРА измените тип запроса в запрос на УДАЛЕНИЕ (рис.10).
Обратите внимание, что перетаскивание полей в бланк запроса удобно реализовать с помощью элемента *. Поле НАЛИЧИЕ следует перенести дополнительно для указания условия отбора.
Рис.10. Построение запроса на удаление записей
3. Запустите запрос на исполнение. Сохраните запрос.
Если в запросе на удаление объединяются две или более таблиц, находящихся в отношении 1:М, то удалить можно только записи одной из таблиц на стороне «многие».
Задание 6. Автоматизация форм
Теперь, когда мы с вами имеем несколько запросов-действий, можно с их помощью автоматизировать формы.
1. Можно удалить вычисляемое поле ЦЕНА_РЕАЛИЗАЦИИ в подчиненной форме ПАРТИЯ и использовать запрос на обновление ЦЕНА_РЕАЛИЗАЦИИ.
2. Запрос на обновление ЦЕНА_РЕАЛИЗАЦИИ может быть добавлен в виде командной кнопки в подчиненную форму ПУНКТ_ЗАКАЗА. В этом случае у формы следует изменить режим изображения на ОБЫЧНЫЙ, так как в режиме ТАБЛИЦА командные кнопки не отображаются (рис. 11).
Рис.11. Примерный вид формы ЗАКАЗ с командными кнопками для запросов-действий
3. В форму ЗАКАЗ можно добавить еще одну командную кнопку для выполнения запроса на выборку НАЛИЧИЕ_ТОВАРА_НА_СКЛАДЕ (рис.12).
4. При наличии такой кнопки пользователь может вызывать запрос на исполнение, выбирать нужную партию товара и снимать флажок в поле НАЛИЧИЕ в случае продажи партии.
Рис.12. Запрос о наличии партий товара на складе
5. В форму ЗАКАЗ также можно добавить командную кнопку для выполнения запроса АРХИВ_ПРОДАННОГО. Напомним, этот запрос должен добавить в заранее созданную таблицу ПРОДАННЫЕ_ПАРТИИ_ТОВАРОВ записи о проданных партиях товаров.
Создание таблицы ПРОДАННЫЕ_ПАРТИИ_ТОВАРОВ следует выполнить один раз, до того, как пользователь будет формировать заказы. Мы предусмотрим это позже, при создании главной формы приложения.
6. И, наконец, в форму ЗАКАЗ можно добавить командную кнопку для выполнения запроса на удаление записей о партиях проданных товаров из таблицы ПАРТИЯ_ТОВАРА (рис.11).
Создание командной кнопки для выполнения запроса на удаление выполняется по другой технологии, не так, как вы привыкли создавать кнопки для всех других запросов-действий.
Рассмотрим эту технологию.
1. В окне базы данных щелкните мышью по меню СОЗДАНИЕ - МАКРОС.
2. В открывшемся окне МАКРОСА из выпадающего списка выберите команду ОТКРЫТЬ ЗАПРОС (рис. 13).
3. В списке свойств в нижней части окна для строки ИМЯ ЗАПРОСА в раскрывающемся списке выберите имя запроса на удаление, в нашем случае – «Найти и удалить».
4. Сохраните макрокоманду, дайте ей название – УДАЛИТЬ. Закройте окно КОНСТРУКТОРА макрокоманд.
Рис.13. Конструктор макрокоманд
5. Раскройте форму ЗАКАЗ в режиме КОНСТРУКТОРА.
6. Щелкните по элементу КНОПКА на панели инструментов и покажите место расположения кнопки в области формы.
7. В окне МАСТЕРА создания кнопок укажите действие Выполнить макрос – «Удалить».
8. Перейдите в РЕЖИМ ФОРМЫ и протестируйте работу с формой.
9. Необходимо написать инструкцию пользователю о последовательности нажатия командных кнопок, чтобы не произошло удаления записей из таблицы ПАРТИЯ_ТОВАРА, до их копирования в архивную таблицу.
10. В области ПРИМЕЧАНИЕ формы напишите для пользователя краткое руководство к действиям.