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

Теперь, когда в нашем распоряжении имеется сводная таблица, наступило время для создания, вычисляемого поля. Чтобы создать вычисляемое поле, активизируйте диалоговое окно Вставка вычисляемого поля (Insert Calculated Field).

Чтобы открыть его, на контекстной вкладке ленты Параметры (Options), выбранной в группе контекстных вкладок Работа со сводными таблицами (PivotTable Tools), перейдите в группу Вычисления (Calculations), щелкните на кнопке Поля, элементы и наборы (Fields, Items & Sets) и выберите в раскрывающемся меню команду Вычисляемое поле (Calculated Field), как показано на рис. 5.7.

На экране появится диалоговое окно Вставка вычисляемого поля (Insert Calculation Field), как показано на рис. 5.8.

В верхней части диалогового окна имеются два текстовых поля: Имя (Name) и Формула (Formula). В этих полях следует задать имя вычисляемого поля и создать формулу, указав необходимые поля данных и математические операторы. На рис. 5.9 показано, что вычисляемому полю присвоено описательное имя Средняя выручка за час. Это имя должно точно характеризовать тип выполняемой математической операции.

Далее перейдите к списку Поля (Fields) и дважды щелкните на поле Объем продаж. Введите символ косой черты /, чтобы указать программе Excel, что вы будете делить значение поля Объем продаж на другой элемент.

По умолчанию текстовое поле Формула диалогового окна Вставка вычисляемого поля содержит выражение = 0. Перед вводом собственной формулы следует удалить нуль.

С этого момента диалоговое окно должно выглядеть так, как показано на рис. 5.10.

Рис. 5.10. Начните ввод формулы со значения = ‘Объем продаж’/

Закончите ввод формулы, дважды щелкнув на поле Период продаж (в часах). Готовая формула показана на рис. 5.11.

Рис 5.11. Полная формула = ‘Объем продаж’/’Период продаж (в часах)’ отображает требуемое вычисляемое поле

Щелкните на кнопке Добавить (Add), а затем - на кнопке ОК для активизации нового вычисляемого поля. Как видно на рис. 5.12, в результате вы получите вычисляемое поле внутри сводной таблицы.

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

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

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

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

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

1. Создайте свободный элемент управления типа Поле (Text Box) в нужном месте формы.
2. Прямо в поле введите выражение =Date (). Знак равенства обязателен.
3. Откройте окно Свойства (Properties) для этого элемента и задайте для свойства Формат поля (Format) желаемый формат даты, например Длинный формат даты (Long Date).
4. Установите длину поля таким образом, чтобы в нем помещалось необходимое количество символов.
5. Измените название метки этого текстового поля. Созданное поле будет выглядеть, как показано на рис. 9.47.

Рис. 9.47.

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

Именно в свойстве Данные (Control Source) должно быть задано выражение, которое вычисляет значение этого элемента. Ввод выражения допускается прямо в элемент управления, как мы и сделали это в примере. Однако, если выражение достаточно длинное, его неудобно вводить прямо в поле. Можно ввести выражение в ячейку свойства Данные (Control Source) в окне Свойства (Properties), а если выражение не помещается в ячейке свойства, просто нажать комбинацию клавиш + и открыть окно Область ввода (Zoom). Еще можно воспользоваться Построителем выражений.

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

• Первый способ основан на том, что форма строится на базе запроса, в который включают вычисляемые столбцы. В форме создается текстовое поле, у которого в качестве источника данных указывается вычисляемый столбец запроса. Поле блокируется, чтобы пользователь не мог изменить данные в нем, ведь эти данные вычисляются в процессе выполнения запроса.
• Во втором случае форма может строиться прямо на базе таблицы. В ней создается текстовое поле, в котором в качестве источника данных указывается выражение, например = [Цена за единицу] * Количество. При этом в выражении необязательно указываются те поля, которые включены в форму, а любые поля базовой таблицы.

Первый способ в большинстве случаев предпочтительнее, т. к. такой запрос может быть использован не только в одной форме, но и в отчетах и в других формах. Примером такой организации расчетов может служить форма "Подчиненная форма заказов" (Orders Subform) базы данных "Борей". Она основана на запросе "Сведения о заказах" (Order Details Extended), в котором создано вычисляемое поле "ОтпускнаяЦена" (ExtendedPrice), содержащее формулу.

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

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

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

2. На панели элементов выберите элемент Поле и поместите его в необходимое место области данных.

3. Сделайте выравнивание надписи поля и самого поля по своему усмотрению.

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

5. Щелчком правой кнопки мыши выполните вызов контекстного меню для самого поля и выберите пункт Свойства.

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

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

· Щелчок мыши по арифметическому знаку позволит Вам также поместить его в создаваемую формулу.

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

8. Сохраните созданную форму и откройте ее в режиме формы. В случае обнаружения ошибок вернитесь в режим конструктора.

Задания

1. Создайте поле, вычисляющее стоимость каждой поставки для Экземпляров медиатеки.

2. Для этого воспользуйтесь представленной выше последовательностью действий и следующими замечаниями:

· вычисляемое поле необходимо создавать в форме «Поставка подчиненная форма»;

· откройте эту форму в режиме «Конструктор»;

· выберите элемент управления «поле»;

· в надписи поля внесите «Стоимость поставки», в поле формулу =[Цена]*[Количество] (где [Цена] и [Количество]– это имена соответствующих полей таблицы «Поставка», которые используются в форме для отображения необходимых данных).

3. Откройте форму «Поставка подчиненная форма» и убедитесь, что вычисляемое поле работает верно.

4. Откройте форму «Экземпляр», посмотрите, видно ли вновь созданное поле. При необходимости внесите изменения в размер подчиненной формы, чтобы все данные отображались.

5. Сохраните изменения, результат продемонстрируйте преподавателю.

Создание главной кнопочной формы

Главная кнопочная форма – это форма, которая загружается первой при открытии БД. Данная форма должна содержать кнопки, по нажатию которых Вы сможете иметь доступ (открывать, распечатывать и т.д.) ко всем объектам Вашей БД: формам, запросам, отчетам. Кроме того, на ней должна быть кнопка выхода (как и на каждой из форм), либо кнопка возврата на главную форму.

При создании Главной кнопочной формы можно использовать:

1) режим Конструктора ();

2) Диспетчер кнопочных форм (меню Сервис/Служебные программ/Диспетчер кнопочных форм).

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

1. Создание «макета» формы:

· на закладке Форма выберите команду Создать;

· в качестве режима создания формы укажите Режим Конструктора;

· источник данных для формы не указывайте (вновь созданная форма имеет только область данных);

· сохраните форму по именем «Главная».

2. Вставка графического объекта в форму:

· на Панели Элементов выберите элемент управления Рисунок;

· поместите его в область данных;

· в появившемся диалоговом окне выберите графический файл (*.bmp) для вставки его в область данных;

· изменить свойства объекта можно, вызвав для него контекстное меню и воспользовавшись необходимыми командами.

3. Создание кнопки:

· на панели элементов выберите элемент управления кнопка;

· поместите его в область данных.

· выберите из списка категорий необходимую для Вас (например, Работа с формой);

· из списка действий выберите то, которое Вы предполагаете осуществлять с помощью данной кнопки (например, Закрытие формы). Нажмите кнопку Далее;

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

· дайте название вновь созданной кнопке. Рекомендуется оставлять нумерацию системы (название по умолчанию «Кнопка N», где N – номер по порядку).

· нажмите кнопку Готово;

· вид и расположение вновь созданной кнопки Вы можете изменить, воспользовавшись контекстным меню и соответствующими его командами;

· откройте форму в режиме формы, проверьте работу кнопки.

Задания

1. Построить главную кнопочную форму для учебной задачи, руководствуясь информацией пункта «Создание главной кнопочной формы» и следующими требованиями:

· на форме должен быть рисунок, соответствующий теме учебной задачи;

· каждые элементы созданной системы должны быть доступны из отдельной вкладки: формы, отчеты, запросы;

· на форме должны быть кнопки запуска элементов и закрытия формы;

· кнопки для запуска запросов и отчетов могут отсутствовать или существовать в виде «заглушек», то есть кнопок без назначенного действия;

· на каждой из созданных ранее форм должны быть кнопки закрытия формы и перехода на Главную форму (создайте на созданных Вами формах «Поставщик» и «Экземпляр» необходимые кнопки: создания новой записи, удаления записи, закрытия формы и перехода на главную) (см. рис. 5, 6).

Примечание: кнопки и другие элементы управления, позволяющие работать с остальными объектами БД, необходимо будет добавить при выполнении последующих лабораторных работ (11 и 12).

2. Сохраните Главную форму.

3. Проверьте ее работу.

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

Вопросы для самоконтроля

1. Для чего используется форма как объект СУБД MS Access?

2. Какие области формы Вы знаете, каково предназначение каждой из них?

3. В чем отличие Ленточного метода компоновки данных на форме (внешний вид) от Табличного?

4. В чем отличие метода компоновки данных на форме «Сводная таблица» от «Сводная диаграмма»?

5. В чем отличие метода компоновки данных на форме «В один столбец» от «Выровненного»?

6. Можно ли изменить выбранный стиль формы, если да, то как это сделать?

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

8. Какая часть элемента управления «поле» может быть изменена, а какая нет и почему?

9. Каков алгоритм создания подчиненной формы в режиме Мастера?

11. Можно ли использовать при записи вычисляемого выражения имена других вычисляемых полей данной формы (ответ обоснуйте)?

12. Определите назначение главной кнопочной формы.

13. Какова последовательность действий при работе с Диспетчером кнопочных форм? Укажите преимущества и недостатки данного режима?

14. Позволяет ли режим Конструктора форм создавать формы на основе нескольких таблиц или запросов, если да, то каким образом это осуществляется?

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

ЛАБОРАТОРНАЯ РАБОТА № 11
Конструирование запросов в СУБД MS Access

1. Конструирование запроса на выборку.

2. Конструирование запросов на удаление и обновление.

3. Конструирование запроса с параметром.

Цель: научиться конструировать запросы на выборку, удаление и параметрические запросы.

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

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

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

Известно, что в БД Microsoft Access вычисляемые поля могут быть созданы в запросе, форме или отчете. Рассмотрим создание вычисляемых полей в отчете базы данных Access 2003. Для ввода Выражения в форму ее необходимо открыть в режиме Конструктора и создать свободное текстовое поле, используя кнопку "ab " (Поле) на панели инструментов. Слева от созданного свободного поля располагается метка, связанная с текстовым полем, в которой отображается порядковый номер введенного поля.

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

Рассмотрим создание вычисляемого поля в форме Студенты на примере базы данныхTraining_students_VP . Постановка задачи: в форме БД Access Студенты_конструктор создать одно поле, в котором должна выводиться текущая дата, и другое поле, которое объединяет фамилию, имя и отчество студента. Для решения этой задачи откроем форму Студенты_конструктор в режиме конструктора (рис. 1).

Рис. 1.

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

• нажать кнопку "ab " (Поле) на панели инструментов, а затем щелкнуть на форме в том месте, где планируется вставить свободное текстовое поле;
• затем установить курсор в поле "Свободный" (рис. 2) и ввести функцию: =Date();
• следующий шаг - изменить название метки текстового поля (рис. 2), для этого надо удалить порядковый номер (в нашем примере Поле30) и ввести слово "Дата: ";

Рис. 2.

• изменить длину метки и текстового поля так, чтобы в них поместилось содержимое;
• отформатировать вычисляемое поле (при необходимости), для этого необходимо щелкнуть правой кнопкой мыши на вычисляемое поле и в контекстном меню выбрать команду Свойства, откроется диалоговое окно "Поле". Форматирование осуществляется в окне "Поле" на вкладке "Все" в строке "Формат поля" (рис. 3);

Рис. 3.

• закрыть окно Поле;
• просмотреть форму БД Access в режиме формы, выполнив щелчок по кнопке Открыть (рис. 4);

Рис. 4.

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

• добавить свободное поле на форму (рисунок 5);

Рис. 5.

• изменить название метки текстового поля на ФИО;
• вызвать построитель выражений, для этого щелкнуть на панели инструментов на пиктограмме "Построитель", откроется окно Построитель, в котором надо выделить "выражение" (рисунок 6) и щелкнуть ОК;

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

Чтобы добавить к отчету вычисляемое поле "Сумма":

1. Откройте отчет "Товары на складе" в режиме Конструктора.
2. На панели элементов нажмите кнопку Надпись (Label) и поместите новую надпись справа от надписи "На складе" (Units in Stock) в разделе верхнего колонтитула. Введите в качестве текста надписи слово "Сумма" (Sum).
3. Чтобы создать вычисляемое поле "Сумма" (Sum), нажмите кнопку Поле (Text Box) на панели элементов и добавьте текстовое поле справа от текстового поля "НаСкладе" (UnitsInStock) в разделе данных. Удалите метку нового текстового поля.
4. Щелкните внутри текстового поля и введите в качестве его значения выражение:

   = [НаСкладе]* [Цена] (= * ).

   Откройте окно свойств поля, дважды щелкнув по нему, и на вкладке Макет (Format) в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).

Замечание

Ввести выражение в текстовое поле можно несколькими способами: выделите его и нажмите комбинацию клавиш +, чтобы войти в режим редактирования содержимого поля, или сделайте двойной щелчок по полю для отображения диалогового окна Свойства (Properties), а затем введите выражение как значение свойства Данные (Source).

Рис. 6.27.

1. Повторите шаги 3 и 4 для того, чтобы создать текстовое поле в разделе примечаний группы "Категория" (Category), но в качестве выражения для вычисления промежуточного значения следует ввести: =Sum([НаСкладе] * [Цена]) (=Sum( * )). Нажмите кнопку Полужирный (Bold) на панели форматирования, чтобы установить полужирный шрифт для текста в новом поле. Откройте диалоговое окно Свойства (Properties) и раскройте в нем вкладку Другие (Other), в поле Имя (Name) введите имя текстового поля "Сумма" (Sum). Затем раскройте вкладку Макет (Format) и в поле со списком свойства Формат поля (Format) выберите значение Денежный (Currency).
2. Поскольку ширина области данных нашего отчета увеличилась, уменьшите ширину полей страницы, чтобы все поля поместились на одной странице. Для этого выберите команду Файл, Параметры страницы (File, Page Setup) и задайте вместо ширины левого и правого полей, заданных по умолчанию, значение 10 мм (см. разд. "Просмотр и печать отчетов" в этой главе).
3. Нажмите кнопку Вид (View) на панели инструментов Конструктор отчетов (Report Design), чтобы оценить результаты своей работы. Отчет будет выглядеть так, как показано на рис. 6.27. К прежним данным добавился крайний правый столбец со значениями стоимости каждого товара в имеющемся количестве на складе и промежуточных итоговых сумм по стоимости товаров для каждой категории.