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

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

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

Угоны автомобилей	Предыдущий год	Текущий год
Из гаражей	10	15
Со стоянок	30	40

Например, при запуске запроса для приведенной выше таблицы, содержащего формулу - Угнано всего: [Предыдущий год]+[Текущий год], в результирующей таблице появится новый столбец - Угнано всего. Его вторая строка будет иметь значение 25 (10+15), а третья - 70 (30+40).

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

Марка автомобиля	Модель автомобиля	Число аварий за год
ВАЗ	2101	9
ВАЗ	2105	4
Москвич	412	3
ВАЗ	2108	12
ВАЗ	2109	18
ВАЗ	21099	8
Москвич	2140	7
ВАЗ	2110	3
ГАЗ	3110	5
ГАЗ	24	3
ЗАЗ	968	2

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

Задание 3

1. Запустить СУБД Access. Создать в папке Мои документы базу данных DB??, вместо вопросительных знаков вставить в название две цифры - номер вашей группы. Открыть базу данных DB??, импортировать в нее таблицу А1975 из базы данных db1, расположенной в папке 00000_371\Inform\Access, а затем открыть импортированную таблицу.

2. Создать запрос на выборку из таблицы А1975 родившихся в г. Омске лиц с именем Александр, первая буква фамилии у которых «Л», и лиц с именем Алексей, первая буква фамилии у которых «М». Вывести на экран данные только из полей NUM, FAMILY, IMY, OTCH, DATE и GOROD. Записать номера полученных записей в блокнот.

3. Создать запрос на выборку из таблицы А1975 лиц, родившихся в 1957 году, задать сортировку по возрастанию для поля NUM и вывод на экран данных из полей NUM, FAMILY, IMY, OTCH. В полученной динамической таблице при помощи команды Найти из меню Правка найти лицо с фамилией, начинающейся с букв «Луг» и заменить в его отчестве букву «т» на «р». Закрыть динамическую таблицу, найти указанное лицо в таблице А1975 и убедиться, что исправления внесены в исходную таблицу. Записать номер полученной записи в блокнот.

4. Создать запрос с параметром для поля DATE на выборку из таблицы А1975 лиц по условию >[ДАТА], в запросе установить поля NUM FAMILY, IMY, OTCH, DATE. Запустить запрос, ввести первый параметр - 01.01.1957 и просмотреть результаты выборки в динамической таблице. Вновь запустить запрос, ввести второй параметр - 30.12.1958 и записать номера полученных записей в блокнот.

5. При помощи запроса на создание таблицы создать в текущей базе данных новую таблицу NT с полями NUM, FAMILY, IMY, OTCH, DATE, содержащую записи на лиц с именем Владимир из таблицы А1975.

6. Создать запрос на добавление записей на лиц с именем Иван из таблицы А1975 в таблицу NT.

7. Добавить в таблицу NT новое числовое поле - Число записей на лицо.

8. Для таблицы NT создать запрос на обновление, изменяющий имя Владимир на имя Вальдемар и позволяющий вставить значение 1 в поле Число записей на лицо.

9. Создать запрос для расчета числа записей с именами «Вальдемар» и «Иван» в таблице NT, сохранить запрос под именем Расчет.

10. Создать запрос с полями NUM, FAMILY, IMY, OTCH, DATE на удаление из таблицы А1975 лиц родившихся позже 1959 года. Записать номера удаляемых записей в блокнот.

Формы

Одной из основных задач создания и использования баз данных является предоставление пользователям необходимой информации на основе существующих данных. В Access для этих целей предназначены формы и отчеты.

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

Создание формы

В MS Access для создания форм применяются автоформы, мастер форм и конструктор форм.

Рис. 22. Выбор способа создания формы

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

С формой можно работать как с таблицей БД, например, задать для нее фильтр, составить запрос или отредактировать данные в полях формы.

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

Рис. 23. Окно формы

Окно формы (рис. 23) состоит из трех областей: заголовка, области данных и примечания, которые могут содержать элементы управления. К элементам управления относятся: надписи, поля, списки, поля со списком, выключатели, переключатели, флажки, кнопки, вкладки, рисунки, рамки для вывода объектов OLE, линии и прямоугольники. Для изменения характеристик какого либо элемента окна формы, необходимо его сначала выделить щелчком мыши, а затем модифицировать используя, например, панель инструментов Формат. Маркеры выделения позволяют изменять размеры выделенного элемента, а методом drag and drop можно изменить его расположение в окне формы.

Создание элементов окна формы

Панель инструментов Конструктор форм в Access (рис. 24) содержит кнопки, предназначенные для разработки форм.

Рис. 24. Панель инструментов Конструктор форм

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

Рис. 25. Панель элементов

В качестве примера рассмотрим порядок создания в области заголовка элементов управления Надпись, Поле и Рисунок.

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

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

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

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

Отчеты

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

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

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

Access предлагает несколько способов создания отчетов (см. рис. 26). Наиболее простым из них является использование средств автоматического создания отчета. Автоматически создаваемый на основе таблицы или запроса отчет называется Автоотчетом. Access позволяет автоматически создавать отчеты двух форматов: в столбец и ленточный.

Рис. 26. Диалоговое окно Новый отчет

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

Рассмотрим процесс создания отчета при помощи мастера. На панели объектов окна базы данных щелкните по ярлыку Отчеты и нажмите кнопку Создать. Появится диалоговое окно Новый отчет (см. рис. 26). В списке диалогового окна Новый отчет выделите элемент Мастер отчетов. В поле со списком, находящемся в нижней части диалогового окна Новый отчет, содержатся имена всех таблиц и запросов базы данных, которые могут быть использованы в качестве источника данных для отчета. Раскройте этот список и выделите в нем нужную таблицу или запрос. Нажмите кнопку ОК. Появится первое диалоговое окно Мастера отчетов (рис. 27).

).

Рис. 27. Первое диалоговое окно Мастера отчетов

Далее нужно определить, какие поля включить в отчет. В отчете поля появляются слева направо, в соответствии с последовательностью их расположения в списке Выбранные поля.

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

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

Рис. 28. Второе диалоговое окно Мастера отчетов

Рис. 29. Сортировка записей.

Access позволяет сортировать записи в пределах группы по значению произвольного поля, при этом сортировка проводится не более чем по четырем полям (рис. 6.8). По умолчанию принят порядок сортировки по возрастанию; если вы хотите выбрать порядок сортировки по убыванию, то нажмите на кнопку справа от раскрывающегося списка.

Чтобы указать для столбца отчета итоговую информацию, нажмите на кнопку Итоги. В появившемся диалоговом окне Итоги, можно задать параметры для выбранного столбца. Выбрать можно среди всех числовых полей отчета, не являющихся счетчиками, которые Мастер отчетов перечислит в этом диалоговом окне и позволит установить для любого из них флажки Sum, Avg, Min и Мах. В зависимости от установленных флажков Мастер отчетов добавит соответствующие итоговые поля в конец отчета, вычисляющие итоговую сумму, среднее значение, минимальное или максимальное значение данного поля по всем записям. Группа переключателей Показать (Show) позволяет выбрать, следует ли в отчете указывать только итоги или необходимо выдавать полный отчет, добавляя поля с промежуточными итогами в конце каждой группы и поля с полными итогами в конце отчета. Для возврата к диалоговому окну Мастера отчетов нажмите кнопку ОК.

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

Рис. 30. Выбор вида макета

В следующем диалоговом окне Мастера отчетов выберите один из предложенных стилей оформления отчета. В левой части диалогового окна Мастера отображается эскиз отчета выбранного стиля (рис. 31).

Рис. 31. Выбор стиля отчета

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

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

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

Рис. 32. Отчет в режиме конструктора.

В области заголовка печатается заголовок отчета. Элементы управления области данных обеспечивают вывод данных из таблиц базы для печати на принтере. Верхний и нижний колонтитулы могут быть использованы для печати подзаголовков, номеров страниц, а также для размещения дополнительных элементов управления. Например, на рис.32 в нижнем колонтитуле, выводится текущая дата (для этого используется функция =Now( ) ), общее число страниц номер текущей страницы из общего числа страниц (для этого используются функции Page () и Pages () ). Текст, записанный в кавычках, выводится вместе со значениями указанных функций, а оператор & используется для объединения этого текста и значений функций. Область Примечание отчета в примере не используется. Для корректировки отчета можно использовать панель элементов, команды из меню Вид (например, Свойства) и из меню Формат (например, команды выравнивания, установки размеров и интервалов).