При выполнении запроса можно вычислять значения по одному или нескольким полям исходной таблицы. Например, таблица Заказы содержит данные о количестве товаров (Продано), проданных в каждой партии, а таблица Товары данные о стоимости единицы изделия (Цена). Можно вычислить стоимость каждой партии проданных товаров. Результаты вычислений, выводящиеся в поле, не запоминаются в исходной таблице, они выполняются каждый раз при запуске запроса. Редактировать результаты вычислений нельзя.
Для расчетов с использованием выражений требуется в бланке запроса создать новое вычисляемое поле. Оно создается в пустой ячейке поля с помощью Построителя выражений (кнопка ВД).
Рассмотрим создание запроса для таблицы Заказы и определения стоимости каждой партии товаров для клиентов с кодом 1,2 или 3. Прежде всего, создадим запрос для таблиц Заказы и Товары в режиме Конструктора, и добавим в бланк запроса поля Код товара. Код клиента. Заказано и Продано.
Для определения стоимости партии товаров перейдите на строку Поле пустого столбца бланка и выберите на панели инструментов Построитель выражений.
Построитель помогает создать выражение путем последовательного добавления в него с помощью кнопки Вставить полей таблиц и запросов, функций, констант и операторов, расположенных в нижней части окна построителя списков.
Выражение будет иметь вид (рисунок 27):
Рисунок 27 - Построитель выражений
Рисунок 28 - Запрос с вычислением
После завершения формирования выражения нажать кнопку ОК, и выражение будет перенесено в строку Поле бланка запроса. Имя вычисляемого поля будет задано автоматически: Выражение!, которое отделяется от выражения двоеточием. Для изменения имени следует просто отредактировать текст в строке Поле (рисунок 28). После заполнения бланка запроса запускаем запрос. Результат показан на рисунке 29:
Рисунок 29 - Результат выполнения запроса
Многотабличные запросы
При выборе информации из таблиц наиболее часто используются многотабличные запросы, поскольку информация в реляционных БД содержится не одной отдельной таблице, а в совокупности связанных таблиц.
Для фо рмирования многотабличного запроса следует перенести в окне Конструктора все таблицы, участвующие в выборке. Далее все делается, как в однотабличном запросе: перенести в бланк запроса поля этих таблиц, задать условие выборки. Можно использовать логические условия И / ИЛИ.
Итоговые запросы
| Значение | Выполняемая операция |
| Sum | Сложение |
| Avg | Среднее значение |
| Min | Минимальное значение |
| Мах | Максимальное значение |
| Count | Количество записей, содержащих значения |
| First | Значение в первой записи |
| Last | Значение в последней записи |
Запросы, выполняющие вычисления в группах записей, называются итоговыми запросами. Для создания итогового запроса, находясь в Конструкторе запросов, следует выбрать из меню Вид/Групповые операции. В бланке запроса появится новая строка с наименованием Групповая операция. В ней следует указать тип выполняемого вычисления.
Например, требуется подсчитать общее количество заказанных товаров:
1. В окно Конструктора добавить таблицу Заказы.
2. Поместить в бланк запроса поле Заказано.
3. Из меню Вид выбрать Групповые операции и из раскрывающегося списка в бланке запроса выбрать Sum.
4. Выполните запрос. Обратите внимание, что результирующая таблица содержит всего одну запись (рисунок 30).
Для изменения имени поля в режиме Конструктора набрать в строке Поле требуемое имя (в нашем примере - Всего товаров) и отделить от выражения двоеточием,
Рисунок 30 - Результат запроса
ЛАБОРАТОРНАЯ РАБОТА
МОДУЛИ
Задание:
1. Разработать форму и модуль для выполнения расчетов по отдельной таблице Вашей базы данных.
2. Разработать форму и модуль для решения задачи, полученной у преподавателя.
3. Подготовить отчет.
Отчет должен содержать:
1. Титульный лист.
2. Распечатку формы в режиме Конструктора и результаты выполнения модуля.
3. Распечатку программы.
4. Краткое описание работы.
Ход работы:
Начиная работать с модулями в СУБД Access, необходимо иметь навыки в программировании на языке VB (Visual Basic). Разработка модуля производится на языке VBA (Visual Basic for Application), который является общим языком программирования для приложений Microsoft Office.
Модуль представляет набор описаний и процедур на языке Visual Basic for Application для приложений, сохраненных в одном программном блоке.
Рисунок 31 - Окно Модуля
Необходимость разработки собственного модуля возникает, когда в СУБД отсутствуют средства выполнения конкретного задания. Например, нет такой функции, которая смогла бы выполнить вычисление стоимости заказа в таблице "ЗАКАЗЫ".
В Access предполагается применение модулей двух видов:
-стандартных, являющихся объектами базы данных, они отображаются на вкладке Модули окна базы данных;
- модулей форм и отчетов (модули класса), являющиеся частью этих объектов.
Модуль формы - это модуль, содержащий программы всех процедур обработки событий, возникающих в конкретной форме или в ее элементах управления.
Модуль отчета - это модуль, содержащий программы всех процедур обработки событий, возникающих в конкретном отчете или в его элементах управления.
Модули форм и модули отчетов являются модулями класса, связанными с определенной формой или отчетом. Они часто содержат процедуры обработки событий, запускаемые в ответ на событие в форме или отчете. Процедуры обработки событий используются для управления поведением формы или отчета и их откликом на события, такие как нажатие кнопки.
Процедура обработки события - это процедура, автоматически выполняемая в ответ на событие, возникающее в результате действий пользователя, выполнения программы или генерируемое системой.
При создании первой процедуры обработки события для формы или отчета автоматически создается связанный с ней модуль формы или отчета.
В процедурах модулей форм и отчетов могут содержаться вызовы процедур, добавленных в стандартные модули.
В Access 97 и более поздних версиях модули класса могут существовать независимо от форм и отчетов. Этот тип модулей класса отображается в окне базы данных. Модули класса можно использовать для создания описания пользовательского объекта. В Access 95 модуль класса существует только в связи с формой или отчетом.
В стандартных модулях содержатся общие процедуры, не связанные ни с каким объектом, а также часто используемые процедуры, которые могут быть запущены из любого окна базы данных. Основное различие между стандартным модулем и модулем класса, не связанным с конкретным объектом, заключается в области определения и времени жизни. Значение любой переменной или константы, определенной или существующей в модуле класса, не связанном с конкретным объектом, доступно только во время выполнения этой программы и только из этого объекта.
В VBA для приложений имеется два типа процедур:
- функции (процедура Function)
- подпрограмма (процедура Sub).
Процедура-подпрограмма Sub выполняет действие или набор действий, но не возвращает значение. Пользователь имеет возможность создавать процедуры Sub самостоятельно или использовать процедуры обработки событий, определенные в Microsoft Access.
Каждая форма или отчет в базе данных имеет встроенный модуль формы или модуль отчета, содержащий встроенные процедуры обработки событий, которые выполняются в ответ на события, возникающие в форме или отчете и в элементах управления в форме или отчете. После того как Microsoft Access определит, что событие возникло в форме, отчете или в элементе управления, автоматически запускается процедура обработки события, имя которой образуется как комбинация имен объекта и события. Например, процедура обработки события позволяет открыть другую форму при нажатии кнопки в форме.
При создании объекта с помощью мастера (например, мастера кнопок) часто автоматически создаются процедуры обработки событий для данного объекта. Для того чтобы понять, как работают процедуры, полезно ознакомиться с такими процедурами.
Процедура-функция Function (часто такие процедуры называют просто функциями) возвращает значение, например, полученное в результате расчетов. Visual Basic включает ряд встроенных функций;
например, функция Now возвращает текущее значение даты и времени. В дополнение к встроенным функциям, пользователь имеет возможность самостоятельно создавать функции, которые называют специальными или пользовательскими функциями.
Функции, возвращающие значения, могут использоваться в выражениях. Выражения, содержащие функции, широко используются в Microsoft Access, например, в аргументах инструкций или методов Visual Basic, при указании значений свойств и при определении условий в запросах или фильтрах.
После создания функции ее можно использовать в Microsoft Access в любых выражениях.
Функция может быть выполнена из любого места Microsoft Access, в том числе из выражений в формах, запросах, макросах. При работе функции выдается единственное значение, подпрограмма может быть выполнена при вызове из функции, другой подпрограммы или как процедура обработки события в форме или отчете.
Синтаксис инструкции Function:
[Public | Private] [static] Function имя функции инструкции тела функции
END Function
Подпрограмма Sub:
[Public Private] [static] Sub имя подпрограммы инструкции тела подпрограммы ' END Sub
Функция и подпрограмма, описанная с ключевым словом Public, доступны для всех процедур во всех модулях. Ключевое слово Private процедуры Function или процедуры Sub доступны только для других процедур только того модуля, в котором они описаны.
При использовании ключевого слова Static значения всех переменных, объявленных явно или неявно внутри соответствующих процедур, сохраняется в течение всего времени, пока открыт модуль с данной процедурой.
Вопрос о явном объявлении переменных решается пользователем. Для упрощения отладки программ лучше явно объявлять переменные. Для этого, находясь в конструкторе форм, необходимо выполнить команду Вид/ Программа/ Tools и на вкладке Options установить флажок Require Variable Declaration. Режим начинает работать при повторном обращении к команде Вид/ Программа, что будет видно по наличию в модуле строки Option Explicit.
В VBA используются идеи объектно-ориентированного программирования. При разработке программ необходимо ознакомиться с архитектурой приложения Access [1,2]. В соответствие с этой архитектурой различают объекты, классы, семейства, свойства, методы, события.
Объекты - это абстракции, которыми оперируют в объектно-ориентированных языках программирования. Объект обладает собственными характерными признаками, отличающими его от.других объектов. Примерами объектов в СУБД Access служат формы, отчеты, таблицы, запросы.
Класс представляет собой описание совокупности однотипных объектов. Объект представляет из себя экземпляр класса.
Семейство - это упорядоченный набор однотипных объектов, примерами семейств могут служить, например, формы - семейств Forms, элементы управления в форме - семейство Controls и т.д. в свою очередь, как уже отмечалось, семейство тоже является объектом VBA.
Каждый объект имеет свойства и методы, которые различны у разных классов объектов.
Свойством называют отдельную характеристику объекта или класса, например, форма имеет около 100 свойств, элемент поле свыше 70 и т.д.
Метод представляет собой процедуру объекта или класса. Например, для открытия формы можно использовать метод OpenForm объекта DoCmd. DoCmd - объект VBA, который позволяет в программах выполнять макрокоманды. Его синтаксис: DoCmd. метод [аргумент].
Для просмотра объектов, их свойств и методов можно пользоваться окном Object Browser (обозреватель объектов). Для его вызова необходимо, находясь в окне модуля:
1. Нажать F2;
2. Нажать кнопку Object Browser в панели инструментов
3. Выполнить команду View, Object Browser.
При разработке модулей необходимо обращаться к элементам форм, формам, таблицам, полям и т.д., при этом используются ссылки на данные объекты. Синтаксис ссылки на объект внутри семейства следующий:
-имя семейства! [имя объекта], например, Forms! [Заказы]
-имя семейства ["имя объекта"], предыдущий пример примет вид Forms! ("заказы")
-имя семейства (относительный номер объекта). Access нумерует все объекты семейства, начиная с 0. В нашем случае форма заказ будет иметь нулевой номер, поэтому ссылка примет вид: Forms (0).
В качестве примера приведем методику создания программы, определяющей величину стоимости в таблице Заказы.
Перед написанием программы создадим средство, из которого можно будет включить процесс заполнения записи в поле "Стоимость" таблицы "Заказы".
С помощью Конструктора создадим форму "Определение стоимости". В ней разместим лишь кнопку 0, щелчок по которой запустит процесс вычисления и заполнения поля "Стоимость" таблицы.
Форма будет иметь вид:
Рисунок 32 - Вид формы Определение стоимости:
а) в режиме Конструктора; б) в режиме формы
Выполним команду Вид/Программа. На экран выведется окно редактора VBE (Visual Basic Editor), которое может содержать до трех панелей:
1. Панель проекта, в ней приводится дерево объектов нашей базы данных ПРОДАЖИ с указанием места объекта Form - определение стоимости.
2. Панель свойств, в ней отражены свойства выделенного объекта, в нашем случае это Кнопка 0.
3. Панель редактора кода.
Код программы состоит из двух разделов:
- General (общая область), в ней располагаются процедуры программы. В правой части этого раздела располагается кнопка раскрывающегося списка Object. Если нажать на нее, то выведется список разделов, которые может содержать программа:
Form
Кнопка О
Область данных
- Declarations (описание)
Выделим строку Кнопка 0 и щелкнем по ней в разделе General окажется запись Кнопка 0, а в разделе Declarations предлагается использовать событие Click (щелчок по Кнопке 0) для запуска программы, которую нам еще предстоит написать. Программа вычисления с пояснением приведена на рисунке 33. Если нажать на кнопку раскрывающегося списка, то откроется множество событий, которые можно использовать для запуска программы.
В учебном примере приходится удалять значение стоимости в таблице ЗАКАЗЫ, так как в противном случае нет необходимости в вычислениях, после первого вычисления записи в поле Стоимость будут заполнены. Если необходимо сохранить вычисленные значения, то из модуля необходимо удалить второй цикл For NEXT.
|
Рисунок 33 - Программа вычисления
С помощью модуля можно также решать и задачи, не связанные с вычислением или работой с таблицами.
Например, необходимо определить сумму вклада через Т лет, если на настоящий момент вклад составляет А рублей и процент по вкладу составляет Р%.
Для решения этой задачи Конструктором форм разрабатывается форма, в которую вносятся необходимые данные А, Р, Т и результат - сумма вклада через Т лет. Для запуска программы в форме расположим кнопку. Форма будет иметь вид, представленный на рисунке 34. Программа для решения данной задачи представлена на рисунке 35.
Рисунок 34 - Форма для определения вклада через Т лет
|
Рисунок 35 - Программа для вычисления суммы вклада