Глава 6. Отчеты и запросы. Создание накладной на прием товаров

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

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

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

Для того, чтобы создать запрос, на вкладке Запросы окна базы данных нажмем кнопку Создать. Как и при создании других объектов, MS Access предлагает воспользоваться услугами мастеров. На рисунке изображено окно диалога, появляющееся на экране после нажатия кнопки Создать.

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

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

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

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

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

Имеется несколько способов размещения полей в нижней части бланка запроса. Так для вывода всех полей одной из таблиц достаточно выполнить двойной щелчок по значку «*» в первой строке списка полей каждой таблицы в верхней части бланка запроса. Двойной щелчок по имени поля таблицы помещает его в нижнюю часть бланка запроса в первый справа свободный столбец. Можно также перетащить поле таблицы в нужный столбец; если при этом окажется, что столбец занят, он и все остальные столбцы справа передвинутся, освобождая место перемещаемому полю. Наконец, можно в строке Имя таблицы выбрать из списка одну из таблиц запроса, а затем также из списка выбрать имя поля в строке Поле (естественно, в том же столбце бланка).

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

Итак, создадим запрос для накладной на прием товаров. Для этого воспользуемся режимом конструктора и добавим в запрос три таблицы: Товары, Поставщик и Категория товара, т.к. нам понадобится выводить в отчете, помимо сведений о товаре, название категории товара и имя поставщика. Назовем запрос Накладная_прием. Отметим, что в данном случае использованы те связи между таблицами, которые были автоматически предложены в соответствии со схемой данных.

Вычисляемые поля в запросе

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

Если запрос используется в качестве источника данных для нескольких форм и/или отчетов, то предпочтительно ввести вычисляемое поле именно в запросе. В результате мы сможем использовать это поле в каждой форме или отчете, для которого этот запрос является источником данных, а не строить его для каждой такой формы или отчета.

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

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

Использование построителя выражений

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

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

Продемонстрируем работу с построителем на примере приведенной выше формулы. Окно построителя выражений изображено на рисунке.

Само выражение строится в верхней части окна построителя. Это текстовая область, поэтому здесь допускается ввод с клавиатуры. Наиболее часто используемые операторы можно вставить в выражение с помощью кнопок, расположенных ниже этой области. Нижняя часть окна содержит три списка. В левом списке в структурированном виде представлены все объекты базы данных. Чтобы выбрать конкретный объект, нужно выполнить двойной щелчок на папке соответствующей категории, а затем выделить нужный объект. Например, чтобы получить доступ к полям таблицы Категория товара, нужно дважды щелкнуть на папке Таблицы, после чего выделить эту таблицу. В результате в среднем списке появятся элементы объекта, выбранного в левом списке. Например, если в левом списке выбрать запрос Накладная_прием, то в средний список будут выведены поля этого запроса. Наконец, если какой-либо элемент среднего списка сам содержит другие элементы, то при выборе этого элемента в правый список будут выводиться его элементы. Так, если Вам необходимо поместить в выражение одну из встроенных функций MS Access, при выборе объекта Встроенные функции в средний список заносятся категории функций. Конкретная же функция выбирается из правого списка после указания нужной категории. Заметим здесь, что если вы не знаете, в какой из категорий находится нужная функция, можно выбрать элемент "Все"; тогда в правый список будут выведены имена всех имеющихся функций.

Итак, чтобы построить нужное нам выражение, сначала введем в область построения выражений имя поля с двоеточием (Всего:). Выберем в левом списке запрос Накладная_прием (заметим, что этот запрос должен быть предварительно сохранен как объект в базе данных, иначе он будет отсутствовать в левом списке). После этого в среднем списке выберем поле Кол_во_в_упак и нажмем кнопку Вставить (вместо этого можно выполнить двойной щелчок на имени поля). Оператор умножения «*» можно ввести либо с клавиатуры, либо щелкнуть в построителе по соответствующей кнопке. Аналогично вставим второй операнд операции умножения - Кол_во_упак. Выражение

Итак, чтобы построить нужное нам выражение, сначала введем в область построения выражений имя поля с двоеточием (Всего:). Выберем в левом списке запрос Накладная_прием (заметим, что этот запрос должен быть предварительно сохранен как объект в базе данных, иначе он будет отсутствовать в левом списке). После этого в среднем списке выберем поле Кол_во_в_упак и нажмем кнопку Вставить (вместо этого можно выполнить двойной щелчок на имени поля). Оператор умножения «*» можно ввести либо с клавиатуры, либо щелкнуть в построителе по соответствующей кнопке. Аналогично вставим второй операнд операции умножения - Кол_во_упак. Выражение построено; чтобы вернуться в окно конструктора запросов, нажмите кнопку ОК. Уже находясь в конструкторе запросов, не забудьте нажать клавишу ENTER, чтобы выполнить компиляцию построенного выражения. Если синтаксис выражения неверен, появится сообщение об ошибке, после чего можно будет вернуться в построитель и исправить ошибку.

Отчеты

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

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

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

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

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

Для того, чтобы выполнить группировку, помимо описанных выше действий, следует выбрать значение Да хотя бы для одного из свойств - Заголовок группы, Примечание группы – в нижней части окна Сортировка и группировка. При этом слева от имени поля в области выделения появится значок группировки, а в бланке отчета появится соответствующий раздел (разделы). Остальные три свойства имеют отношение к полям, по которым выполняется группировка. Рассмотрим их подробно.

Свойство Группировка для текстовых полей может принимать одно из двух значений: По полному значению или По первым символам. Первое из них означает, что в одну группу будут отобраны те записи, у которых полностью совпадает содержимое соответствующего поля. Если использовать значение По первым символам, то в одну группу попадут записи, у которых в этом поле совпадают первые N символов; при этом число N задается свойством Интервал. Например, если значением свойства Интервал является 5, то группировка будет выполняться по первым пяти символам в данном поле. Если поле, по которому выполняется группировка, имеет тип даты, то группировка, помимо полного значения, может быть выполнена также по годам, кварталам, месяцам, неделям, дням, часам и минутам. В этом случае число, заданное в качестве значения свойства Интервал, определяет временной интервал группировки (например, количество дней или минут).

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

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

Для печати накладных на прием создадим отчет в режиме конструктора. Заметим, что имеется возможность преобразования формы в отчет. Этим можно воспользоваться, если имеется форма, содержащая нужные для отчета поля. Для этого нужно выделить имя формы в окне базы данных и в контекстном меню выбрать команду Сохранить как отчет и далее в окне диалога выбрать тип объекта в списке Как. Лишние элементы управления затем можно удалить в режиме конструктора отчетов.

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

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

Чтобы получить итоговые цифры по каждой накладной, соответствующие формулы следует поместить в раздел примечания группы ‘ФИО_поставщика’. Будем печатать общее количество упаковок (=Sum([Кол_во_упак])) и сумму партии товара (=Sum([Цена_опт]* [Кол_во_в_упак]*[Кол_во_упак])). В зависимости от раздела отчета, в котором используется статистическая функция Sum, суммирование может выполняться для различных наборов записей. Так, если написать эти формулы в полях заголовка или примечания отчета, то суммирование будет выполняться для всех записей из источника данных отчета. В нашем случае суммирование выполняется для каждой накладной, поскольку поля, содержащие эти формулы, находятся в примечании группы ‘ФИО_поставщика’, которая вложена в группу по полю Дата_поступления.

Также в раздел примечания группы ‘ФИО_поставщика’ поместим еще одно вычисляемое поле с формулой =Rub_Kop([Всего]). Здесь Всего – это поле, которое содержит формулу =Sum([Цена_опт]*[Кол_во_в_упак]* [Кол_во_упак]), а функция Rub_Kop() из модуля Общие преобразует аргумент числового типа в текст денежной суммы прописью. В нашем случае прописью будет печататься итоговая сумма по каждой накладной.

Сохраним созданную накладную под именем Накладная на прием. Теперь, чтобы просмотреть накладные, достаточно выбрать отчет в окне базы данных и нажать кнопку Просмотр. Чтобы напечатать отчет, нужно в меню Файл выбрать команду Печать. Заметим, что при конструировании отчета в целях отладки можно переключаться между режимами с помощью меню Вид. Такими режимами являются: Конструктор, Образец и Предварительный просмотр. Режим Образец отличается от режима Предварительный просмотр тем, что при предварительном просмотре используются все записи из источника записей, а в режиме образца только ограниченный набор записей. Поэтому при большом количестве записей и сложном форматировании открытие отчета в режиме предварительного просмотра будет выполняться более медленно, чем в режиме образца.

Если открыть созданный отчет в режиме предварительного просмотра (или образца), то на одной странице может оказаться более одной накладной. Этого можно избежать, если установить конец страницы после печати каждой накладной. Чтобы это сделать, установите для раздела Заголовок группы ‘ФИО_поставщика’ свойство Конец страницы (в окне свойств на вкладке Макет) в значение До раздела или установите для раздела Примечание группы ‘ФИО_поставщика’ свойство Конец страницы в значение После раздела.

Нумерация строк в отчете

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

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

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

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

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

Печать отчетов с отбором записей

Организуем печать накладных из формы Прием товаров, созданной нами ранее (глава 5). Для этого поместим на форму кнопку (с помощью мастера), при нажатии которой отчет будет выводиться в режиме предварительного просмотра. Теперь, чтобы напечатать нужную накладную, следует нажать эту кнопку (мы ее надпишем Печать накладных) и распечатать нужную страницу отчета (команда Файл\Печать...). Напомним, что накладные следуют в порядке убывания даты приема и в алфавитном порядке по поставщикам.

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

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

Печать отчета выполняет команда DoCmd.OpenReport, первым параметром которой является имя отчета (используется строковая переменная stDocName), а вторым – константа acViewPreview, обеспечивающая открытие отчета в режиме предварительного просмотра. Однако эта команда может иметь до четырех параметров:

Метод OpenReport использует следующие аргументы.

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

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

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

Четвертый аргумент представляет собой критерий отбора записей, который является предложением WHERE (без самого этого слова) языка SQL. Описание синтаксиса операторов языка SQL и примеры написания запросов можно найти в главе 11. В этом предложении, которое является строковым выражением, можно задать значения полей источника записей для отчета. В нашем случае мы должны указать поставщика и дату приема товара. Поскольку и ту, и другую информацию мы можем взять из текущей записи, то будем использовать при построении этого предложения содержимое соответствующих полей формы. Суммируя вышесказанное, построим следующее условие отбора записей

Разберем его подробно. Имена в квадратных скобках представляют собой поля источника записей отчета. Другие имена - это имена элементов управления на форме, из которых берется нужная информация. Для формирования условия отбора используется конкатенация (соединение) строк. Дело в том, например, что строка "[Поставщик]=Поставщик_" является ошибочной. Действительно, в этом случае предполагалось бы, что в поле [Поставщик] должно находиться слово ‘Поставщик_’. Нам же требуется значение из поля Поставщик_ на форме. Поэтому к строке "[Поставщик]=" при помощи оператора конкатенации строк () добавляется не строка символов ‘Поставщик_’, а значение из соответствующего поля. Значениями рассматриваемого поля являются числа (код поставщика), однако, если бы значения соответствующих полей представляли собой строковые выражения, то этот фрагмент выглядел бы так

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

Оператор AND соединяет два условия, которые должны выполняться одновременно. Функция Format() позволяет изменить формат даты. Проблема представления даты связана с возможностью использования региональных настроек. Если мы используем привычный формат "день-месяц-год", который можно поменять в панели управления Windows, то при программном использовании даты в условиях отбора нам придется поменять местами месяц и день (mm-dd-yy), поскольку в кодах

Как видно, используются настройки «день-месяц-год». Следующая тестовая функция при помощи объекта Recordset выбирает из таблицы две даты – 08/15/06 и 03/08/06.

Результатом выполнения функции будет «15 августа 2006; 08 марта 2006». Если мы вместо #08/15/06# напишем в условиях отбора #15/08/06#, то получим следующее сообщение об ошибке: «Нет текущей записи». Это означает, что запрос ничего не вернул, и набор записей пуст. Поэтому последующее использование rst!дата и дает ошибку. Второй пример, на наш взгляд, демонстрирует значительно худший вариант. Если в первом случае сообщение об ошибке заставит нас задуматься

Символ решетки «#» служит ограничителем даты (аналогично кавычкам, ограничивающим строку символов).

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