Глава 9. Группировка в запросах и запросы с параметрами (прибыль и убытки; списание и возврат)

В этой главе мы расширим свое представление о запросах. Будут использоваться вычисляемые поля, группировка и параметры в запросах. Сформулируем задачу. Требуется получить информацию о продажах товаров за любой заданный период времени, т.е. установить, сколько и какого товара было продано, скажем, с 1/05/99 по 31/12/99, какой была прибыль и какой получен доход. Кроме того, важным для принятия управленческих решений является распределение экономических показателей по категориям товаров, магазинам и т.д.

Сначала создадим запрос, который будет подсчитывать доход за любой период времени (назовем его Доход по интервалу). Добавим в него две связанные таблицы Товары и Продажи и выберем следующие поля: Наименование, Цена_опт и Цена_розн. Остальную нужную нам информацию получим при помощи вычисляемых полей запроса. Нам нужно подсчитать прибыль, убытки и доход. Прибыль составляет объем продаж, умноженный на разницу между розничной и оптовой ценой данного товара, убытки – это количество списанных единиц товара, умноженных на оптовую цену товара, а доход – это разность между прибылью и убытками. Добавим в запрос следующие поля:

При расчете количества списанных и проданных единиц товара мы использовали функцию IIF, которая работает следующим образом. Если условие (первый аргумент) истинно, то функция возвращает значение второго аргумента, в противном случае третьего. Т.е. если в поле Прод_возвр находится значение Сп (списано), то значение из поля Количество добавляется к общей сумме, в противном случае – нет (добавление нуля).

Для разделения аргументов в функциях, используемых в запросах, используется символ, заданный параметром в региональных настройках Windows. По умолчанию для русифицированной версии ОС используется точка с запятой («;»). Если функцию IIF использовать в модуле, то аргументы функций нужно разделять запятыми, как это принято в VBA.

Группировка в запросах

Прежде чем вводить эти поля в запрос, в меню Вид нужно выбрать команду Групповые операции. Дело в том, что при подсчете количества списанного и проданного товара, необходимо выполнять суммирование (т.к. партия товара продается в течение некоторого времени и может быть зарегистрировано несколько продаж одного и того же товара). Кроме того, суммирование должно выполняться для каждого наименования товара в отдельности (неправильно в нашем случае суммировать сапоги и пироги). Группировка как раз и будет указывать, по каким группам производить суммирование в запросе; а именно, это будет выполняться по тем группам, которые характеризуются одинаковой информацией в полях, для которых в строке Групповая операция выбран параметр Группировка. У нас это поля Наименование, Цена_опт и Цена_розн.

Заметим, что поле в строке Групповая операция бланка запроса не может оставаться пустым. Здесь должно быть выбрано одно из значений списка. Такими значениями, кроме упомянутой группировки, являются: Выражение, Условие, а также ряд статистических функций. Выражение создает вычисляемое поле с помощью математического выражения, которое должно включать статистическую функцию. Обычно вычисляемое поле создается, если требуется включить в выражение несколько функций. Условие определяет условия отбора для поля, которое не участвует в группировке. Если для поля выбирается этот параметр, автоматически снимается флажок Вывод на экран, и данное поле не выводится на экран при выполнении запроса.

В следующей таблице перечислены имеющиеся статистические функции.

Для вышеприведенных вычисляемых полей групповой операцией будет Выражение. На рисунке показан фрагмент рассматриваемого запроса в режиме конструктора.

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

Теперь нам нужно, чтобы этот запрос давал информацию по интервалу дат, причем даты эти при каждом выполнении запроса могут быть разными. Эта проблема решается с помощью параметров запроса. Параметры используются для ввода в запрос значений, неизвестных на момент создания запроса, или значений, которые могут изменяться со временем. Параметры вводятся в запрос в режиме конструктора с помощью команды Запрос\Параметры. Имена введенных параметров затем можно использовать в вычисляемых полях и условиях отбора. При всяком выполнении запроса сначала на экране возникают окна для ввода значений каждого из параметров, после чего запрос выполняется с учетом этих введенных значений. Имеется следующая особенность: всякое поле, которого нет в таблицах запроса и которое не определено в запросе как вычисляемое, считается параметром, хотя оно может и не быть явно определено как параметр. Это часто вызывает недоумение у неопытных пользователей. Если при написании вычисляемого поля Списано вы, скажем, вместо имени поля Количество по ошибке введете Колич, то при выполнении запроса появится следующее диалоговое окно

Если параметр не является следствием ошибки, нужно ввести с клавиатуры значение требуемого параметра и нажать кнопку ОК. Кнопка Отмена отменяет выполнение запроса. Рассмотренная выше ошибочная ситуация является еще одним доводом в пользу применения построителя выражений.

Пусть нам требуется отобрать записи с датой продажи, лежащей в интервале, который может быть произвольным. В соответствии с вышесказанным введем в условие отбора для поля Дата_продажи следующее выражение

Это значит, что запрос будет выполняться только для товаров, регистрация продаж которых велась в интервале от [Дата от] до [Дата до] включительно. Значения параметров [Дата от] и [Дата до] будут вводиться нами с клавиатуры при каждом выполнении запроса. Заметим, что дату нужно будет вводить в соответствующем формате (например, 01/02/99).

Для проверки работы запроса можно перейти в режим таблицы (Вид\Режим таблицы). Созданным запросом можно пользоваться непосредственно, если открыть его из окна базы данных или перейти в режим таблицы из конструктора запросов. Однако мы воспользуемся этим запросом в несколько расширенном виде, взяв его в качестве источника данных для отчета о финансовой деятельности предприятия.

Отчеты с формированием условий отбора записей

Выше в этой главе был рассмотрен вопрос использования параметров для выполнения запроса с изменяющимися условиями отбора записей. Здесь мы рассмотрим печать одного и того же отчета, в котором будут выводиться различные данные в зависимости от различных условий отбора записей. Мы разработаем отчет и подготовим запрос, служащий источником записей для отчета. Однако теперь изменяющиеся условия отбора мы реализуем не с помощью параметров запроса, а посредством некоторой вспомогательной формы, в которой мы будем формировать условия отбора записей для отчета. Эта форма позволит нам, во-первых, сформулировать достаточно сложные условия отбора с переменным числом параметров, а, во-вторых, воспользоваться выбором значений в условиях отбора из списков, а не вводить их всякий раз при помощи клавиатуры, как это происходит при использовании параметров запроса.

Разработаем отчет, в котором будет печататься информация о доходах за любой период времени. При этом будем выводить итоговую сумму дохода, раскладку его по магазинам и промежуточные итоги по категориям товаров для каждого магазина.

Имея в виду вышесказанное, сначала подготовим запрос. За основу примем созданный ранее запрос Доход по интервалу, сделаем его копию и назовем ее Доходы от продаж. Дело в том, что почти вся необходимая нам информация уже содержится в запросе Доход по интервалу. Осталось добавить в этот запрос таблицу Категория товара, поля Наименование_кат, Код_категории, Магазин и убрать условие отбора для поля Дата_продажи – смотри рисунок.

Теперь создадим отчет. Воспользуемся мастером отчетов, используя в качестве источника данных только что созданный запрос Доходы от продаж. Выбираемые для отчета поля и уровни группировки представлены на рисунке, на котором изображено окно диалога мастера отчетов. На следующем шаге мастера выберем поля Убытки, Прибыль и Доход, по которым будут подведены итоги, и функцию суммирования – Sum – для чего следует нажать кнопку Итоги.... По умолчанию отчет будет называться так же, как и запрос – Доходы от продаж.

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

Перейдем теперь к построению формы, с помощью которой будут формироваться критерии и выводиться на печать отчеты

Теперь нужно организовать печать отчета из этой формы, используя поля формы для формирования критерия отбора записей в отчете. Для решения задачи воспользуемся событийной процедурой нажатия кнопки Печать отчета, которую предварительно нужно поместить на форму.

Дополним созданную мастером событийную процедуру следующими строками кода:

Здесь мы формируем критерий отбора записей по интервалу дат, а именно, дата в поле Дата_продажи должна находиться в интервале от даты, введенной в поле ДатаОт, до даты в поле ДатаДо включительно. Функция Format() позволяет изменить формат даты. Мы используем формат "mm-dd-yy" (месяц-день-год) исходя из того, что мы используем русифицированные региональные настройки (день-месяц-год), а в кодах мы вынуждены использовать настройки, принятые в языке SQL. Наконец, символ # служит ограничителем даты (аналогично двойным кавычкам, ограничивающим строку символов).

Вышеприведенные строки кода дополняют критерий информацией из остальных полей, если они не пусты. То есть код магазина должен совпадать с выбранным из списка значением, а код категории - с кодом выбранной из списка категории. Если оставить эти поля пустыми (или одно из них), то в критерий не будет добавлено соответствующее ограничение, и будет печататься информация по всем магазинам и/или по всем категориям.

Осталось воспользоваться этим критерием при формировании отчета. Это делается при помощи аргумента команды DoCmd.OpenReport, которая уже использовалась нами при печати накладных на прием, итак:

Вернемся к отчету. В заголовке отчета имеются свободные поля От и До с надписями «с» и «по» соответственно . Предполагается в этих полях выводить даты границ временного интервала. Данные мы можем взять из формы Финансовая деятельность, т.к. она будет открыта на момент печати отчета. Событийная процедура для формирования значений в этих полях связана с событием форматирования заголовка отчета, где находятся эти поля. Таким образом, процедура должна иметь вид

Обратите внимание, что для ссылки на форму используется служебное слово Forms, описывающее коллекцию форм текущей базы данных. При этом форма конкретизируется строковым параметром, представляющим имя, под которым форма хранится в базе данных. Можно обращаться к элементам управления только открытой формы; в противном случае возникает ошибка с выводом на экран соответствующего сообщения.

Наконец, в заголовке группы ‘Магазин’ надпись ‘Доход’ будем менять на слово ‘Убытки’, если значение в поле будет отрицательным. В соответствии с этим событийная процедура форматирования заголовка группы будет содержать одну инструкцию:

Этим кодом формируется надпись с именем Дох_маг посредством изменения свойства Caption (подпись) данного элемента управления.

Для многостраничных отчетов полезно сделать печать шапки на каждой странице и обеспечить нумерацию страниц. Чтобы шапка печаталась на каждой странице, ее нужно поместить в область верхнего колонтитула (Вид\Колонтитулы). Для того, чтобы организовать нумерацию страниц, можно в режиме конструктора отчета выбрать в меню Вставка команду Номер страницы.

Есть, по крайней мере, один недостаток в работе рассматриваемой формы. Если задать критерии отбора, которые приводят к отсутствию данных для построения отчета, то мы будем получать сообщение об ошибке в процедурах форматирования заголовков отчета, которые не учитывают данную ситуацию. Например, можно задать интервал дат, когда не было продаж в некотором магазине. Самый простой способ избежать таких ошибок заключается в обработке события Отсутствие данных для отчета. Это событие как раз и наступает, когда при печати или просмотре отчета оказывается, что источник данных для него не содержит ни одной строки. Процедура обработки этого события имеет параметр Cancel, которому нужно присвоить значение True, чтобы прервать выполнение формирования отчета. Кроме того, необходимо сообщить пользователю, почему отчет не печатается. Мы можем это сделать при помощи процедуры VBA MsgBox (смотри приложение 2). То есть мы можем написать такую процедуру:

Эта процедура даст нам то, что нужно. Попробуйте, например, выбрать седьмой магазин и не задавать других критериев отбора (будет использован интервал дат по умолчанию). Правда появляется еще одно окно с сообщением, что прервано выполнение макрокоманды OpenReport. Чтобы закрыть это окно, пользователю придется еще раз щелкнуть по кнопке ОК в окне сообщения или нажать клавишу ENTER. Этим мы и можем воспользоваться, чтобы подавить появление излишнего сообщения. Идея заключается в том, чтобы программным образом передать код нажатой клавиши. Это можно сделать при помощи процедуры SendKeys, которая имеет один строковый параметр – последовательность символов, передаваемых так, как будто были нажаты соответствующие клавиши на клавиатуре. Можно указывать и не символьные клавиши, дополнительно заключая их в фигурные скобки. В нашем случае вызов данной процедуры будет выглядеть следующим образом:

Этот код и нужно добавить последней строкой в приведенную выше событийную процедуру.

Использование макросов и модулей (списание и возврат)

Для правильного подсчета прибыли и убытков необходимо оформлять списание и возврат товаров. Напомним, что в таблице Товары есть поле Возврат, в котором мы отмечаем возможность возврата. Используется следующая схема. По истечении срока годности товара его остатки возвращаются поставщику (если таковая возможность имеется). При этом оформляется соответствующая накладная. В противном случае оформляется списание, и фирма несет убытки. Поле Возврат в сочетании со сроком реализации (поле Срок_годн) может использоваться и для обработки информации о товарах, взятых на реализацию. В этом случае по истечении срока реализации имеется возможность вернуть остатки товара, если возврат предусмотрен.

Ниже мы создадим форму для просмотра списка товаров на списание и возврат. При этом предусмотрим вывод этой информации порознь. Из формы мы будем печатать накладные на списание и возврат с одновременной записью соответствующей информации в таблицу Продажи.

Начнем с создания запроса Списание/Возврат, который будет служить источником данных для создаваемой формы. В этот запрос добавим запрос Накладная_прием, который содержит всю необходимую информацию.

Добавим в запрос вычисляемые поля:

Условиями отбора записей служат истечение срока годности и ненулевой остаток.

Создадим ленточную форму Списание/Возврат, показанную на рисунке. Источником записей для этой формы будет служить запрос с аналогичным названием, рассмотренный выше . Будем использовать контрольные индикаторы (флажки), чтобы можно было вывести на форме как товары на списание (установлен один флажок «На списание») или возврат (установлен один флажок «На возврат»), так и все товары, для которых истек срок реализации (установлены оба флажка или ни один из них). В событийную процедуру После обновления одного из флажков запишем следующий код:

Заметим, что если флажок установлен, то его значением является True (истина), в противном случае – False (ложь). В процедуре используются имена, которые следует дать флажкам при конструировании формы: ФлажокСписание и ФлажокВозврат.

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

Таким образом, при любом изменении состояния любого флажка, будет отрабатывать вышеприведенная процедура.

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

Теперь нужно организовать процедуру списания (возврата) с одновременным оформлением накладной. Подготовим отчет, который будет представлять эти накладные. Отчет Возвратная накладная в режиме Конструктор показан на рисунке.

Используемые в отчете функции: shortname(), rub_kop() находятся в модуле Общие учебной базы данных, которую можно скачать по адресу http://msi77.narod.ru/Tables_2003.zip. Тексты этих и некоторых других процедур приведены в приложении 1. Функция shortname() используется для формирования фамилии и инициалов из полного имени, а функция rub_kop() формирует на основании числового аргумента денежную сумму прописью.

Процедуры и функции, размещенные в модуле, доступны из любых объектов базы данных, если их описание не сопровождается служебным словом Private. В последнем случае процедура или функция будет доступна только в пределах данного модуля. Так, например, вызов функции shortname() можно использовать в вычисляемых полях запросов, любой событийной процедуре или в формуле, служащей источником данных элемента управления на форме или в отчете.

Чтобы накладные печатались при нажатии кнопок формы, нужно в событийную процедуру Нажатие кнопки ввести код, реализующий печать накладной (смотри, например, главу 6). Однако поступим иначе. Создадим два простых макроса, которые будут выполнять процедуру Sp_Voz() из модуля Общие. Эта процедура помимо печати отчета добавляет записи в таблицу Продажи о списанных или возвращенных товарах.

Для создания такого макроса в окне базы данных на вкладке Макросы нажмем кнопку Создать. Макрос будет состоять из одной макрокоманды ЗапускПрограммы, которая выбирается из списка в поле Макрокоманда. Эта макрокоманда выполняет функцию VBA, имя которой следует ввести в соответствующей строке аргументов макрокоманды (в нашем случае - это функция Sp_Voz()). Это можно сделать и с помощью Построителя. Аргументом функции является значение True – для списания и False – для возврата.

Теперь, чтобы задействовать этот макрос при нажатии кнопки Оформление возврата на форме, нужно на вкладке События окна свойств для этой кнопки выбрать имя макроса в строке Нажатие кнопки.

С помощью макрокоманды может быть выполнена только функция, но не процедура.

Осталось устранить один недостаток. При списании и возврате товаров у нас используется один и тот же отчет Возвратная накладная. Использование различных условий отбора записей позволяет нам печатать соответствующие данные. Однако заголовок отчета остается одним и тем же - "Возвратная накладная №". Это же относится к слову "Возврат " перед именем поставщика. Будем теперь формировать текст накладной в зависимости от того, какая кнопка нажата. Поместим в событийную процедуру открытия отчета следующие строки кода:

Здесь проверяется, была ли нажата (активна) кнопка Списание на форме Списание/Возврат. Если условие выполнено, то формируется заголовок "Накладная на списание №" и слово "Списание" около имени поставщика. В противном случае текст остается прежним (что и требуется). Обратите внимание на то, как формируется ссылка на элемент управления. ActiveControl.Caption – это то, что написано на активном элементе управления. Активным является элемент, имеющий фокус. Фокус получает элемент управления, если щелкнуть на нем мышью. Кроме того, фокус можно перемещать от элемента к элементу с помощью клавиши TAB. Естественно, при нажатии кнопки она и получает фокус. Объект Screen (экран) представляет отдельную форму, отчет, которые в текущий момент