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

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

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

Математическое выражение может содержать имена полей, числа и символы математических операций, а также имена встроенных функций.

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

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

Например, для нашего примера вычисляемое поле должно содержать такую формулу:

Стоимость:[Количество]*[Цена]

Фрагмент бланка запроса в данном случае должен выглядеть так:

Если поля, которые используются в вычислении, присутствуют в бланке запроса, то в строке Групповая операция вычисляемого поля надо выбрать операцию Выражение. Иногда простейших математических операций бывает недостаточно, чтобы выполнить требуемые вычисления. Чтобы решить эту проблему, в MSAccess определено примерно 150 встроенных функций. Охарактеризуем некоторые из них, сгруппированные по своему назначению.

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

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

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

· Функции даты и времени. Используются при обработке дат и времени. Возвращают дату и время полностью или частично (год, месяц, день), например, функция Date() формирует текущую дату, функция Month() выделяет месяц из значения поля, содержащего дату.

· Функции обработки текста. Используются при работе с символьными строками.

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

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

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

Параметрические запросы представляют собой варианты базового запроса и отличаются друг от друга только другими значениями в условиях отбора. Они используются для повышения гибкости запроса – предоставления выбора пользователю в одном и том же запросе с различными значениями некоторого параметра в пределах поля. Например, выдавать данные о каждом товаре по отдельности. В рамках запроса на выборку надо сделать столько запросов, сколько наименований товаров в таблице. Для запроса с параметром достаточно одного. Для этого служит специальная команда SQLLike[], вводимая в нужном поле в Условии отбора. В квадратных скобках можно поместить любой текст, который будет отображаться в диалоговом окнеВведите значение параметра над окном ввода. Запрос можно вызывать многократно, изменяя значение параметра. Параметр (особенно текстовый) надо вводить очень аккуратно, т. к. программа сравнивает значение параметра со значением в таблице посимвольно.

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

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

Перекрёстные запросы

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

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

Перекрёстный запрос можно создавать или с помощью мастера перекрёстного запроса или сразу в режиме конструктора.

Если имеется таблица или запрос со всей необходимой для построения перекрёстного запроса информацией, то запрос лучше создавать с помощью мастера перекрестного запроса (Создание/Мастер запросов/Перекрестный). Если такого объекта нет, то надо сначала создать запрос на выборку с необходимой информацией и на его основе создавать перекрёстный запрос.

Удобнее создавать перекрестный запрос с помощью команды Создание/Конструктор запросов. На вкладке Создание нажмите Перекрестный. В бланке запроса отображаются строки Групповая операция и Перекрестная таблица.

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

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

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

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

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

Создание отчётов

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

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

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

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

Разделы отчета

Отчет обычно состоит из нескольких областей.

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

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

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

-Заголовок группыиспользуется при группировке запи­сей отчета для вывода названия группы и однократного отображения по­лей, по которым производится группировка. Отображается перед каждой новой группой записей. Например, если отчет сгруппирован по покупателям, в заголовке группы можно указать название покупателя, а также адрес, телефон и другие реквизиты. Допускается до 10 уровней группировки выводимых записей.

-Область данныхотображает записи из источника данных, со­ставляющие основное содержание отчета.

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

-Нижний колонтитулприменяется для нумерации страниц и отображе­ния другой информации внизу каждой страницы.

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

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

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

Некоторые области могут быть пустыми (не содержать данных).

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

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

Элементы отчета

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

Свойства отчета

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

Свойства, собранные на вкладке Макет, определяют,каквыводятся данные:

- размещение поля на листе (от левого края, от верхнего края);

- размеры поля (ширина, высота);

- «внешний» вид поля и выводимых в нем данных (тип фона, цвет фона, оформление, тип границы, цвет границы, ширина границы, цвет текста, шрифт, размер шрифта, насыщенность, курсив, подчеркнутый, выравнивание текста);

- способ представления данных в поле (формат поля, число десятичных знаков, вывод на экран, расширение, сжатие).

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

Свойства на вкладке Данные определяют, что выводится в поле.

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

Если требуется изменить свойства нескольких полей, то их выделяют при нажатой левой кнопке мыши, а затем указывают необходимые свойства. Они распространяются на все выделенные поля.

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

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

Создание отчёта рассмотрим на следующем примере. Пусть мы задумали получить отчёт в таком виде.

ОТЧЁТ О ДЕЯТЕЛЬНОСТИ ФИРМЫ

Кафе «Парус»

Название продукта                Общий вес (кг) Стоимость (руб)

…                                                      …                                    …       

                                                                                              Итого руб

Ресторан «Волга»

Название продукта                Общий вес (кг) Стоимость (руб)

…                                                         …                                 …

                                                                                              Итого руб

25.03.2013

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

Будем считать, что он называется Итоги.