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

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

В качестве примера рассчитаем гонорары экспертов. Предположим, что час работы оценивается в 30% базовой величины заработной платы. Для кандидатов наук применяется коэффициент 1,2, для докторов наук – 1,5, для специалистов без ученой степени – 1.

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

Сформируем запрос. Будем использовать таблицы Эксперты и Выполненная работа. Из них выберем поля Фамилия, Кол-во часов, Тарифный коэффициент. В строке Поле одного из свободных столбцов бланка заказов введите название нового поля Гонорар. (После имени нового поля всегда ставится двоеточие!) После этого вызовите Построитель выражений кнопкой Построить - на панели инструментов. В окне построителя выражений дважды щелкните мышью по типу объектов Табли­цы. Используя знаки операций и список полей нужных таблиц, по­стройте формулу для вычисления. Затем нажмите кнопку OK.

 

Гонорар:0,3*20000* [Кол-во часов]*[Тарифный коэффициент]

Для достижения цели необходимо сгруппировать результат (команда Вид/Групповые операции). В строке Групповая операция выберите для поля Фамилия функцию Группировка, для полей Кол-во часов, Гонорар – функцию Sum, для поля Тарифный коэффициент – функцию First (функция First выводит первое найденное значение).

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

Сохраните запрос под именем «Гонорар».

 

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

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

Для примера составим запрос на отбор сведений об экспертах, используя поиск по задаваемому коду организации.

Создайте запрос на основе таблиц Организации и Эксперты с выводом следующих полей: Код организации, Организация, Фамилия, Ученая степень, Должность.

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

Сохраните запрос под именем «Эксперты организаций».

 

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

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

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

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

 

ФОРМЫ В СУБД ACCESS

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

Access содержит большой набор инструментов для автоматического создания элементов интерфейса. Наиболее простой способ создания формы – использование Мастеров.

 

Автоформы

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

 

 

Мастера форм

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

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

 

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

Используя Мастер форм, в качестве главной таблицы выберите таблицу Эксперты и в ней поля Фамилия, Ученая степень, Должность. Второй таблицей выберите Выполненную работу и в ней поля: Дата, Кол-во часов, Тема. На следующем шаге выберите внешний вид подчиненной формы, и далее выполнив остальные шаги, получите окончательный результат.

 

Конструктор форм

Откройте форму «Организации» в режиме Конструктора.

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

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

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

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

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

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

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

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

 

Элементы форм

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

Поле со списком создается для ввода данных или выбора значений из списка. Создайте в форме дополнительный элемент – поле со списком организаций и их кодов. Такой список удобен для ввода кода организации при обновлении данных. В режиме Конструктор включите пиктограмму Панель элементов для того, чтобы вывести на экран необходимые для работы инструменты. Выберите на панели пиктограмму Поле со списком и укажите положение и размер создаваемого окна в области данных экрана. Далее, выберите способ присвоения значений – в данном случае вариант "…использовать значение из таблицы или запроса". Следующий шаг – выбор таблицы с такими значениями (Организации) и полей, в которых эти значения хранятся ("Организация", "Код организации"). Запомните, что нельзя использовать поле типа MEMO. Далее укажите, что выбранное значение следует хранить в поле "Код организации". Благодаря этой команде код выбранной организации будет автоматически загружен в поле "Код организации" заполняемой таблицы. Последний шаг – укажите подпись, которая будет сопровождать поле со списком ("Ввод кода организации").

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

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

 

Фильтры

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

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

Для выбора критериев фильтрации удобно использовать пиктограмму Фильтр по выделенному. Например, если в автоформе надо отобрать экспертов со степенью кандидатов наук, то достаточно в поле Ученая степень выделить первую букву «к» и нажать пиктограмму Фильтр по выделенному. Мастер автоматически составит запрос на фильтрацию и выведет результат отбора на экран.

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

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

ОТЧЕТЫ В ACCESS

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

 

Автоотчет

Самый быстрый и простой способ создания отчета – использование пиктограммы Новый объект/Автоотчет после выбора таблицы или запроса. Создайте Автоотчет для одного из запросов.

 

Мастер отчетов

Использование мастеров в Access – удобный и простой способ создания более сложных отчетов. В окне базы данных переключитесь на объект Отчеты, нажмите кнопку Создать и в диалоговом окне Новый отчет выберите команду Мастер отчетов. В этом же окне выберите в качестве источника данных таблицу Организации. Далее в диалоговом окне Создание отчетов выберите поле Организация. Здесь же из таблицы Эксперты выберите поле Фамилия, а из таблицы Выполненная работа – поля Дата работы, Кол-во часов, Тема. При выполнении инструкций Мастера добавьте третий уровень группировки по Дате работы. Далее Мастер предложит порядок сортировки данных (допускается сортировка записей не более чем по четырем полям) и представит инструмент для вычислений в записях (кнопка ИТОГИ...). Ответив на вопросы Мастера по оформлению макета отчета и присвоив ему имя «Работа экспертов», получите готовый к печати отчет.

 

Конструктор отчетов

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

Вставьте в область заголовка рисунок (Вставка/Объект/Рисунок Microsoft Word) и переместите его левее заголовка. Размер заголовка установите 26 пт.

На Панели элементов выберите объект Надпись. В нижний колонтитул вставьте надпись: свою фамилию и номер группы.

 

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

 

Мастер диаграмм

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

Серийные письма

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

Сначала создайте запрос "Список", выбрав необходимые данные об экспертах (поля Фамилия, Должность, Ученая степень, Организация, Руководитель). Выбрав в окне базы данных этот запрос, используйте команду Сервис/ Связи с Office/ Слияние с MS Word. Можно работать как с подготовленным ранее документом MS Word, так и создать новый. Во втором случае Access запускает Word, устанавливает DDE-связь между Word и Access и открывает новый текстовый документ, в котором можно составить новое письмо. Затем в подготовленное письмо вставляются управляющие поля. При печати серийных писем они заменятся данными соответствующей таблицы базы данных. Количество серийных писем определяется числом записей в таблице (запросе).

Составьте следующее серийное письмо (выделенные курсивом названия полей вводятся при помощи кнопки Добавить Поле слияния!):

 

«Организация»

Уважаемый г-н «Руководитель»!

Выражаем благодарность за то, что «Должность»

«Ученая степень» «Фамилия» принял активное и плодотворное участие
в работе Научно-консультативного центра.

 

Дирекция НКЦ

 

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

Сохранив и закрыв текстовый документ, вы вернетесь в Access. Благодаря DDE-связи при изменении данных в таблице Access данные в серийном письме обновятся автоматически. Если вы будете открывать файл с серийным письмом в WS Word, то автоматически загрузится Access с нужной базой данных.

 

 

* * *

 

Знакомство с другими объектами СУБД (макросы, модули, страницы) предполагается при более глубоком изучении программы.