Базы данных в MS ACCESS

Московский гуманитарный университет Серия «Информатика» А. Ю. Выжигин, Г. Н. Семенов Базы данных в MS ACCESS Учебное пособие Издательство Московского гуманитарного университета 2005 ББК 99.99 Х 99 Х 99 Выжигин А. Ю., Семенов Г. Н. Базы данных в MS ACCESS. Учебное пособие.– М.: Московский гуманитарный университет, 2005.– 110 с.– (Серия «Информатика»). Рецензенты: Хныкин А. П. – к.т.н., доцент, заместитель декана факультета информатики Московской государственной академии приборостроения и информатики Харьков В. П. – к.т.н., с.н.с., заведующий кафедрой информатики, прикладной математики и естественнонаучных дисциплин Национального института бизнеса ISBN-9-9999-999-9 Учебное пособие предназначено для использования при изучении дисциплин «Информатика», «Математика и информатика», «Базы данных» и содержит основные приемы проектирования баз данных и их сопровождения в системе управления баз данных MS Access. Пособие может быть использовано как для работы в компьютерном классе, так и при самостоятельной подготовке к зачету и экзамену. ББК 99.99 ISBN-9-99999-999-9 © Выжигин А. Ю., Семенов Г. Н., 2005 © Московский гуманитарный университет, 2005 Оглавление Терминология 5 Первичные ключи 8 Реляционные отношения между таблицами 10 Отношение один-ко-многим 10 Отношение один-к-одному 11 Отношение многие-ко-многим 11 Ссылочная целостность 12 Индексы 15 Проектирование баз данных 18 Нормализация базы данных 18 Избыточность данных и аномалии 19 Приведение к нормальным формам 21 Первая нормальная форма 23 Вторая нормальная форма 24 Третья нормальная форма 26 Средства CASE 28 Занятие 1. Базы данных и конструктор таблиц 32 Базы данных 32 Упражнение 1. Создание таблиц с помощью мастера таблиц 33 Упражнение 2. Заполнение таблицы 36 Конструктор таблиц 38 Упражнение 3. Создание таблицы в режиме конструктора 38 Упражнение 4. Связывание таблиц с использованием списка подстановки 40 Упражнение 5. Связывание таблиц и обеспечение целостности данных 44 Упражнение 6. Установка параметров полей 48 Упражнение 7. Индексирование полей 52 Контрольное упражнение 55 Подведение итогов 56 Занятие № 2. Формы и отчеты 57 Форма 57 Упражнение 1. Мастер форм и отображение данных таблиц 57 Упражнение 2. Настройка форм с помощью конструктора 59 Упражнение 3. Применение элементов управления 63 Упражнение 4. Создание заголовка и примечания 67 Отчет 70 Упражнение 5. Применение мастера отчетов 70 Упражнение 6. Конструирование отчетов 73 Упражнение 7. Группировка и сортировка данных в отчете 78 Вывод результатов 81 Упражнение 8. Демонстрация отчетов и их предварительный просмотр 82 Упражнение 9. Печать таблиц и запросов 82 Упражнение 10. Сжатие, восстановление, защита базы данных и связь с другими приложениями 84 Контрольное упражнение 86 Подведение итогов 86 Занятие 3. Запросы, фильтры, подбор требуемых данных 87 Фильтры 87 Упражнение 1. Установка фильтра 87 Упражнение 2. Преобразование фильтра в запрос 89 Запросы 91 Упражнение 3. Построение запросов с помощью мастера 91 Упражнение 4. Конструирование сложных запросов 93 Упражнение 5. Условие отбора в запросе 96 Упражнение 6. Создание итогового запроса 98 Упражнение 7. Запрос действия 102 Контрольное упражнение 105 Подведение итогов 105 Ответы на вопросы контрольных упражнений 106 Контрольный вопрос Занятие 1.1 106 Контрольный вопрос Занятие 1.2 106 Контрольный вопрос Занятие 1.3 106 Контрольный вопрос Занятие 1.4 107 Контрольный вопрос Занятие 2.1 107 Контрольный вопрос Занятие 2.2 107 Контрольный вопрос Занятие 2.3 107 Контрольный вопрос Занятие 3.3 107 Контрольный вопрос Занятие 3.4 108 Контрольный вопрос Занятие 3.5 108 Список литературы 109 Терминология Совместно используемый набор логически связанных данных (и описание этих данных), предназначенный для удовлетворения информационных потребностей организации, пользователя называют базой данных. Применительно к информатике базами данных (БД) называют электронные хранилища информации, доступ к ко­торым осуществляется с помощью одного или нескольких компьютеров. Обычно БД создается для хранения и доступа к данным, содержащим сведения о некоторой пред­метной области, то есть некоторой области человеческой деятельности или области реального мира. Программное обеспечение, с помощью которого пользователи могут определять, создавать и поддерживать базы данных, а также осуществлять к ним контролируемый доступ, называют системами управления базами данных (СУБД). СУБД – это программное обеспечение, которое взаимодействует с прикладными программами пользователя и базой данных и обладает следующими возможностями: • позволяет определять базу данных с возможностью предоставления средств указания типа данных и их структуры, а также средства задания ограничений для информации, хранимой в БД; • позволяет вставлять, обновлять, удалять и извлекать информацию из БД; • предоставляет контролируемый доступ к базе данных с помощью: ◦ системы обеспечения безопасности, предотвращающей несанкционированный доступ к БД со стороны пользователей; ◦ системы поддержки целостности данных, обеспечивающей непротиворечивое состояние хранимых данных; ◦ системы управления параллельной работой приложений, контролирующей процессы их совместного доступа к БД; ◦ системы восстановления, позволяющей восстановить БД до предыдущего непротиворечивого состояния, нарушенного в результате сбоя аппаратного или программного обеспечения. Различают три основных вида СУБД: промышленные универсального назначения, промышлен­ные специального назначения и разрабатываемые для конкретного заказчика. Специа­лизированные СУБД создаются для управления базами данных конкретного назначения - бухгалтерские, складские, банковские и т.д. Универсальные СУБД не имеют четко очерченных рамок применения, они рассчитаны «на все случаи жизни» и, как следствие, достаточно сложны и требуют от пользователя специальных знаний. Как специализированные, так и универсальные промышленные СУБД относительно деше­вы, достаточно надежны (отлажены) и готовы к немедленной работе, в то время как заказные СУБД требуют существенных затрат, а их подготовка к работе и отладка занимают значительный период времени (от нескольких месяцев до нескольких лет). Однако, в отличие от промышленных, заказные СУБД в максимальной степени учитывают специфику работы заказчика (того или иного предприятия), их интерфейс обычно интуитивно понятен пользователям и не требует от них специальных знаний. В зависимости от расположения СУБД различают локальные и распределенные (удаленные) СУБД. Все части локальной СУБД размещаются на компьютере пользо­вателя базы данных. Если к одной БД обращаются несколько пользователей одновре­менно, каждый пользовательский компьютер должен иметь свою копию локальной СУБД. В отличие от этого, значительная часть программно-аппаратных средств рас­пределенной СУБД централизована и находится на одном достаточно мощном ком­пьютере (сервере), в то время как компьютеры пользователей несут относительно небольшую часть СУБД, которая называется клиентом. Локальные СУБД могут рабо­тать в сети, но могут и не использовать ее, в то время как распределенные (клиент-серверные) СУБД обязательно работают в компьютерной сети. Заметим, что местона­хождение собственно базы данных никак не влияет на специфику СУБД: в локальных СУБД сама БД может располагаться как на компьютере пользователя, так и на уда­ленном сетевом компьютере (файл-сервере). Безусловным достоинством клиент-серверных систем является возможность централизованного управления доступом к БД. В таких системах база данных в значительной мере защищена как от случайных, так и намеренных искажений, в них проще реализовать целостность и непротиворечи­вость данных. СУБД по месту расположения: локальные = и БД, и СУБД располагаются на: 1) 1 ПК вне сети 2) на нескольких ПК в сети (на каждом) при изменении состояния 1-й БД эта вся БД копируется сразу на все ПК распределенные (удаленные) клиент-серверные (2-х уровневые) 1) 1 уровень БД и основная СУБД (на сервере) 2) 2 уровень только часть СУБД с возможностью реализации запросов и получения ответов с сервера БД клиент-серверные (3-х уровневые) 1) сервер БД 2) Сервер СУБД 3) Локальные клиенты с малой СУБД Единицей хранящейся в БД информации является таблица. Каждая таблица представляет собой совокупность строк и столбцов, где строки соответствуют экземпляру объекта, конкретному событию или явлению, а столбцы - атрибутам (признакам, характеристикам, параметрам) объекта, события, явления. На рис.1 приведен пример таблицы, в которой содержатся сведения об отпуске товаров со склада. Столбцы представляют собой такие параметры, как дата отпуска товара, наименование товара, на­именование покупателя, количество единиц отпущенного товара. Каждая строка со­держит сведения о конкретном событии - отпуске товара покупателю. В терминах БД столбцы таблицы называются полями, а ее строки - записями. Дата Товар Покупатель Отпущено (ед.) 10.02.97 Сахар Геракл, ТОО 100 10.02. 97 Сахар Геракл, ТОО 100 12.02.97 Сахар Пищеторг, ЗАО 2000 12.02.97 Макароны Пищеторг, ЗАО 300 14.02.97 Сахар Геракл, ТОО 200 15.02. 97 Дрожжи База № 28 100 Рисунок 1. Пример таблицы «Отпуск товаров» Между отдельными таблицами БД могут существовать связи. Например, информация о покупателе в предыдущей таблице может дополняться в другой: Покупатель Адрес Телефон Геракл, ТОО 107005 Москва, 2-я Бауманская ул. , 12 273-00-14 Пищеторг, ЗАО 105066 Москва, Измайловский б-р, 18/11 165-18-99 База № 28 274088 Хотьково МО, ул. Лесная, 1 17-54 Рисунок 2. Пример таблицы «Покупатель» В данном учебном пособии рассматриваются только реляционные базы данных. Реляционная модель основана на математическом понятии отношения (relation), физическим представлением которого является таблица. В реляционной модели отношения используются для хранения информации об объектах, представленных в базе данных. Отношение – плоская таблица, состоящая из столбцов и строк. Строки соответствуют отдельным записям, а столбцы – атрибутам. Атрибут – это поименованный столбец отношения. База данных, между отдельными таблицами которой существуют связи, называется реляционной. Связанные отношениями таблицы взаимодействуют по принципу главная (master) - подчиненная (detail). В нашем примере таблица «Отпуск товаров» - главная, а таблица «Покупатель» - подчиненная. Главную таблицу часто называют родительской, а под­чиненную - дочерней. Одна и та же таблица может быть главной по отношению к од­ной таблице БД и дочерней по отношению к другой. Первичные ключи В каждой таблице БД может существовать первичный ключ - поле или набор полей, однозначно идентифицирующий запись. Значение первичного ключа в таблице БД должно быть уникальным, то есть в таблице не должно существовать двух или более записей с одинаковым значением первичного ключа. Первичные ключи облегчают установление связи между таблицами. В таблице «Покупатель» таким ключом является одноименное поле. Установив связь по первичному ключу, мы можем выяснить, что, например, 10.02.97 со склада было отпущено 100 единиц товара «Сахар» покупателю «Геракл, ТОО», офис которого расположен по адресу: 107005 Москва, 2-я Бауманская ул., 12 (телефон для связи 273-00-14). Поскольку первичный ключ должен быть уникальным, для него могут использо­ваться не все поля таблицы. В приведенном примере название покупателя вряд ли может быть уникальным, поэтому поле «Покупатель» не может использоваться в ка­честве первичного ключа. Значительно более редким является совпадение телефонов у двух разных покупателей, поэтому поле «Телефон» в большей степени подходит на роль первичного ключа. Если в таблице нет полей, значения в которых уникальны, для создания первичного ключа в нее обычно вводят дополнительное числовое поле, зна­чениями которого СУБД может распоряжаться по своему усмотрению. Если, напри­мер, в таблицу «Покупатель» добавить поле «№№», то связанные таблицы выглядели бы так: Дата Товар Покупатель Отпущено (ед.) 10.02.97 Сахар 1 100 10.02. 97 Сахар 1 100 12.02.97 Сахар 2 2000 12.02.97 Макароны 2 300 14.02. 97 Сахар 1 200 15.02.97 Дрожжи 3 100 №№ Покупатель Адрес Телефон 1 Геракл, ТОО 107005 Москва, 2-я Бауманская ул., 12 273-00-14 2 Пищеторг, ЗАО 105066 Москва, Измайловский б-р, 18/11 165-18-99 3 База № 28 274088 Хотьково МО, ул. Лесная, 1 17-54 Рисунок 2а. Установка связи между таблицами по первичному ключу Теперь в таблице «Отпуск товаров» в поле «Покупатель» указывается значение первичного ключа, построенного по полю «№№» таблицы «Покупатель», что позволя­ет установить однозначную связь между таблицами. Реляционные отношения между таблицами Отношение один-ко-многим Товар Ед. изм. Цена ед. Товар Дата Кол-во (ед.) Сахар Кг 5000 Сахар 10.01.97 100 Макароны Кг 7000 Сахар 12.01.97 200 Куры Кг 10000 Сахар 14.01.97 50 Фанта Бут 1 л. 6000 Макароны 10.01.97 1000 Макароны 11.01.97 500 Фанта 10.01.97 2000 Фанта 12.01.97 3000 Таблица «Товары» Таблица «Отпуск товаров» Рисунок 3. Связь один-ко-многим Как видно из рис. 3, одной записи из родительской таблицы «Товары» может со­ответствовать несколько записей в дочерней таблице «Отпуск товаров». Обратите внимание на глагол может: он означает, что такая возможность - потенциальная и что в родительской таблице могут быть записи, для которых в данный момент нет записей в дочерней таблице (например, товар «Куры»). Различают две разновидности связи один-ко-многим: в первом случае выдвигается жесткое требование, согласно которому всякой записи в родительской таблице долж­ны соответствовать записи в дочерней таблице; во втором случае подобное требование не носит жесткого характера и подразумевается (как в описанном выше случае), что некоторые записи в родительской таблице могут не иметь связанных с ними записей в дочерней таблице. Связь один-ко-многим является самой распространенной для реляционных баз данных. Как можно заметить, она позволяет моделировать иерархические структуры данных. Отношение один-к-одному Отношение один-к-одному имеет место, когда одной записи в родительской таблице соответствует одна запись в дочерней таблице (рис. 4). Таблица «Сотрудники» Таблица «Информация о сотрудниках» № ФИО Должность Отдел № Год рожд. Кол-во детей № 1 Иванов И.И Инженер 10 1 1940 3 1 2 Петров П.П. Бухгалтер 20 2 1942 1 2 3 Васин В.В. Прораб 10 3 1960 2 3 Рисунок 4. Связь один-к-одному Данное отношение встречается много реже, чем отношение один-ко-многим. Его используют, если не хотят, чтобы таблица БД «распухала» от второстепенной инфор­мации. Связь один-к-одному приводит к тому, что для чтения связанной информации в нескольких таблицах приходится производить несколько операций чтения, что замед­ляет получение нужной информации. Кроме того, базы данных, в состав которых вхо­дят таблицы со связью один-к-одному, не могут считаться полностью нормализован­ными (о нормализации см. ниже). Подобно связи один-ко-многим, связь один-к-одному может быть жесткой и неже­сткой. Отношение многие-ко-многим На рис. 5 показаны таблицы, состоящие в отношении многие-ко-многим. Каждой учебной группе соответствует несколько преподавателей. Каждый преподаватель мо­жет вести, во-первых, несколько разных предметов и, во-вторых, преподавать в разных группах. Некоторые СУБД не поддерживают связи многие-ко-многим на уровне индексов и ссылочной целостности (см. следующий подраздел), хотя и позволяют реализовывать ее в таблицах неявным образом. Считается, что БД можно перестроить так, чтобы любая связь многие-ко-многим была заменена на одну или более связей один-ко-многим. Таблица «Учебные группы и дисциплины» Таблица «Преподаватели» Группа Предмет № преподавателя № преподавателя ФИО Кафедра ПС-1 Программирование 10 10 Краснов Ю.Б. ТИ-1 ТИ-1 Программирование 12 12 Володин В.Н. ТИ-1 ПС-1 Теория систем 10 62 Булгаков В.Н. РИО РТ-2 Философия 62 78 Гноенский Л.С. ТИ-1 ПС-1 Социология 62 85 Полушкин М.А. ЭИ-1 … Рисунок 5. Связь многие-ко-многим Между записями одной таблицы также могут существовать связи, то есть одни за­писи могут ссылаться на другие. Ссылочная целостность Рассмотрим наиболее часто встречающуюся в базах данных связь один-ко-многим. Соответствующие таблицы показаны на рис. 6. Как можно заметить, дочерняя и родительская таблицы связаны между собой по общему полю «Товар». Назовем это поле полем связи. Возможны два вида изменений, которые приведут к утере связей между записями в родительской и дочерней таблицах: • изменение значения поля связи в записи родительской таблицы без изменения значений полей связи в соответствующих записях дочерней таблицы; • изменение значения поля связи в одной из записей дочерней таблицы без соответствующего изменения значения полей связи в родительской и дочерней таблицах. Товар Ед. изм. Цена ед. Товар Дата Кол-во (ед.) Сахар Кг 5000 Сахар 10.01.97 100 Макароны Кг 7000 Сахар 12.01.97 200 Куры Кг 10000 Сахар 14.01.97 50 Фанта Бут. 1 л. 6000 Макароны 10.01.97 1000 Макароны 11.01.97 500 Фанта 10.01.97 2000 Фанта 12.01.97 3000 Таблица «Товары» Таблица «Отпуск товаров» Рисунок 6. Связанные таблицы базы данных Рассмотрим первый случай. На рис. 7 показано изменение значения поля «Товар» с «Сахар» на «Рафинад» в таблице «Товары». В таблице «Отпуск товаров» значение поля связи «Сахар» осталось прежним. В результате: • в дочерней таблице «Отпуск товаров» для товара «Рафинад» (таблица «Товары») нет сведений о его отпуске со склада; • некоторые записи таблицы «Отпуск товаров» содержат сведения об отпуске товара «Сахар», о котором нет информации в таблице «Товары». Товар Ед. изм. Цена ед. Товар Дата Кол-во (ед.) РАФИНАД Кг 5000 Сахар 10.01.97 100 Макароны Кг 7000 Сахар 12.01.97 200 Куры Кг 10000 Сахар 14.01.97 50 Фанта Бут. 1 л. 6000 Макароны 10.01.97 1000 Макароны 11.01.97 500 Фанта 10.01.97 2000 Фанта 12.01.97 3000 Таблица «Товары» Таблица «Отпуск товаров» Рисунок 7. Нарушение целостности базы данных: записи с товаром «Сахар» (таблица «Отпуск товарок») не имеют родительской записи Рассмотрим второй случай. Пусть в одной из записей таблицы «Отпуск товаров» значение поля связи «Сахар» изменилось на «Рафинад» (рис. 8). В результате: • в дочерней таблице «Отпуск товаров» недостоверны сведения об отпуске со склада товара «Сахар» (таблица «Товары»); • одна из записей таблицы «Отпуск товаров» содержит данные об отпуске товара «Рафинад», сведения о котором отсутствуют в таблице «Товары». Товар Ед. изм. Цена ед. Товар Дата Кол-во (ед.) Сахар Кг 5000 РАФИНАД 10.01.97 100 Макароны Кг 7000 Сахар 12.01.97 200 Куры Кг 10000 Сахар 14.01.97 50 Фанта Бут. 1л. 6000 Макароны 10.01.97 1000 Макароны 11.01.97 500 Фанта 10.01.97 2000 Фанта 12.01.97 3000 Таблица «Товары» Таблица «Отпуск товаров» Рисунок 8. Нарушение целостности базы данных: запись с товаром «Рафинад» (таблица «Отпуск товаров») не имеет родительской записи И в первом, и втором случаях мы наблюдаем нарушение целостности базы дан­ных. Это означает, что хранящаяся в ней информация становится недостоверной. СУБД обычно блокирует действия, которые нарушают целостность связей между таблицами, т.е. нарушают ссылочную целостность. Когда говорят о ссылочной целостности, имеют в виду совокупность связей между отдельными таблицами во всей БД. Нарушение хотя бы одной такой связи делает информацию в БД недостоверной. Чтобы предотвратить потерю ссылочной целостности, используется механизм каскадных изменений. Он состоит в обеспечении следующих действий: • при изменении поля связи в записи родительской таблицы следует синхронно изменить значения полей связи в соответствующих записях дочерней таблицы; • при удалении записи в родительской таблице следует удалить соответствующие записи в дочерней таблице. Изменения или удаления в записях дочерней таблицы при одновременном изменении (удалении) записи родительской таблицы называются каскадными изменениями и каскадными удалениями. Существует другая разновидность каскадного удаления: при удалении родительской записи в записях дочерних таблиц значения полей связи обнуляются. Эта разно­видность применяется редко, т.к. дочерние таблицы будут содержать избыточные данные, например, сведения о товаре, которого нет на складе. Обычно для реализации ссылочной целостности в дочерней таблице создают внешний ключ, в который входят поля связи дочерней таблицы. Этот ключ для дочер­ней таблицы является первичным и поэтому по составу полей должен совпадать с первичным ключом родительской таблицы или - реже - с частью первичного ключа. Индексы По определениям ключей СУБД автоматически строит индексы, которые представляют собой механизмы быстрого доступа к хранящимся в таблицах данным. Индексы это внутренние служебные таблицы, содержащие два столбца. Первый столбец содержит значение индексируемого поля, второй- адреса всех записей, имеющих это значение в индексируемом поле. Индексная таблица отсортирована по значению индексируемого поля, что приводит к увеличению скорости поиска данных. Сущность индексов состоит в том, что они хранят отсортированные значения индексных полей (т.е. полей, по которым построен индекс) и указатель на запись в таблице. Например, пусть имеется таблица, показанная на рис. 9. С логической точки зрения ее индексы выглядят так, как показано на рис. 10. Порядковый № записи Дата прихода товара Наименование товара Количество кг 1 10.01.1997 Сахар 10 2 12.01.1997 Картофель 50 3 12.01.1997 Свекла 20 4 14.01.1997 Сахар 50 5 14.01.1997 Свекла 10 6 16.01.1997 Сливы 4 Рисунок 9. Физическая структура таблицы Индекс по дате прихода товара Индекс по наименованию товара Индекс по количеству Дата прихода № записи Наименование товара № записи Количество № записи 10.01.1997 1 Картофель 2 4 6 12.01.1997 2 Сахар 1 10 1 12.01.1997 3 Сахар 4 10 5 14.01.1997 4 Свекла 3 20 3 14.01.1997 5 Свекла 5 50 2 16.01.1997 6 Сливы 6 50 4 Рисунок 10. Логическая структура индексов Обратите внимание: значения полей в индексе («Дата прихода», «Наименование товара», «Количество») сортируются по возрастанию, что существенно ускоряет поиск нужных значений. Если, например, нужно выбрать все записи с наименованием товара «Свекла», нет нужды просматривать всю таблицу. Достаточно найти в индексе, построенном по столбцу «Наименование товара», первый указатель на запись, содержащую товар «Свекла», и считать из таблицы эту запись, а затем повторить для всех иных указателей в индексе на записи с товаром «Свекла». Поскольку значения полей отсортированы, поиск первого указателя осуществляется специальными методами быстрого поиска и реализуется значительно быстрее, чем если бы поиск по неотсортированным полям таблицы. Такой метод доступа к записям таблицы называется индексно-последовательным потому что: • поиск ведется по индексу, а не по таблице; • доступ начинается с первой строки, удовлетворяющей условию запроса или части; • строки в индексе, начиная с первой найденной записи, просматриваются последовательно. В том случае, если в условия запроса входят поля, по которым не построено индексов, ищется иной пригодный индекс, а если такого индекса нет, производится последовательный перебор записей таблицы БД. Проектирование базы данных Проектирование реляционной БД заключается главным образом в разработке структуры данных, т. е. в определении состава таблиц и связей между ними. При этом структура должна быть эффективной и обеспечивать: • быстрый доступ к данным; • отсутствие дублирования (повторения) данных; • целостность данных. Проектирование структуры данных (структуры БД) также называют логическим проектированием, или проектированием на логическом уровне. При проектировании структур данных можно выделите три основных подхода. Сбор информации об объектах решаемой задачи в рамках одной таблицы (одного отношения) и последующее разбиение ее на несколько взаимосвязанных таблиц на основе нормализации отношений. Формулирование знаний о системе (определение типов исходных данных и их взаимосвязей) и требований к обработке данных, а затем получение с помощью средств CASE схемы БД или прикладной информационной системы. Структурирование информации в результате системного анализа на основе совокупности правил и рекомендаций. Проектирование может выполняться классическим способом, когда разработчик собирает и выделяет объекты системы и их характеристики, после чего вручную приводит их к требуемой структуре данных. Кроме того, для проектирования можно использовать так называемые CASE-системы, которые автоматизируют процесс разработки не только БД, но и информационной системы в целом. Нормализация базы данных Нормализация БД - это процесс уменьшения избыточности информации в БД. Метод нормализации основан на достаточно сложной теории реляционных моделей данных. Рассмотрим основные особенности и технику нормализации, не вдаваясь в теоретическое обоснование этих вопросов. Избыточность данных и аномалии При разработке структуры БД могут возникнуть проблемы, связанные: • с избыточностью данных; • с аномалиями. Под избыточностью данных понимают дублирование данных, содержащихся в БД. При этом различают простое (неизбыточное) дублирование и избыточное дублирование данных. Избыточность данных при выполнении операций с ними приводит к различным аномалиям — нарушению целостности БД. Выделим аномалии: • удаления; • обновления; • ввода. Неизбыточное дублирование является естественным и допустимым, его примером является список телефонов (местных) сотрудников организации, показанный в табл. 1. Таблица 1. Пример неизбыточного дублирования данных Сотрудник Телефон Иванов П.Л. 123 Петров А.Ф. 123 Сидоров О.Е. 456 Кузнецова В.А. 789 Васин И.Г. 123 Три сотрудника имеют одинаковый номер телефона 123, что может быть, например, в случае, когда они находятся в одной комнате. Таким образом, номер телефона в таблице дублируется, однако для каждого сотрудника этот номер является уникальным. В случае удаления одного из дублированных значений номера телефона (удаления соответствующей строки таблицы) будет потеряна информация о фамилии сотрудника — Иванова П. Л., Петрова А. Ф. или Васина И. Г., что является аномалией удаления. При смене номера телефона в комнате его необходимо изменить для всех сотрудников, которые в ней находятся. Если для какого-либо сотрудника этого не сделать, например, для Петрова А. Ф., то возникает несоответствие данных, связанное с аномалией обновления. Аномалия ввода заключается в том, что при вводе в таблицу новой строки для ее полей могут быть введены недопустимые значения. Например, значение не входит в заданный диапазон или не задано значение поля, которое в обязательном порядке должно быть заполнено (не может быть пустым). Теперь приведем пример избыточного дублирования данных. Список телефонов дополнен номерами комнат, в которых находятся сотрудники (табл. 2). При этом номер телефона указан только для одного из сотрудников — в примере Иванова П. Л., который находится в списке первым. Вместо номеров телефон других сотрудников этой комнаты поставлен прочерк. На практике кодирование прочерка зависит от особенностей конкретной таблицы, например, можно обозначать прочерк значением Null. Таблица 2. Пример избыточного дублирования данных Сотрудник Комната Телефон Иванов П.Л. 17 123 Петров А.Ф. 17 -#- Сидоров О.Е. 22 456 Кузнецова В.А. 8 789 Васин И.Г. 17 - Однако при таком построении таблицы появляются проблемы. Номер телефона произвольного сотрудника можно получить только путем поиска в другом столбце таблицы (по номеру комнаты). При запоминании таблицы для каждой строки отводится одинаковая память вне зависимости от наличия или отсутствия прочерков. При удалении строки с данными сотрудника, для которого указан номер телефона комнаты, будет утеряна информация о номере телефона для нее сотрудников этой комнаты. Если вместо прочерков указать номер телефона, то избыточное дублирование все равно остается. От него можно избавиться, например, с помощью разбиения таблицы на две новых (табл. 3 и 4). Разбиение — это процесс деления таблицы на несколько таблиц с целью поддержания целостности данных, т. е. устранения избыточности данных и аномалий. Таблицы связаны между собой по номеру комнаты. Для получения информации о номере телефона сотрудника в первой таблицы по фамилии сотрудника нужно считать номер его комнаты, после чего из второй таблицы по номеру комнаты считывается номер телефона. Рассмотренное разбиение таблицы на две является примером нормализации отношений. При этом избыточность данных уменьшилась, однако одновременно увеличилось время доступа к ним. Для получения номера телефона сотрудника теперь необходимо работать с двумя таблицами, а не с одной, как в предыдущем случае. Таблицы 3 и 44. Список номеров сотрудников и номеров комнат и телефонов Сотрудник Комната Комната Телефон Иванов П. Л. 17 17 123 Петров А. Ф. 17 8 789 Сидоров 0. Е. 22 22 456 Кузнецова В.А. 8 Васин И.Г. 17 Приведение к нормальным формам Процесс проектирования БД с использованием метода нормальных форм является итерационным (пошаговым) и заключается в последовательном переводе по определенным правилам отношений из первой нормальной формы в нормальные формы более высокого порядка. Каждая следующая нормальная форма ограничивает определенный тип функциональных зависимостей, устраняет соответствующие аномалии при выполнении операций над отношениями БД и сохраняет свойства предшествующих нормальных форм. Выделяют следующую последовательность нормальных форм: • первая нормальная форма; • вторая нормальная форма; • третья нормальная форма; • усиленная третья нормальная форма, или нормальная форма Бойса-Кодда; • четвертая нормальная форма; • пятая нормальная форма. Проектирование начинается с определения всех объектов, информация о которых должна содержаться в БД, и выделении атрибутов (характеристик) этих объектов. Атрибуты всех объектов сводятся в одну таблицу, которая является исходной. Затем эта таблица последовательно приводится к нормальным формам в соответствии с их требованиями. На практике обычно используются три первых нормальных формы. Для примера спроектируем БД для хранения информации о футбольном чемпионате страны. Будем запоминать информацию о дате матча, игравших командах и забитых голах. Сначала объединим все данные в одну исходную таблицу, имеющую следующую структуру (поля): • дата матча; • команда хозяев; • команда гостей; • игрок, забивший гол • признак команды • время В качестве данных о команде (хозяев и гостей) укажем ее название, город и фамилию тренера, что однозначно идентифицирует любую команду. Для игрока, забившего гол, будем указывать фамилию, а для обозначения его принадлежности к той или иной команде используем признак, например, х — для команды хозяев, а г — для команды гостей. Время гола представляет число минут, прошедших с начала матча. Приведенная таблица имеет относительно простую структуру, на практике подобные таблицы содержат также данные о составах команд, арбитрах, времени начала игры, числе зрителей, нарушениях правил, заменах, предупреждениях и удалениях игроков и другую информацию. В структуре таблицы указаны только названия полей, поскольку тип и размерность полей на этом этапе большого значения не имеют. Созданную таблицу можно рассматривать как однотабличную БД. Ее главным недостатком является значительная избыточность данных. Так, для каждого игрока, забившего гол, указывается дата матча и информация о команде хозяев и гостей. Дублирование данных способно привести к аномалиям, а также к существенному увеличению размера базы. Отметим, что в таблице отсутствует информация о счете матча, т. к. ее можно получить, подсчитав голы, забитые хозяевами и гостями. Побочным явлением такого подхода является то, что если в матче не было забитых голов, то информация о соответствующем матче в таблице отсутствует. Эта ситуация исправляется автоматически при последующем разбиении исходной таблицы на таблицы матчей и голов. Первая нормальная форма Приведем исходную таблицу к первой нормальной форме, для которой должны выполняться следующие условия: • поля содержат неделимую информацию; • в таблице отсутствуют повторяющиеся группы полей. Во втором и третьем полях исходной таблицы содержится информация о названии команды, городе и тренере, например, зенит Санкт-Петербург Ю. Морозов. Это противоречит первому требованию — информация о команде, городе и тренере является делимой. Эта информация может и должна быть разделена на три отдельных поля — название команды, город, тренер. Согласно второму требованию, в таблице должны отсутствовать повторяющиеся группы полей, т. е. группы, содержащие одинаковые функциональные значения. В исходной таблице таких групп нет, поэтому второму требованию она удовлетворяет. Может показаться, что повторяющимися группами полей являются поля с информацией о командах хозяев и гостей. Несмотря на то, что состав определения налипших полей полностью совпадают, эти поля имеют различное функциональное назначение и не считаются повторяющимися Примером таблицы, имеющей повторяющиеся группы полей, может служить табл. 5 с результатами экзаменационной сессии. В этой таблице для оценок, полученных студентами на экзаменах, необходимо создать столько полей, сколько может быть различных предметов. В связи с тем, что студент сдает не псе экзамены (для них проставлены прочерки), размер записей и таблицы в целом неоправданно увеличивается. Кроме того, значительные трудности создает изменение состава предметов. Если организовать переименование предмета достаточно просто, то его удаление или добавление требует изменения структуры таблицы, что, вообще говоря, крайне нежелательно, поскольку структура таблицы обычно определяется еще на этапе проектирования БД. Таблица 5. Результаты экзаменационной сессии Студент Математика Информатика Физика История Английский язык Семенов Р. 0. 4 4 4 - 4 Костина В. К. 4 3 3 - 4 Папаев Д. Г. 5 5 5 - - Симонов П. С. - - - 4 4 Вторая и третья нормальные формы касаются отношений между ключевыми и неключевыми полями. Вторая нормальная форма Ко второй нормальной форме предъявляются следующие требования: • таблица должна удовлетворять требованиям первой нормальной формы; • любое неключевое поле должно однозначно идентифицироваться ключевыми полями. Записи таблицы, приведенной к первой нормальной форме, не являются уникальными и содержат дублированные данные. Так, если за одну минуту футболист забил несколько голов, то таблица будет содержать одинаковые записи. Чтобы обеспечить уникальность записей, введем в таблицу поле ключа — код матча. При этом значение ключа будет однозначно определять каждую запись таблицы. Тогда структура таблицы примет такой вид: • Код матча (уникальный ключ) • Дата матча • Название команды хозяев • Город команды хозяев • Фамилия тренера команды хозяев • Название команды гостей • Город команды гостей • Фамилия тренера команды гостей • Игрок • Признак команды • Время Записи этой таблицы имеют значительное избыточное дублирование данных т. к. дата матча, а также информация о командах указываются для каждого гола. Поэтому разобьем таблицу на две таблицы, одна из которых будет содержать данные о матчах, а вторая — о голах, забитых в каждом конкретном м. Структуры этих таблиц будут следующими. Поля таблицы матчей: • Код матча (уникальный ключ) • Дата матча • Название команды хозяев • Город команды хозяев • Фамилия тренера команды хозяев • Название команды гостей • Город команды гостей • Фамилия тренера команды гостей Поля таблицы голов: • Код гола (уникальный ключ) • Код матча • Игрок • Признак команды • Время Таблицы связаны по полю Код матча, которое для таблицы матча имеет уникальное значение. Чтобы обеспечить уникальность записей таблицы голов, в нее введено ключевое поле Код гола. Третья нормальная форма Требованиями третьей нормальной формы являются следующие: таблица должна удовлетворять требованиям второй нормальной формы; ни одно из неключевых полей не должно однозначно идентифицироваться значением другого неключевого поля (полей). Приведение таблиц, к третьей нормальной форме предполагает выделение в отдельную таблицу (таблицы) тех полей, которые не зависят от ключа. В таблице матчей такими являются поля с фамилиями тренеров команд которые одно­значно определяются значениями названия и города команды. (Предполагается, что в течение сезона у команды тренер не меняется, что на практике часто не выполняется, но не меняет сути рассматриваемого вопроса.) Разобьем табли­цу матчей на две таблицы: одну с данными о матчах и вторую - с данными о командах. Их структура будет такой: Таблица матчей: • Код матча (уникальный ключ) • Дата матча • Код команды хозяев • Код команды гостей Таблица команд: • Код команды (уникальный ключ) • Название команды • Город • Фамилия тренера Информация о команде хранится в строковых полях, которые имеют достаточно большой размер - в нашем случае приблизительно 60 символов. На практике о каждой команде необходимо запоминать больше данных, чем просто ее название, город приписки и фамилия тренера, что приводит к существенному объему информации. Поэтому для уменьшения размера записей таблицы матчей не только фамилия тренера, но и вся подробная информация о командах вынесена в таблицу команд. Вместо этого команды хозяев и гостей в таблице матчей идентифицируются кодом команды, который является уникальным ключевым значением в таблице команд. После приведения к третьей нормальной форме база данных с информацией о футбольном чемпионате страны будет иметь структуру, показанную на рис. 11, где кроме описания таблиц обозначены также и связи между ними. Следование требованиям нормализованных форм не всегда является обязательным. Как уже отмечалось, с ростом числа таблиц структура БД усложняется и возрастает время доступа к данным. В ряде случаев для упрощения структуры БД можно позволить частичное дублирование данных, не допуская, однако, нарушения их целостности и сохраняя их непротиворечивость. В рассмотренной БД это относится, например, к информации о счете матча. Чтобы исключить дублирование данных, для него не отведено отдельное поле Узнать счет матча можно с помощью двух запросов к БД, возвращающих число мячей, забитых в указанном матче хозяевами и гостями, соответственно Запрос может иметь следующий вид: SELECT CQUNT(G_OwnerSign) FROM Goal WHERE Goal.G_Match = :MatchCode AND Goal.G_OwnerSign = :OwnerSign Номер матча и признак команды, игрок которой забил гол, передаются в запрос через параметры MatchCode и Ownersign. В связи с усложнением процесса обработки данных и времени доступа к ним возможным вариантом для структуры рассмотренной БД является такой, когда счет матча запоминается в отдельном поле таблицы матчей. При этом необхо­димо обеспечить целостность данных при изменении БД с учетом того факта, что часть ее данных дублирована. Средства CASE В предыдущем разделе проектирование БД выполнено вручную. Разработчик сам осуществляет такие операции, как определение состава полей, распределение их по таблицам, а также установление связей между таблицами. Ручное проектирование применяется для разработки БД самого различного назначения и для относительно небольших БД вполне приемлемо. Однако с ростом размера базы данных, когда в нее включаются нескольких десятков и сотен различных таблиц, возникает проблема сложности организации данных, в том числе установления взаимосвязей между таблицами. Для облегчения решения этой проблемы предназначены системы автоматизации разработки приложений, или средства CASE (Computer Aided Software Engineering). Средства CASE представляют собой программы, поддерживающие процессы создания и/или сопровождения информационных систем, такие как анализ и формулировка требований, проектирование БД и приложений, генерация кода, тестирование, обеспечение качества, управление конфигурацией и проектом. То есть средства CASE позволяют решать более масштабные задачи, чем просто проектирование БД. Систему CASE можно определить как набор средств CASE, имеющих определенное функциональное предназначение и выполненных в рамках единого программного продукта. Классификация средств (систем) CASE, используемых для разработки баз данных, производится по следующим признакам: • ориентация на этапы жизненного цикла; • функциональная полнота; • тип используемой модели; • степень независимости от СУБД; • платформа. По ориентации на этапы жизненного цикла можно выделить следующие основные типы систем CASE (в скобках приведены названия фирм-разработчиков): • системы анализа, предназначенные для построения и анализа моделей предметной области, например, Design/IDEF (Meta Software) и BPWin (Logic Works); • системы анализа и проектирования, поддерживающие и обеспечивающие создание проектных спецификаций, например, Vantage Team Builder (Cayenne), Silverrun (Silverrun Technologies), PRO-1 (McDonnell Douglas); • системы проектирования БД, обеспечивающие моделирование данных и разработку схем баз данных для основных СУБД, например, ERwin (Logic Works), SDesigner (SPD), DataBase Designer (ORACLE); • системы разработки приложений, например, Uniface (Compuware), JAM (JYACC), PowerBuilder (Sybase), Developer/2000 (ORACLE), New Era (Informix), SQL Windows (Centura), Delphi (Borland). По функциональной полноте системы CASE условно делятся на следующие группы: • системы, предназначенные для решения частных задач на одном или нескольких этапах жизненного цикла, например, ERwin (Logic Works), S-Designer (SPD), CASE.AnanHTHK (МакроПроджект) и Silverrun (Silverrun Technologies); • интегрированные системы, поддерживающие весь жизненный цикл информационной системы и связанные с общим репозиторием, например, система Vantage Team Builder (Cayenne) и система Dcsigner/2000 с системой разработки приложений Developer/2000 (ORACLE). В зависимости от типа используемых моделей существует три разновидности систем CASE: • структурные, • объектно-ориентированные, • комбинированные. Исторически первыми появились структурные системы CASE, которые основываются на методах структурного и модульного программирования, структурного анализа и синтеза, например, Vantage Team Builder (Cayenne). Объектно-ориентированные системы CASE получили массовое распространение с начала 90-х годов. Они позволяют сократить сроки разработки, а также повысить надежность и эффективность функционирования информационной системы. Примерами объектно-ориентированных систем CASE являются Ration, il Rose (Rational Software) и Object Team (Cayenne). Комбинированные системы CASE поддерживают одновременно и структурное, и объектно-ориентированное программирование, например, Designer/2000 (ORACLE). По степени независимости от СУБД системы CASE делятся на две группы: • независимые системы; • встроенные в СУБД системы. Независимые системы CASE поставляются в виде автономных систем, не входящих в состав конкретной СУБД. Обычно они поддерживают несколько форматов баз данных через интерфейс ODBC. К числу независимых относятся SDesigner (SDP, Powersoft), ERwin (LogicWorks), Silverrun (Silverrun Technologies). Встроенные системы CASE обычно поддерживают главным образом формат базы данных, в состав системы управления которой они входят. При этом возможна поддержка форматов и других баз данных. Примером встроенной системы является система Designer/2000, входящая в состав СУБД ORACLE. Платформа определяет компьютер и операционную систему, на которых допускается использовать продукт, созданный с помощью данной системы CASE. Перечислим средства CASE, которые можно применять при разработке БД и приложений с помощью Delphi. Data Module Designer — позволяет проектировать БД с таблицами формата Paradox. Программа обеспечивает достаточно удобный и наглядный интерфейс. Структура БД, в том числе связи между таблицами, отображается в графическом виде. Cadet — независимый продукт, позволяющий проектировать БД с таблицами форматов dBase, Paradox и InterBase. С учетом того, что указанные форматы являются родными для Delphi, программу Cadet удобно использовать при разработке информационных систем. Обе программы - Data Module Designer и Cadet — предназначены для моделирования структур данных и автоматизации проектирования БД. Возможности, предоставляемые этими средствами, меньше, чем возможности таких мощных систем, как, например, Sdesigner, однако доступность делает их тем не менее весьма привлекательными для использования. Так, программа Cadet является условно бесплатной, a Data Module Designer входит в состав СУБД Paradox 7.0. Отметим, что цена мощных систем CASE достаточна высока и доходит до сотен тысяч долларов. Занятие 1. Базы данных и конструктор таблиц Microsoft Access XP — профессиональная программа управления базами данных. С ее помощью можно накапливать и систематизировать разнообразную информацию, искать и сортировать объекты согласно выбранным критериям, конструировать удобные формы для ввода данных и генерировать на основании имеющихся записей прекрасно оформленные отчеты. MS Access обеспечивает одновременный доступ к данным десяткам пользователей. Базы данных ТЕМА ЗАНЯТИЯ На этом занятии вы познакомитесь со структурой баз данных, научитесь конструировать и изменять таблицы данных — основные хранилища информации в MS Access. В упражнениях занятия рассматриваются следующие темы: • мастер таблиц; • конструктор таблиц; • типы данных; • список подстановки; • связь таблиц; • значение по умолчанию; • ограничение на значение поля; • индекс. Базы данных — это совокупность структур, предназначенных для хранения больших объемов информации и программных модулей, осуществляющих управление данными, их выборку, сортировку и другие подобные действия. Информация базы данных хранится в одной или нескольких таблицах. Любая таблица с данными состоит из набора однотипных записей, расположенных друг за другом. Они представляют собой строки таблицы, которые можно добавлять, удалять или изменять. Каждая запись является набором именованных полей, или ячеек, которые могут хранить самую разнообразную информацию, начиная от даты рождения и заканчивая подробным описанием кулинарного рецепта. Однотипные поля разных записей образуют столбец таблицы. Записи одной таблицы могут содержать ссылки на данные другой таблицы, например, в таблице со списком товаров могут храниться ссылки на справочник производителей товаров с их адресами и другими реквизитами. При этом записи, касающиеся разных товаров, могут указывать на одного и того же производителя. Такое взаимодействие таблиц называется связью. Другие модули базы данных предназначены для обработки информации, хранящейся в таблицах. С помощью запросов производится выборка данных, отвечающих определенным условиям. Формы предназначены для форматированного ввода и восприятия информации. Отчеты обеспечивают вывод (как правило, на принтер) красочно оформленного списка записей с заголовками, пунктами и подпунктами Упражнение 1. Создание таблиц с помощью мастера таблиц С помощью мастера таблиц можно создавать некоторые часто встречающиеся структуры данных. Прежде чем заново строить структуру таблицы, подумайте, нельзя ли воспользоваться одним из стандартных шаблонов мастера таблиц. Это позволит сэкономить время. Например, чтобы создать таблицу данных, предназначенную для хранения списка людей, с которыми вы контактируете, выполните следующие действия. 1.  Запустите Microsoft Access, выполнив соответствующую команду меню Пуск > Программы (Start > Programs). 2.  В появившемся в области задач приложения окне Создание файл (New File) в разделе Создание (New) щелкните на пункте Новая база данных (Blank Access Database) (рис. 12). Откроется окно диалога Файл новой базы данных (File New Database). 1. Выберите для размещения новой базы данных папку Мои документы (My Documents). В поле Имя файла (File Name) введите имя Контакты. Затем щелкните па кнопке Создать (Create). Рисунок 12. Окно Microsoft Access Примечание После своего создания файл базы данных обновляется автоматически при закрытии и выполнении некоторых критических операций (например, изменении структуры таблицы). Нет необходимости вручную сохранять файл, хотя при долгой работе это следует делать во избежание потерь данных из-за сбоя компьютера. Откроется окно базы данных, показанное на рис. 13. Оно является основным окном базы и позволяет открывать, добавлять и удалять любые объекты базы данных. В левой области окна находятся кнопки групп объектов. В его правой части расположен список объектов выбранного типа, имеющихся в текущей базе данных, а также значки команд, позволяющих создавать новые объекты данного типа. Пока что в этой базе данных нет ни одного объекта. Рисунок 13. Окно базы данных 1.  Щелкните на кнопке Таблицы (Tables). 2.  Чтобы запустить мастер создания таблиц, дважды щелкните на значке Создание таблицы с помощью мастера (Create Table By Using Wizard). В первом окне мастера, показанном на рис. 14, нужно указать подходящую структуру и выбрать в ней необходимый набор полей. 1. Выберите положение переключателя Деловые (Business). 2. В списке Образцы таблиц (Sample Tables) выберите пункт Контакты (Contacts). 3. Щелкните на пункте Имя (FirstName) списка Образцы полей (Sample Fields). 4.  Щелкните на кнопке со стрелкой вправо, чтобы добавить выбранное поле в структуру создаваемой таблицы. 5.  Повторяя шаги 8 и 9, добавьте в список Поля новой таблицы (Fields In My New Table) поля Фамилия (LastName), Адрес (Address), НазваниеКомпании (Company Name) и РабочийТелефон (WorkPhone). 6.  Щелкните на кнопке Далее (Next). 7. В поле второго окна мастера введите слово Контакты, которое будет играть роль имени таблицы. Затем щелкните на кнопке Далее. 1.  В третьем окне мастера щелкните на кнопке Готово (Finish). Мастер сгенерирует таблицу и откроет ее в режиме ввода данных. Обратите внимание: поскольку во втором окне осталось выбранным положение переключателя Microsoft Access автоматически определяет ключ (Yes, Set A Primary Key For Me), MS Access добавляет в таблицу дополнительное ключевое поле Код_Контакты (КонтактыID), которое играет роль уникального идентификатора записей. Рисунок 14. Мастер создания таблиц Упражнение 2. Заполнение таблицы Таблица данных похожа на лист электронной таблицы, но ее размеры ограничены. Число ее строк на единицу больше количества записей (последняя пустая строка предназначена для ввода новой записи), а число столбцов равно количеству полей в записи. Заголовки столбцов таблицы соответствуют именам полей, сформированных с помощью мастера создания таблиц. 1.  Щелкните на поле Имя и введите какое-нибудь имя. MS Access автоматически добавит в таблицу, созданную на предыдущем упражнении, первую запись, а строка новой записи сместится вниз. 2. Введите фамилию в поле Фамилия. 3.  Заполните все остальные поля первой записи. 4.  Щелкните на поле Имя последней строки и введите имя еще одного человека, его фамилию, адрес, название компании и телефон. Добавьте подобным образом несколько записей, чтобы таблица стала такой, как показано на рис. 15. Если вы ошиблись при вводе, просто щелкните на ячейке с неверными данными и отредактируйте их. Стандартные заголовки столбцов часто оказываются неудобными, поэтому MS Access позволяет заменять их. Рисунок 15. Таблица данных 1.  Щелкните на заголовке Фамилия правой кнопкой мыши и выберите команду Переименовать (Rename). 2.  Введите слово Название, которое заменит подсвеченный заголовок Фамилия. 3.  Повторив шаги 5 и 6, верните столбцу название Фамилия. 4. Щелчком на кнопке Первая запись (First Record) переместите указатель теку щей записи в начало таблицы. 5. Два раза щелкните на кнопке Следующая запись (Next Record), чтобы перейти к четвертой записи. 6. Щелкните на кнопке Удалить запись (Delete Record) панели инструментов. 7.  В появившемся окне запроса, информирующем о невозможности отмены операции удаления записи, щелчком на кнопке Да (Yes) подтвердите необходимость удаления. Обратите внимание, что ключевое поле Код_Контакты автоматически заполняется некоторыми числами. Эти числа не обязательно образуют непрерывную последовательность. Единственным условием является их уникальность. Содержимое ключевого поля различно для всех записей таблицы, что позволяет однозначно идентифицировать запись. Такие поля используются для формирования связей таблиц, о которых вы узнаете на этом занятии позже. Конструктор таблиц Конструктор таблиц предназначен для задания и изменения структуры таблицы. С помощью конструктора можно формировать сколь угодно сложные таблицы с полями любого типа. Упражнение 3. Создание таблицы в режиме конструктора В упражнении 1 была создана таблица для хранения данных о людях, с которыми вы контактируете. Теперь давайте с помощью конструктора добавим в базу данных еще одну таблицу, предназначенную для записи всех случаев контакта с людьми из первой таблицы. 1.  Щелчком на кнопке Окно базы данных (Database Window) переключитесь на список таблиц базы данных. 2. Дважды щелкните на значке Создание таблицы в режиме конструктора (Create Table In Design View). Окно конструктора, показанное на рис. 16, состоит из двух частей. В верхней отображается список полей таблицы, а в нижней — свойства выделенного поля. 3.  Введите слово Дата, которое будет играть роль названия нового поля. 4. Щелкните на ячейке Тип данных (Data Type) первой строки списка полей. Здесь необходимо выбрать тип поля, который определяет структуру и размер данных. 5. Щелкните на появившейся стрелке раскрывающегося списка ячейки Тип данных и выберите пункт Дата/время (Date/Time). 6.  Нажмите клавишу Tab и введите расшифровку назначения поля: Дата контакта. Такие описания вводить не обязательно, но их наличие помогает разобраться в структуре таблицы. 7.  Щелкните на второй ячейке столбца Имя поля (Field Name) и введите имя Описание. 8. Задайте для второго поля тип Текстовый (Text). 9. Укажите назначение поля в соответствии с рис. 16. Рисунок 16. Конструктор таблиц Кроме даты и описания контакта таблица данных должна содержать информацию о том, с кем вы контактировали. Но список людей хранится в таблице Контакты, поэтому в новой таблице не нужно заново вводить имя и фамилию. Достаточно лишь добавить ссылку на одну из записей таблицы Контакты. Эта ссылка будет иметь числовой тип и содержать число, равное значению поля Код_Контакты соответствующей записи таблицы Контакты. 1.  Добавьте еще одно поле с именем Код_Контакты и типом Числовой (Number), Теперь нужно добавить индексированное поле и назначить его ключом, однозначно идентифицирующим записи таблицы. Индексированные поля, или индексы, отличаются от обычных тем, что для них MS Access создает специальные списки, позволяющие выполнять быструю сортировку и поиск по содержимому индексированного поля. Таблица может содержать несколько индексов. Ключ — это специальный индекс, идентифицирующий записи. Значения такого поля должны быть уникальными. Его наличие не обязательно, но оно так часто используется, что при отсутствии ключа MS Access напоминает об этом и предлагает автоматически добавить ключевое поле. 1. Добавьте поле Ключ и назначьте ему тип Счетчик (AutoNumber). Такой тип данных избавляет пользователя от необходимости ввода значений ключевого поля и инициирует автоматическую генерацию уникальных значений. 2.  Сохраняя активной строку Ключ, щелкните на кнопке Ключевое поле (Primary Key). На кнопке этой строки появится значок ключа, показывающий особый статус поля. 3.  Щелкните на кнопке закрытия окна конструктора. 4.  MS Access спросит о необходимости сохранения структуры таблицы. В ответ щелкните на кнопке Да. 5.  В открывшемся окне диалога (рис. 17) введите имя Список. 6.  Щелкните на кнопке ОК. Рисунок 17. Ввод имени таблицы Упражнение 4. Связывание таблиц с использованием списка подстановки Пришло время наполнить таблицу Список конкретной информацией. Для этого выполните следующие шаги. 1. В окне базы данных откройте список таблиц и дважды щелкните на значке Список. Выбранная таблица откроется в режиме просмотра данных. 2.  Добавьте несколько записей в соответствии с рис. 18. Рисунок 18. Список состоявшихся контактов Примечание Чтобы расширить столбец Описание, содержащий длинные текстовые строки, поместите указатель мыши на его правую границу и, когда значок сменится на двунаправленную стрелку, перетащите границу вправо. Не забывайте, что числа в столбце Код_Контакты должны соответствовать номерам записей таблицы Контакты, описывающих людей, с которыми вы контактировали. Согласитесь, что искать соответствующие номера очень неудобно. MS Access позволяет автоматизировать эту операцию с помощью списка подстановки, который настраивается следующим образом. 1.  Щелкните на кнопке Вид (View) панели инструментов. Откроется окно конструктора таблицы. 2.  В списке типа данных для поля Код_Контакты выберите пункт Мастер подстановок (Lookup Wizard). 3.  В первом окне мастера оставьте выбранным положение переключателя Объект «столбец подстановки» будет использовать значения из таблицы или запроса (I Want The Lookup Column To Look Up The Values In The Table Or Query) и щелкните на кнопке Далее. Три положения переключателя второго окна мастера выводят на экран список таблиц, запросов или объединяют эти два списка. Здесь нужно выбрать таблицу или запрос на роль источника подстановки. В нашем случае база данных кроме таблицы Список содержит только один объект — таблицу Контакты, которая и окажется выбранной в окне мастера. 4.  Щелкните на кнопке Далее. Третье окно мастера, показанное на рис. 19, предлагает выбрать из таблицы Контакты поля, участвующие в подстановке. Этот список обязан содержать то поле, значения которого должны помещаться в поле Код_Контакты, то есть одноименное поле таблицы Контакты. 5.  Щелкните на пункте Код_Контакты. 1.  Щелкните на кнопке со стрелкой вправо. В список источника подстановки следует также добавить те поля, содержимое которых должно отображаться вместо численного значения, помещаемого из поля Код_Контакты таблицы Контакты в поле Код_Контакты таблицы Список. Удобнее выбирать людей по их именам и фамилиям. 2.  Добавьте в список Выбранные поля (Selected Fields) пункты Фамилия и Имя. 3.  Затем щелкните на кнопке Далее. 4.  В следующем окне мастер продемонстрирует два столбца таблицы-источника, скрыв столбец Код_Контакты, содержащий идентификатор записи. Щелкните на кнопке Далее. 5.  В поле последнего столбца введите название Контакт, которое заменит имя столбца Код_Контакты, и щелкните на кнопке Готово. 6.  В появившемся окне диалога щелчком на кнопке Да подтвердите необходимость сохранения построенной структуры. Примечание Параметры списка подстановки появятся на вкладке Подстановка (Lookup), показанной на рис. 20. Если вы знакомы с языком структурированных запросов SQL, то сможете разобраться в запросе Источник строк (Row Source), который формирует таблицу подстановки. Рисунок 20. Параметры подстановки 1. Щелкните на кнопке Вид, чтобы снова переключиться в режим ввода данных. Теперь в третьем столбце таблицы вместо чисел видны фамилии, соответствующие этим числам, хотя само значение поля Код_Контакты не изменилось. MS Access автоматически ищет соответствующую запись таблицы Контакты по числу, содержащемуся в поле Код_Контакты, и выводит в ячейку текст поля Фамилия — второго поля таблицы Контакты из выбранных в окне мастера подстановки. Давайте попробуем добавить в таблицу Список еще одну запись. 1.  Щелкните на нижней ячейке столбца Дата и введите дату 10.10.03. 2.  Нажмите клавишу Tab и напечатайте текст Письмо с предложениями о сотрудничестве. 3.  Снова нажмите клавишу Tab и щелкните на появившейся кнопке раскрывающегося списка, как показано на рис. 21. Теперь вы можете выбирать людей из списка подстановки, сформированного на базе полей Фамилия и Имя таблицы Контакты. 4.  Щелкните на втором пункте списка. MS Access автоматически отыщет в таблице Контакты значение поля Код_Контакты для записи, содержащей информацию о Петре Петрове, и введет найденное число в поле Код_Контакты таблицы Список. Это значение останется скрытым. Вместо него в ячейке таблицы Список появится соответствующая фамилия, взятая из таблицы Контакты.   INCLUDEPICTURE "../../../../Documents%20and%20Settings/teacher220/Application%20Data/Microsoft/500%20учебников/Autorun/Info/Office/OfficeXP/16/16.10.gif" \* MERGEFORMATINET Рисунок 21. Список подстановки Упражнение 5. Связывание таблиц и обеспечение целостности данных В предыдущем упражнении с помощью мастера подстановок была сформирована связь между двумя таблицами. Такая связь позволяет установить правила взаимодействия между таблицами. Список подстановок можно было организовать и без связи. Но представьте, что вы удалите из таблицы Контакты запись, на которую ссылаются некоторые записи таблицы Список. В такой ситуации останутся описания контактов, фамилии людей в которых потеряны. Наличие правильно организованной связи позволяет избежать подобных неприятностей.  1. Выберите команду Сервис > Схема данных (Tools > Relationships). Откроется окно, схематически изображающее связи базы данных. На рис. 22 видно, что линия связи соединяет поля Код_Контакты таблицы Контакты и Контакт таблицы Список. Примечание Данная связь соединяет ключевое и обычное поля. Это связь типа «один ко многим», когда одной записи таблицы Контакты может соответствовать несколько записей таблицы Список, но не наоборот. Рисунок 22. Схема данных Для тренировки давайте удалим связь, а затем создадим ее заново. 1.  Щелчком выделите линию связи. 2.  Нажмите клавишу Delete. В ответ на появившийся запрос щелкните на кнопке Да. Связь исчезнет. 3.  Поместите указатель мыши на пункт Код_Контакты таблицы Контакты. 4.  Нажмите кнопку мыши и перетащите указатель на поле Контакт таблицы Список. 5.  В открывшемся окне диалога щелкните на кнопке Создать. Связь появится вновь. Чтобы настроить параметры связи, выполните следующие шаги. 1.  Дважды щелкните на линии связи. Откроется окно диалога, показанное на рис. 23. Примечание Если таблицы, между которыми нужно организовать связь, отсутствуют, щелкните в окне Схема данных (Relationships) правой кнопкой мыши и выберите команду Добавить таблицу (Show Table). В открывшемся окне диалога выделяйте таблицы, которые нужно разместить в окне Схема данных и щелкайте на кнопке Добавить (Add). Получив все необходимые таблицы, закройте окно диалога кнопкой Закрыть (Close). Рисунок 23. Параметры связи 1.  Установите флажок Обеспечение целостности данных (Enforce Referential Integrity). Этот режим не позволит MS Access оставлять в таблице Список записи, для которых нельзя подобрать запись таблицы Контакты с подходящим значением поля Код_Контакты. 2.  Установите флажок Каскадное удаление связанных записей (Cascade Delete Related Records). Теперь при удалении записи таблицы Контакты (то есть удалении информации о конкретном человеке) будут удалены все соответствующие записи таблицы Список (то есть все описания контактов с этим человеком). Если указанный флажок сброшен, удаление тех записей таблицы Контакты, на которые ссылается хотя бы одна запись таблицы Список, запрещено. Примечание Установка флажка Каскадное обновление связанных полей приведет к тому, что при обновлении поля Код_Контакты таблицы Контакты будут автоматически обновляться одноименные поля в соответствующих записях таблицы Список. 1.  Щелкните на кнопке ОК. 2.  Закройте окно Схема данных. 3.  В окне базы данных дважды щелкните на значке таблицы Контакты. В открывшемся окне таблицы видны небольшие знаки «плюс», расположенные в левой части записей. Их присутствие говорит о наличии связи ключевого поля таблицы с другой таблицей. 4. Щелкните на знаке «плюс» записи для Петра Петрова. Откроется вложенная таблица, содержащая те записи таблицы Список, значение поля Код_Контакты которых равно величине одноименного поля записи для Петра Петрова (рис. 24). Рисунок 24. Выборка данных из связанной таблицы Примечание При раскрытии фрагмента связанной таблицы знак «плюс» превращается в знак «минус». Чтобы скрыть дополнительную таблицу, щелкните на нем еще раз. 1.  Щелкните еще на каком-нибудь знаке «плюс», и вы увидите описание всех контактов с еще одним человеком. Таким образом, с помощью созданной связи вы можете быстро просматривать в окне таблицы Список описания всех контактов с определенным человеком или группой лиц. Упражнение 6. Установка параметров полей Для того, чтобы обеспечить возможность хранения в базе данных разнообразной информации, MS Access предлагает большой набор типов данных, перечисленных в табл. 6. Чтобы сделать работу со списком контактов более продуктивной, следует изменить типы данных некоторых полей. 1. В окне базы данных выделите таблицу Список и щелкните на кнопке Конструктор (Design), расположенной в верхней части окна. Выбранная таблица откроется в режиме конструктора. 2.  Описание контакта может быть достаточно длинным, а максимальная длина текстовых полей ограничена 256 символами. Поэтому измените тип поля Описание на Поле MEMO. 3.  Щелкните на поле Дата. Рисунок 25. Выбор формата 1.  Для удобства прочтения даты контакта измените формат ее отображения, выбрав в раскрывающемся списке Формат поля (Format) пункт Длинный формат даты (Long Date), как показано на рис. 25. Таблица 6. Типы данных Название типа Назначение Текстовый (Text) Текст длиной до 255 символов Поле MEMO (Memo) Текст длиной до 65 000 символов Числовой (Number) Числа различных форматов Дата/время (Date/Time) Дата и/или время Денежный (Currency) Денежные значения различных форматов Счетчик (AutoNumber) Счетчик, который автоматически увеличивается на единицу с добавлением каждой новой записи Логический (Yes/No) Величины, способные принимать только два значения: да/нет или 1/0 Поле объекта OLE (OLE Object) Поля, позволяющие вставлять рисунки, звуки и данные других типов Гиперссылка (Hyperlink) Ссылки, дающие возможность открывать объект MS Access (таблицу, форму, запрос и т. п.), файл другого приложения или web-страницу Кроме типа данных и формата отображения вкладка Общие (General) окна конструктора позволяет настроить некоторые дополнительные параметры поля, влияющие на способ ввода данных. Параметр Значение по умолчанию (Default Value) задает содержимое поля, которое записывается в новую запись в том случае, если пользователь не ввел в это поле никаких данных. Если вы регулярно обновляете список контактов, то дата контакта чаще всего будет совпадать с текущей датой. Чтобы упростить ввод информации, можно задать текущую дату в качестве значения по умолчанию для поля Дата. 1.  Введите в поле Значение по умолчанию (Default Value) функцию Date(), которая возвращает текущую дату. В некоторых случаях требуется не только задать тип данных поля, но и ограничить его возможные значения некоторым интервалом или более сложным правилом отбора. Такое правило вводится в поле Условие на значение (Validation Rule). Предположим, вы точно знаете, что все контакты, которые будут заноситься в список, состоятся после 1 января 2003 г. Чтобы избежать ошибок, можно заставить MS Access сообщать вам о вводе неверной даты. Для этого выполните следующие шаги. 2. В поле Условие на значение щелкните на кнопке построителя выражений. Откроется окно диалога. Для задания условий или значений определенных параметров MS Access предлагает большой набор математических операций и функций. Построитель выражений помогает ориентироваться во всем многообразии функций MS Access. Верхнее прокручивающееся поле построителя содержит конструируемое выражение. Его можно изменять как путем непосредственного редактирования текста, так и с помощью других кнопок и списков построителя. 3. Необходимо, чтобы дата записи была позднее, то есть больше, даты 1 января 2003 г. Поэтому щелкните на кнопке >, чтобы добавить нужное условие. Теперь нужно указать дату, с которой выполняется сравнение. Для этого следует воспользоваться специальной функцией, преобразующей строковую константу в дату в формате Access. 1.  Дважды щелкните на папке Функции (Functions) в левом списке построителя выражений. 2.  Щелкните на папке Встроенные функции (Built-in Function), чтобы вывести список стандартных функций MS Access. 3.  В среднем списке выберите категорию Дата/время. 4.  Двойным щелчком на имени функции DateValue добавьте ее в поле выражения. Вместо местозаполнителя аргумента «stringexpr» нужно ввести конкретную дату в строковом формате. 5.  В поле выражения щелчком выделите текст «stringexpr», а затем напечатайте "1.1.2003". 6.  Щелкните на кнопке ОК. 7. В поле Сообщение об ошибке (Validation Text) введите текст Неверная дата, который будет появляться при вводе даты, более ранней, чем 1 января 2003 г. Итоговый набор параметров поля Дата показан на рис. 26. 8.  Щелкните на кнопке закрытия окна конструктора. В окне запроса на обновление структуры таблицы щелкните на кнопке Да. По причине добавления условий на значения поля Дата MS Access запросит о необходимости проверки содержимого этого поля в уже имеющихся записях таблицы. Щелкните на кнопке Нет (No), чтобы не выполнять проверку. Рисунок 26. Параметры поля Дата Давайте изучим влияние настроенных параметров на поведение базы данных. 8466.  Выделите таблицу Список в окне базы данных и щелкните на кнопке Открыть (Open). Теперь все даты отображаются в длинном формате, а в ячейке Дата в строке новой записи автоматически появляется текущая дата. 8467.  Щелкните в ячейке Дата второй строки, измените ее содержимое на 19.11.2003 и нажмите клавишу Tab, чтобы перейти к следующему полю. Так как модифицированная дата предшествует 1 января 2003 г., что противоречит настроенному условию, MS Access не позволит убрать курсор из текущей ячейки и откроет окно сообщения  с текстом, введенным вами в поле Сообщение об ошибке конструктора таблицы на шаге 14. Такое сообщение будет появляться до тех пор, пока вы не скорректируете дату. 1.  Щелкните на кнопке ОК. 2.  Исправьте дату на 19.11.03. 3.  Закройте таблицу Список. Упражнение 7. Индексирование полей Для ускорения поиска данных о конкретном человеке в таблице Контакты удобно рассортировать записи по алфавиту. Чтобы назначить сортировку по полю Фамилия, выполните следующие шаги. 1.  В окне базы данных щелкните на значке таблицы Контакты. 2.  Щелчком на кнопке Открыть откройте таблицу в режиме редактирования. 3.  В окне таблицы щелкните на заголовке Фамилия, чтобы этот столбец был выделен. 4. Щелчком на кнопке Сортировка по возрастанию (Sort Ascending) упорядочите записи столбца Фамилии таблицы по алфавиту (рис. 27). Рисунок 27. Сортировка фамилий по алфавиту 5.  Вставьте в таблицу еще несколько человек с фамилией Петров. Например, Василий Петров, Николай Петров и Игорь Петров. Добавляйте записи в указанном порядке. 6.  Чтобы обновить сортировку, закройте окно таблицы Контакты и вновь откройте ее. Записи окажутся расположенными так, как показано на рис. 28. Фамилии упорядочены по алфавиту, но имена людей расставлены в беспорядке. Если бы в таблице было несколько сотен людей с фамилией Петров, то поиск нужного человека существенно бы осложнился. Чтобы выполнить сортировку сразу по нескольким полям, следует воспользоваться сложным индексом. Его настройка выполняется посредством следующих действий. Рисунок 28. Несколько человек с одинаковыми фамилиями 1.  Щелчком на кнопке Вид переключитесь в режим конструктора. 2.  Чтобы открыть окно индексов таблицы Контакты, показанное на рис. 29, щелкните на кнопке Индексы (Indexes) панели инструментов. В окне индексов есть список со столбцами названия индекса, имени поля, по которому выполняется индексация, и режима сортировки (по возрастанию или по убыванию). Мастер таблиц при создании таблицы Контакты индексировал ее по трем полям: Название Компании, Фамилия, Код_Контакты, добавив индексы с именами соответственно: Название Компании, Фамилия и PrimaryKey. Последний из трех индексов является ключом таблицы, однозначно идентифицирующим ее записи. Все имеющиеся индексы простые, то есть назначают сортировку только по одному полю. Чтобы записи с одинаковыми фамилиями располагались по алфавиту имен, следует модифицировать индекс Фамилия, добавив в него условие сортировки по полю Имя. Для этого выполните следующие шаги. 1.  Щелкните правой кнопкой мыши на той строке, которая расположена ниже строки индекса Фамилия. 2.  Выберите в контекстном меню команду Добавить строки (Insert Rows), чтобы вставить одну пустую строку. 3.  В новой строке щелкните на кнопке раскрывающегося списка ячейки Имя поля. 4. Выберите пункт Имя. В столбце Порядок сортировки (Sort Order) автоматически появится вариант По возрастанию (Ascending). Рисунок 29. Несколько человек с одинаковыми фамилиями В окне настройки индексов имена полей, по которым происходит упорядочивание в данном индексе, располагаются в строке с именем индекса и ниже ее в строках с пустой ячейкой Индекс (Index Name) в порядке убывания влияния поля. То есть первой выполняется сортировка по тому полю, которое в данном индексе расположено выше. В одном индексе можно использовать до 10 полей. Таким образом, теперь индекс Фамилия упорядочивает записи по алфавиту сначала по фамилиям, а потом по именам. Рисунок 30. Сортировка по двум полям 1.  Закройте окно индексов. 2.  Щелкните на кнопке Вид, чтобы переключиться в режим редактирования таблицы. 3.  Ответьте Да на запрос о необходимости сохранения таблицы. Теперь имена оказались выстроенными по алфавиту, как показано на рис. 30. Поскольку перед этим уже была настроена сортировка по полю Фамилия (то есть по индексу Фамилия, главным полем которого является Фамилия), вам даже не пришлось перенастраивать сортировку. Модификация индекса автоматически привела к упорядочению имен. Контрольное упражнение Пришло время самостоятельно попрактиковаться в работе с конструктором таблиц. MS Access автоматически обновляет файл базы данных. Чтобы сохранить его исходный вариант, в Проводнике создайте копии файлов Контакты.mdb и Список.mdb — соответственно файлы Контакты_копия.mbd и Список_копия.mbd. Закончив контрольное упражнение, переименуйте файлы Контакты_копия.mbd и Список_копия.mbd обратно в Контакты.mdb и Список.mdb. 1.  Откройте таблицу Контакты в режиме конструктора. 2.  Измените тип данных поля Адрес на Поле MEMO. 3.  Ограничьте длину текстового поля Имя величиной 30 символов. ◦ Какой параметр определяет длину поля? 4.  Поле Фамилия является ключевым. Запретите MS Access добавлять в таблицу Контакты записи с пустым полем Фамилия. ◦  Как запретить добавление пустого поля? 1.  Закройте конструктор и сохраните структуру таблицы. 2. Создайте новую таблицу с именем Метод. 3.  В конструкторе добавьте в нее поле Метод ID с типом данных Счетчик и поле Вариант с типом Текстовый. Первое из них сделайте ключом таблицы. 4.  Переключитесь в режим редактирования и введите в таблицу три записи, в поле Метод которых введите слова Телефон, Письмо, Факс. Эта таблица будет хранить возможные способы контакта. 5.  С помощью конструктора добавьте в таблицу Список поле Метод ID с числовым типом. 6.  С помощью мастера подстановки назначьте этому полю подстановку, состоящую из полей МетодID и Вариант таблицы Метод. ◦ Как сделать, чтобы в список подстановки кроме названия метода выводился его кодовый номер? 1.  В имеющихся записях таблицы Список введите значение в новое поле, выбрав его в списке подстановки. 2. Закройте базу данных. ◦ Какие операции закрывают базу данных? Подведение итогов На этом занятии вы научились создавать таблицы, вводить в них данные, изменять структуру таблиц с помощью конструктора, добавлять связь таблиц с помощью мастера подстановок, настраивать сложные индексы. Следующее занятие посвящено описанию форм, предназначенных для форматированного ввода данных и их просмотра на экране, и отчетов, с помощью которых содержимое таблиц выводится на принтер. Занятие № 2. Формы и отчеты ТЕМА ЗАНЯТИЯ Информация баз данных хранится в таблицах, но с ними не очень удобно работать. Для ввода, редактирования и печати информации предназначаются формы и отчеты. Вы познакомитесь с ними на этом занятии, в упражнениях которого рассматриваются следующие темы: • мастер форм; • конструктор форм; • элементы управления; • заголовок и примечание формы; • мастер отчетов; • конструктор отчетов; • группировка и сортировка; • печать данных. Форма В то время как таблицы и запросы позволяют отобразить на экране длинные списки записей, формы дают возможность сосредоточиться на конкретной записи. Они облегчают ввод, редактирование и восприятие информации, могут содержать вспомогательные подписи и элементы оформления.   Упражнение 1. Мастер форм и отображение данных таблиц Мастер форм позволяет сберечь время и быстро сконструировать привлекательную форму для записей любой таблицы. 1.  С помощью команды Файл > Открыть (File > Open) откройте базу данных Контакты, созданную на предыдущем занятии. 2. В окне базы данных щелкните на кнопке Формы (Forms). 3.  В списке форм дважды щелкните на значке Создание формы с помощью мастера (Create Form By Using Wizard). 4. В списке Таблицы/запросы первого окна мастера, показанного на рис. 31, выберите пункт Таблица: Контакты. 5. Щелкните на кнопке », чтобы добавить в список Выбранные поля все поля таблицы. 6.  Выделите пункт Код_Контакты и щелчком на кнопке < уберите это поле обратно в левый список. Содержимое поля Код_Контакты генерируется автоматически, а его значение несущественно для пользователя, поэтому не следует включать это поле в форму. 7.  Щелкните на кнопке Далее. 8.  В следующем окне диалога выберите для формы стиль Выровненный (Justified) и щелкните на кнопке Далее. 9. В списке третьего окна выберите вариант оформления Камень (Stone) или любой другой понравившийся вам стиль и снова щелкните на кнопке Далее. 10.  В последнем окне мастера щелкните на кнопке Готово, не изменяя никаких параметров. Мастер сгенерирует форму и откроет ее в режиме просмотра данных. Рисунок 31. Мастер форм Окно формы содержит названия и области отображения для всех полей таблицы, выбранных в первом окне мастера. В нижней части формы расположены кнопки перемещения по записям, аналогичные кнопкам окна таблицы. 1.  Щелкните несколько раз на кнопке Следующая запись, чтобы добраться до информации о Василии Петрове. В этой записи отсутствуют адрес, название фирмы и телефон. 2.  Щелкните на поле ниже подписи Адрес и введите адрес Василия Петрова. 3.  Аналогичным способом добавьте название компании и телефон (рис. 32). Рисунок 32. Форма для таблицы Контакты 1.  Щелкните на кнопке Новая запись (New Record) и добавьте информацию еще об одном человеке. Как видите, форма позволяет просматривать и редактировать любую запись, а также добавлять записи. Упражнение 2. Настройка формы с помощью конструктора Недостатком форм, создаваемых мастером, является то, что они однообразны и не содержат пояснительных надписей. Чтобы приукрасить форму и расположить поля более удобным образом, следует воспользоваться конструктором форм, который позволяет передвигать и масштабировать элементы формы, связывать их с источником данных и настраивать любые другие параметры формы. Давайте создадим с помощью конструктора форму для таблицы Список. 1.  В окне базы данных дважды щелкните на значке Создать форму в режиме конструктора (Create Form In Design View). В окне конструктора присутствуют пустая форма с разметочной сеткой, вертикальная и горизонтальная линейки, помогающие позиционировать объекты (рис. 33). Неотъемлемым атрибутом конструктора форм является панель инструментов Панель элементов (Toolbox), помогающая добавлять элементы формы. Новую форму необходимо связать с источником данных — таблицей Список. 2. Щелкните в пустой области конструктора и выберите в контекстном меню команду Свойства (Properties). 3. На вкладке Данные (Data) окна диалога свойств формы (рис. 34) щелкните на стрелке поля Источник записей (Record Source). Примечание Любой элемент формы имеет контекстное меню, в котором есть команда Свойства. Выбор такой команды открывает окно свойств, подобное окну свойств формы. Вкладка Макет (Format) содержит параметры форматирования объекта. Вкладка Данные — описание источника данных, различные ограничения и условия, накладываемые на данные поля. Вкладка События (Event) позволяет писать функции отклика на различные события (например, перемещение мыши на объект). Параметры, не вошедшие в первые три группы, размещаются на вкладке Другие (Other). Вкладка Все (Аll) обеспечивает одновременный доступ ко всем параметрам. 1. Выберите в раскрывшемся списке пункт Список. 2.  Закройте окно свойств. В конструкторе форм появится небольшое окно со списком полей источника записей (таблицы Список). Это окно помогает добавлять элементы управления формы. Его можно скрывать или выводить на экран с помощью кнопки Список полей (Field List). Сейчас нужно добавить в форму три области ввода для трех полей таблицы. 3.  Поместите указатель на пункт Дата окна Список, нажмите кнопку мыши и перетащите этот пункт в область формы. MS Access создаст элемент управления типа поле, а также подпись. Рисунок 33. Конструктор форм Рисунок 34. Окно свойств формы 1. Проделайте аналогичную операцию для пунктов Описание и Код_Контакты, расположив поля примерно так, как показано на рис. 35. Любой объект формы можно выделить щелчком мыши, при этом вокруг него появляется габаритная рамка с боковыми и угловыми маркерами масштабирования объекта. В левом верхнем углу объекта появляется большой квадрат, за который можно перетаскивать объект в пределах формы. 2. Щелчком выделите подпись Описание. Затем еще раз щелкните на ней, чтобы перейти в режим редактирования. Замените имеющийся текст на Описание контакта. 3.  Захватите правый боковой маркер поля Описание мышью и растяните рамку поля до правого края формы. Рисунок 35. Добавление полей Рисунок 36. Готовая форма 1.  При необходимости, ухватившись за маркер перемещения мышью, подвигайте поле Описание. 2.  Повторите процедуры шагов 8-10 для всех трех полей. Разместите их и настройте подписи в соответствии с рис. 36. 3.  Чтобы привести оформление новой формы в соответствие с форматом формы, созданной в предыдущем упражнении, щелкните на кнопке Автоформат (AutoFormat) панели инструментов. 4.  Выберите в списке открывшегося окна диалога пункт Камень и щелкните на кнопке ОК. MS Access приведет фон формы, оформление, цвет и шрифт полей в соответствие с выбранным шаблоном. 5.  Щелчком на кнопке Вид перейдите в режим редактирования данных. В форму будет выведено содержимое первой записи таблицы Список. Обратите внимание, что поле с подписью Фамилия имеет такой же список подстановки, который был настроен для поля Код_Контакты таблицы Список на предыдущем занятии. Структура списка подстановки была перенесена в форму в процессе перетаскивания поля на шаге 7. 1. Закройте окно формы. 2.  В появившемся окне диалога введите в качестве имени новой формы слово Список и щелкните на кнопке ОК. Теперь форма Список появится на вкладке Формы (Forms) окна базы данных. Примечание Чтобы одновременно переместить поле вместе с его подписью, тащите объект за тело подписи (но не за маркер перемещения). Упражнение 3. Применение элементов управления При открытии конструктора форм на экране появляется панель элементов (см. рис. 33). Если ее нет, щелкните на кнопке Панель элементов панели инструментов. С помощью кнопок панели элементов в форму можно добавлять различные объекты, типы которых перечислены в табл. 7. Элементы управления форм и отчетов сходны между собой, поэтому такая же панель элементов имеется и в конструкторе отчетов, о котором мы поговорим на этом занятии позже, а все операции, рассматриваемые в этом и в следующем упражнении, одинаково применимы как к формам, так и к отчетам. Примечание В предыдущем упражнении в форму было добавлено шесть элементов управления: два поля ввода (Дата и Описание), одно поле со списком Код_Контакты и три надписи, по одной рядом с каждым полем. Таблица 7. Элементы управления форм и отчетов Значок Название Назначение Надпись (Label) Надписи создаются автоматически вместе с элементами типа текстовое поле, поле со списком и т. п. Они разъясняют смысл поля ввода. Дополнительные надписи могут использоваться для заголовков и пояснений Поле (Text Box) Используется для ввода и отображения информации полей таблиц и запросов, а также для вывода результатов вычислений Группа переключателей (Option Group) Группа переключателей ссылается на некоторое поле таблицы. Каждый переключатель группы соответствует определенному целочисленному значению этого поля Выключатель (Toggle Button) Позволяет вводить информацию типа да/нет. Величине «да» или «истина» соответствует утопленное положение выключателя Переключатель (Option Button) Элемент группы переключателей, в которой может быть отмечен только один из них (его кружок выглядит зачерненным) Флажок (Check Box) Предназначен для представления полей типа да/нет. Отмеченный квадратик соответствует величине «да», а пустой — «нет» Поле со списком (Combo Box) Разрешает как непосредственный ввод значения в поле, так и его выбор в раскрывающемся списке предопределенных значений Список (List Box) Позволяет выбирать данные из набора пунктов и не разрешает непосредственный ввод значений Кнопка (Command Button) Щелчок на такой кнопке выполняет команду, с помощью которой можно перейти в другую форму, переместиться по записям и выполнить многие другие операции Рисунок Рисунок любого графического формата с рамкой Свободная рамка объекта (Unbound Object Frame) Объект OLE, не связанный ни с каким источником данных Присоединенная рамка объекта (Bound Object Frame) Объект OLE, связанный с полем данных таблицы или запроса  Разрыв страницы  (Page Break) Линия, по которой формируется перевод страницы при выводе формы или отчета на принтер Набор вкладок (Tab Control) Позволяет разместить на одном и том же пространстве экрана несколько наборов элементов управления. Удобен в тех случаях, когда элементы легко разделяются на логические группы Подчиненная фор ма/отчет  (Subform/Subreport) Вставляет в форму (или отчет) область с другой формой (или отчетом), связанной с главной Линия (Line) Добавляет прямую линию Прямоугольник (Rectangle) Рисует прозрачный или непрозрачный прямоугольник с цветной границей любой толщины  Другие элементы (More Controls) Открывает меню с элементами управления, не вошедшими в панель элементов Чтобы с помощью панели элементов добавить в форму декоративные объекты, выполните следующие шаги. 1. Поместите указатель мыши на угол области формы. 2. Перетащите этот угол вправо вниз, чтобы немного увеличить форму. 3.  С помощью команды Правка > Выделить все (Edit > Select All) выделите все элементы формы. 4. Нажмите клавишу Ctrl и, не отпуская ее, нажатиями клавиш со стрелками передвиньте элементы формы так, чтобы они были отцентрированы относительно новых границ формы. 5. Щелкните на кнопке Прямоугольник (Rectangle) панели элементов. 6.  Растяните мышью диагональ прямоугольника, чтобы он окружил все элементы формы (рис. 37). 7.  В палитре кнопки Цвет линии/границы (Line/Border Color) панели инструментов Форматирование (Formatting) выберите синий цвет. Рисунок 37. Добавление элементов формы 1. В палитре кнопки Толщина линии/границы (Line/Border Width) выберите толщину 4 пиксела. 2. Щелкните на кнопке Рисунок (Image) панели элементов. 3.  В левом нижнем углу формы растяните рамку рисунка. 4.  В открывшемся окне выбора файла найдите папку, в которую был установлен Office XP (обычно она называется Microsoft Office), раскройте вложенную в нее папку Office10\Broadcast, выберите файл NS_logo.gif и щелкните на кнопке ОК. (Вместо этого файла можете вставить любой другой не слишком большой рисунок.) По умолчанию рисунки вставляются в форму в режиме урезания рисунка рамкой элемента. Чтобы изменить режим размещения, выполните следующие действия. Рисунок 38. Параметры рисунка 1.  Щелкните на рисунке правой кнопкой мыши и выберите в контекстном меню команду Свойства. 2.  В списке Установка размеров (Size Mode) вкладки Макет открывшегося окна параметров выберите пункт Вписать в рамку (Stretch), как показано на рис. 38. Теперь рисунок будет растягиваться и сжиматься вместе с рамкой элемента, подстраиваясь под ее размеры. 3. Чтобы рисунок не закрывал поля формы выделите рисунок и примените к нему команду Формат > На задний план. 4.  Закройте окно параметров. Упражнение 4. Создание заголовка и примечания До сих пор вы имели дело с формами, в которых одновременно отображалась только одна запись. Однако параметры формы можно настроить так, чтобы в ее окне демонстрировался длинный список одинаковых форм, содержащих данные разных записей таблицы. Чтобы включить этот режим, выполните следующие действия. 1.  В конструкторе формы Список щелкните на свободном пространстве правой кнопкой мыши. 2. Выберите в контекстном меню команду Свойства, чтобы открыть окно свойств формы, показанное на рис. 39. 3.  В списке Режим по умолчанию (Default View) вкладки Макет выберите пункт Ленточные формы (Continuous Forms). 4.  Закройте окно параметров. Рисунок 39. Параметры форматирования формы В подобных ленточных формах для размещения заголовков и пояснительных надписей удобно пользоваться дополнительными разделами — заголовком и примечанием формы, которые остаются на месте при прокручивании ленточной формы. Чтобы добавить эти разделы, сделайте следующее. 1.  Выберите команду Вид > Заголовок/примечание формы (View > Form Header/Footer), в окне конструктора формы появятся дополнительные области, показанные на рис. 40. 2.  Щелкните на кнопке Надпись (Label) панели элементов. 3.  Растяните мышью прямоугольник в разделе Заголовок формы (Form Header). 4. Введите текст Список контактов. 5.  Нажмите клавишу Esc, чтобы выйти из режима редактирования текста, но оставить надпись выделенной. 6.  В списке Размер (Font Size) панели инструментов Форматирование выберите число 22. 7.  Щелкните на кнопке По центру (Center). 8.  Поместите указатель на верхний край полосы Область данных (Detail) и, нажав кнопку мыши, перетащите ее вверх, уменьшив область заголовка до необходимых размеров. 9.  Повторив шаги 6-8, введите в примечание формы текст. Если при добавлении контакта вы не нашли нужного человека в списке Фамилии, с помощью формы Контакты добавьте информацию об этом человеке в новую запись таблицы Контакты. 10.  Перетащите нижний край формы вверх, уменьшив ее по высоте. 11.  Щелкните на кнопке Вид. Рисунок 40. Добавление заголовка и примечания Теперь в окне формы может разместиться сразу две записи, как показано на рис. 41. Такую форму можно листать с помощью полосы прокрутки. Заголовок и примечание формы отображаются в верхней и нижней частях окна, не смещаясь при прокручивании записей. Рисунок 41. Ленточная форма Отчет В целом отчеты похожи на формы, но они, как правило, предназначаются для вывода информации из базы данных на принтер. Поэтому в отчетах данные форматируют так, чтобы их было удобно размещать на отдельных страницах. Отчеты поддерживают самые разнообразные способы оформления и позволяют группировать данные, разбивая их на логически цельные блоки. Упражнение 5. Применение мастера отчетов Чтобы облегчить работу пользователя, в MS Access имеется специальный мастер, который при недостатке времени позволяет быстро создавать довольно привлекательные отчеты. Давайте поручим мастеру построение отчета о контактах с разными людьми. 1.  В окне базы данных щелкните на кнопке Отчеты (Reports). 2. Дважды щелкните на значке Создание отчета с помощью мастера (Create Report By Using Wizard). В отчет нужно добавить как информацию о людях, так и данные о контактах с ними. Для этого следует сгруппировать вместе все контакты с одним человеком и распечатать их список ниже области с данными об этом человеке. Затем нужно вывести информацию о следующем человеке и список контактов с ним и т. д. Такой порядок печати записей позволит быстро найти полный список контактов с любым из ваших коллег. В первом окне мастера, показанном на рис. 42, нужно указать все поля, включаемые в отчет. 1. В раскрывающемся списке Таблицы и запросы первого окна мастера отчета выберите таблицу Контакты. 2. В списке Доступные поля выделите поле Фамилия. 3.  Щелчком на кнопке > переместите выбранное поле в список Выбранные поля. 4. Повторяя шаги 4 и 5, добавьте в список Выбранные поля поля Имя, Адрес, Название Компании, Рабочий Телефон. Рисунок 42. Выбор полей для отчета Примечание Поле Код_Контакты содержит служебную информацию, обеспечивая связь записей двух таблиц. Не помещайте такие поля в отчет никогда, кроме случаев отладки взаимосвязей таблиц базы данных. 1. Выберите в списке Таблицы и запросы таблицу Список. 2. Переместите в список Выбранные поля поля Дата и Описание, добавив их к полям таблицы Контакты. 3. Щелкните на кнопке Далее. Во втором окне мастера можно выбрать способ расположения данных — упорядоченные по записям таблицы Контакты, а затем уже по записям таблицы Список, или наоборот. Схема группировки отображается в правой части окна диалога. 1.  Оставьте вариант Контакты и щелкните на кнопке Далее. 2.  В следующем окне не назначайте дополнительной группировки. Просто щелкните на кнопке Далее. 3.  Четвертое окно мастера отчета позволяет задать порядок сортировки записей. Выберите в поле спискок 1 пункт Дата и щелкните на кнопке Далее. Рисунок 43. Пятое окно мастера отчетов Следующее окно, показанное на рис. 43, позволяет выбрать способ расположения полей и ориентацию страницы. Рисунок 44. Фрагмент отчета, сгенерированного мастером 1.  Выберите положение переключателя Структура 1 (Outline 1). 2. Затем щелкните на кнопке Далее. 3.  В следующем окне мастера выберите вариант оформления Строгий (Corporate) и щелкните на кнопке Далее. 4.  Введите в качестве заголовка текст Список контактов и щелкните на кнопке Готово. Мастер сформирует отчет и откроет его в отдельном окне в режиме предварительного просмотра (рис. 44). В нем видно, что сначала идет фамилия первого человека, информация о нем и список контактов с ним, затем располагаются те же данные, касающиеся второго человека, и т. д. В целом отчет выглядит неплохо и может устроить нетребовательного пользователя. Упражнение 6. Конструирование отчета Если внимательно присмотреться к отчету, показанному на рис. 44, можно заметить несколько недостатков. Вот их список: • названия таких полей, как Фамилия и Имя, явно лишние, их следует убрать; • информация о человеке занимает слишком много места, ее можно представить компактнее; • некоторые подписи следует заменить более понятными; • поле Дата слишком коротко, оно не вмещает дату в длинном формате. Давайте исправим указанные недочеты в конструкторе отчетов. 1.  Чтобы переключиться в режим конструктора, выберите команду Вид > Конструктор. В схеме отчета (рис. 45) присутствует несколько разделов. Их назначение указано в табл. 8. Поля и подписи можно размещать в любом разделе отчета, регулируя этим их положение на странице. 1.  Щелчком выделите надпись Фамилия. 2.  Нажатием клавиши Delete удалите надпись. 3.  Повторяя шаги 2 и 3, удалите из раздела Заголовок группы Код_Контакты надписи Имя, Дата и Описание. Назначение соответствующих полей очевидно и не нуждается в пояснении. 4.  Щелкните на горизонтальной линии, расположенной ниже поля Название Компании, правой кнопкой мыши и выберите команду Вырезать (Cut). 5.  Аналогичным приемом удалите три оставшиеся линии раздела Заголовок группы Код_Контакты. Нет смысла выводить подписи полей адреса, названия компании и телефона в заголовке каждой группы. Достаточно будет поместить соответствующие надписи в верхний колонтитул страницы. 6.  Поместите указатель мыши между заголовками разделов Верхний колонтитул и Заголовок группы Код_Контакты, чтобы его значок превратился в двунаправленную стрелку. Нажмите кнопку мыши и перетащите заголовок группы вниз, освобождая место для верхнего колонтитула. Рисунок 45. Конструктор отчетов 7.  Щелчком выделите надпись Адрес. Нажмите клавиши Ctrl+X, чтобы вырезать ее. 8.  Щелкните на заголовке раздела Верхний колонтитул и нажмите клавиши Ctrl+V, чтобы вставить вырезанную надпись в верхний колонтитул. 9.  Выделяя надписи колонтитула и перетаскивая их мышью за маркер верхнего левого угла либо передвигая нажатием клавиши Ctrl и клавиш со стрелками, разместите их так, как показано на рис. 46. 10.  Щелкните на надписи Адрес. Повторным щелчком на ней включите режим редактирования и замените текст на Почтовый адрес. 11. Описанным выше приемом измените текст подписей НазваниеКомпании и РабочийТелефон соответственно на Компания и Телефон. 12.  Переставьте поля раздела Заголовок группы Код_Контакты, разместив их в две строки. Фамилия и имя — в первой строке, а остальные три поля — во второй. 13.  Щелкните правой кнопкой мыши на жирной линии, расположенной в разделе Заголовок отчета. Выберите в контекстном меню команду Копировать (Copy). 14. Щелкните на заголовке раздела Заголовок группы Код_Контакты и нажмите клавиши Ctrl+V, чтобы вставить в этот раздел дубликат линии. 15. Нажимая клавиши Ctrl+V, переместите линию вниз, под вторую строку полей данных. 16.  Снова нажмите Ctrl+V, добавив вторую линию вдоль верхнего края раздела. 17.  Перетащите заголовок раздела Область данных вверх, убрав излишнее пустое пространство. 18.  Щелчком выделите поле Дата. Увеличьте его горизонтальный размер, перетащив вправо серединный маркер правой границы поля. 19. Растяните поле Описание вправо до границы отчета. Окончательный вид новой структуры отчета показан на рис. 47. 20.  С помощью команды Вид > Предварительный просмотр (View > Print Preview) переключите отчет в режим вывода данных. В новом варианте, показанном на рис. 48, информация размещается намного компактнее. 21.  Закройте отчет. При появлении запроса о необходимости сохранения новой структуры отчета щелкните на кнопке Да. Таблица 8. Разделы отчета Раздел Назначение Заголовок отчета (Report Header)   Печатается в самом начале отчета один раз. Выводится и скрывается командой Вид > Заголовок/примечание отчета (View > Report Header/Footer) Верхний колонтитул (Page Header)   Печатается в верхнем поле каждой страницы. Выводится и скрывается командой Вид > Колонтитулы (View > Page Header/Footer) Заголовок группы КолКонтакты (Контакты Header)   Появляется в начале каждой группы записей, соответствующих очередному значению поля КодКонтакты (то есть того поля, по которому назначена группировка) Область данных (Detail) Основные данные отчета   Нижний колонтитул (Page Footer) Печатается в нижнем поле каждой страницы. Выводится и скрывается командой Вид > Колонтитулы (View > Page Header/Footer) Примечание отчета (Report Footer) Печатается в самом конце отчета. Выводится и скрывается командой Вид > Заголовок/примечание отчета (View > Report Header/Footer) Рисунок 46. Перемещение надписей между разделами. Примечание   В нижней части страницы отчета выводятся текущая дата и номер страницы. Эта информация генерируется элементами управления, размещенными в разделе Нижний колонтитул конструктора отчета. Рисунок 47. Модифицированная структура отчета. Рисунок 48. Просмотр нового варианта отчета Упражнение 7. Группировка и сортировка данных в отчете Отчет, который вы сконструировали в двух предыдущих упражнениях, имеет еще один существенный недостаток. Фамилии людей из списка Контакты выводятся не по алфавиту. Мастер отчетов автоматически сгруппировал данные таблицы Список по их принадлежности к тем или иным записям таблицы Контакты, которая определяется связью между полями Код_Контакты этих двух таблиц. По указанной причине фамилии в отчете упорядочены по возрастанию числового параметра Код_Контакты — идентификатора записей таблицы Контакты. Это неудобно, в отчетах с большим числом записей при такой сортировке будет довольно трудно отыскать нужного человека. Есть смысл изменить порядок сортировки, для этого выполните следующие шаги. 1. Щелкните на кнопке Отчеты окна базы данных. 2. Выделите значок отчета Список контактов. 3. Щелчком на кнопке Конструктор панели инструментов окна базы данных от- кройте отчет в режиме конструктора. 4. Щелкните на кнопке Сортировка и группировка (Sorting And Grouping) панели инструментов, чтобы открыть окно диалога, показанное на рис. 49. Рисунок 49. Сортировка и группировка В этом окне назначены два уровня сортировки. Сначала в порядке возрастания по полю Код_Контакты, а затем в пределах списка контактов, относящихся к одному человеку, по возрастанию даты контакта. На кнопке строки Код_Контакты виден значок, указывающий на то, что записи группируются по данному полю, то есть при переборе записей таблицы Контакты для каждого нового значения поля Код_Контакты генерируется заголовок группы, в котором, как вы должны помнить из предыдущего упражнения, выводятся имя, фамилия и другие характеристики людей. Казалось бы, можно просто изменить поле сортировки Код_Контакты на Фамилия, и записи в отчете сразу же выстроятся по алфавиту. Но в этом случае для четырех людей с одинаковой фамилией Петров будет выведен только один заголовок группы, ниже которого разместится список контактов со всеми Петровыми. Вспомните, что новый заголовок группы выводится только для каждого нового значения поля, по которому выполняется группировка. Код_Контакты — уникальный идентификатор записей таблицы Контакты, группировка по которому обеспечивает вывод в отчет всех записей этой таблицы. Для сортировки записей по алфавиту фамилий и имен следует добавить в список окна диалога Сортировка и группировка (Sorting And Grouping) соответствующие поля, разместив их выше поля группировки Код_Контакты. 1. Щелкните в третьей строке на кнопке раскрывающегося списка Поле/выражение (Field/Expression). 2.  Выберите пункт Фамилия. 3. В раскрывающемся списке Поле/выражение четвертой строки окна Сортировка и группировка выберите пункт Имя. Примечание При добавлении нового поля сортировки MS Access автоматически назначает ему порядок сортировки По возрастанию. Чтобы реализовать обратный порядок записей, выберите в раскрывающемся списке ячейки Порядок сортировки соответствующей строки пункт По убыванию (Descending). 1. Щелчком на кнопке строки Фамилия выделите ее. 2.  Поместите указатель на кнопку строки Фамилия, нажмите кнопку мыши и перетащите строку на самый верх списка. Отпустите кнопку мыши в тот момент, когда жирная горизонтальная линия, показывающая будущее расположение перемещаемой строки, окажется над первой строкой таблицы. 3.  Повторяя шаги 8 и 9, разместите строку Имя между строками Фамилия и Код_Контакты, как показано на рис. 50. Рисунок 50. Добавление полей сортировки 1.  Закройте окно Сортировка и группировка.  Примечание Заголовки тех групп, для которых нет ни одной записи в таблице Список, в отчет не выводятся. 12.  Щелкните на кнопке Вид панели инструментов, чтобы вывести отчет в режиме предварительного просмотра. Теперь записи сначала сортируются по фамилиям, затем по именам. После этого для каждой новой записи таблицы Контакты появляется заголовок группы (так как величины поля Код_Контакты для всех записей различны). Ниже каждого такого заголовка из таблицы Список выводятся все контакты, соответствующие данному значению величины Код_Контакты, то есть данному человеку (рис. 51). 1.  Закройте отчет. Вывод результатов Несмотря на многообразие объектов баз данных, все они печатаются с помощью одних и тех же приемов. Вид печатной копии отчета или формы определяется расположением элементов управления в окне конструктора. Таблицы и запросы вообще практически не имеют параметров форматирования. Ширина и высота ячеек, а также шрифт их содержимого настраиваются непосредственно в режиме просмотра таблицы или запроса с помощью команд меню Формат (Format). Перед печатью данных полезно вывести отчет в режиме предварительного просмотра, после чего можно распечатывать его на бумагу.   Рисунок 51. Окончательный вид отчета Упражнение 8. Предварительный просмотр и демонстрация отчетов Перед выводом объекта базы данных на принтер нужно настроить параметры страницы и проверить правильность форматирования в режиме предварительного просмотра. Давайте выполним все эти действия на примере таблицы Контакты. 1.  В окне базы данных щелкните на кнопке Таблицы. 2. Двойным щелчком на значке Контакты откройте эту таблицу в режиме редактирования данных. 3. Увеличьте ширину столбца Адрес (Address), перетащив вправо его правую границу, чтобы адреса не обрезались по длине. 4. Выберите команду Формат > Подтаблица > Развернуть все (Format > Subdatasheet > Expand All), чтобы отобразить записи таблицы Список. 1. Выделите столбец Код_Контакты, щелкнув на его заголовке. 2. С помощью команды Формат > Скрыть столбцы (Format > Hide Columns) скройте выделенный столбец. Этот служебный столбец не нужно печатать. 3.  Щелкните на заголовке столбца Ключ подчиненной таблицы. 4.  Повторным выполнением команды Формат > Скрыть столбцы уберите и этот столбец. Примечание Режим предварительного просмотра является основным вариантом демонстрации отчетов, поэтому он активизируется двойным щелчком на значке отчета в окне базы данных. 1. Выберите команду Файл > Предварительный просмотр. Вы увидите предполагаемое оформление бумажной копии таблицы. Упражнение 9. Печать таблиц и запросов Чтобы распечатать активный объект (таблицу, запрос, форму или отчет) базы данных, выполните следующие шаги. 1. Выберите команду Файл > Параметры страницы (File > Page Setup). Откроется окно диалога, которое позволяет настроить размер страницы, ее ориентацию и поля (рис. 52). 2.  На вкладке Поля (Margins) увеличьте левый отступ до 40 мм. Изменение полей отразится на образце, демонстрируемом в разделе Образец (Sample). 3.  Щелкните на вкладке Страница (Page). При необходимости измените ориентацию на Альбомная (Landscape). Ориентация и поля страницы определяют, сколько записей сможет поместиться на одном листе. Рисунок 52. Параметры страницы 1. Щелкните на кнопке ОК. 2.  Выберите команду Файл > Печать (File > Print). Откроется окно диалога Печать (Print). 3. В раскрывающемся списке Имя (Name) выберите принтер. В разделе Копии (Copies) укажите необходимое число копий и щелкните на кнопке ОК. Упражнение 10. Сжатие, восстановление, защита базы данных и связь с другими приложениями Чтобы поддерживать базу данных в надлежащем порядке, необходимо периодически выполнять некоторые служебные процедуры. Наиболее важной из них является резервное копирование данных, которое выполняется с помощью стандартных средств Windows для работы с файлами. Не менее существенной процедурой является сжатие базы данных. По мере обработки таблиц и добавления объектов файл базы данных увеличивается в объеме. При стирании части информации не происходит полного ее удаления из файла. В результате объем, занимаемый базой данных на жестком диске, может оказаться неоправданно большим. 1.  Чтобы сжать базу данных, выполните команду Сервис > Служебные программы > Сжать и восстановить базу данных (Tools > Database Utilities > Compact And Repare Database). 2.  Сравните размер файла базы данных до и после сжатия. Вы увидите, насколько эффективно сжатие экономит дисковое пространство. Та же самая команда выполняет восстановление базы данных, поврежденной в результате внезапного сбоя электропитания. Иногда в процессе работы вы заметите, что база данных ведет себя как-то странно. В такой ситуации, не дожидаясь предупреждения, выполните команду Сжать и восстановить базу данных. MS Access по возможности восстановит нарушения, обнаруженные в структуре данных. Если база данных содержит конфиденциальные сведения, воспользуйтесь возможностями MS Access по ограничению доступа и шифрованию данных. Чтобы задать пароль на открытие базы данных, выполните следующие шаги. 1.  Закройте базу данных. 2.  Щелкните на кнопке Открыть панели инструментов. 3.  В окне открытия файла выделите значок Контакты.mdb. 4.  Щелкните на стрелке палитры кнопки Открыть и выберите пункт Монопольно (Open Exclusive). Такой режим открытия предотвращает одновременный доступ к базе данных другого пользователя через локальную сеть. 5.  Выберите команду Сервис > Защита > Задать пароль базы данных (Tools > Security > Set Database Password). 6.  В поле Пароль (Password) открывшегося окна диалога введите пароль. 7. Для проверки повторно введите тот же пароль в поле Подтверждение (Verify). 8.  Щелкните на кнопке ОК. Теперь для следующего открытия базы данных потребуется вводить пароль. Примечание Чтобы удалить пароль, выберите команду Сервис > Защита > Удалить пароль базы данных (Tools > Security > Remove Database Password), введите старый пароль и щелкните на кнопке ОК. Еще одним полезным инструментом MS Access является возможность пересылки информации из базы данных в другие приложения Office XP. Например, чтобы для выполнения вычислений переслать таблицу Контакты в книгу Excel, выполните следующие операции. 1.  Щелкните на кнопке Таблицы окна базы данных. 2. Выделите таблицу Контакты. 3. Выберите команду Сервис > Связи с Office > Анализ в MS Excel (Tools > Office Links > Analyze It With MS Excel). Примечание Кроме передачи данных в Excel вы можете с помощью команды Публикация в MS Word (Publish It With MS Word) того же подменю опубликовать их в Word или, выполнив команду Слияние с MS Word (Merge It With MS Word), использовать информацию базы данных в качестве источника данных для слияний, которые обсуждались на занятии 8. MS Access автоматически запустит Excel XP, создаст новую книгу с одним листом и перешлет в его ячейки данные из таблицы Контакты.   Контрольное упражнение Чтобы закрепить приемы работы с формами и отчетами, выполните самостоятельно следующие операции. 1. Для создания нового отчета запустите мастер отчетов. 2.  Включите в отчет поля даты и описание контакта, а также фамилию и имя. 3.  Выберите вариант просмотра по таблице Список. 4.  Настройте группировку записей по дате. Как с помощью мастера отчетов сгруппировать записи по дате? 1. Выберите стиль отчета Блок (Block). 2.  В четвертом окне мастера задайте сортировку по фамилиям и именам в обратном алфавитном порядке. Как назначить обратную алфавитному порядку сортировку по двум полям? 1.   Назовите новый отчет Контакты по датам. 2. Переключитесь в режим конструктора отчетов. 3.  Уберите рамку всех полей. Как быстро убрать рамку всех элементов управления? 1.  Перенесите первое поле даты (которое начинается с функции =Format) из раздела Область данных в раздел Заголовок группы Дата. 1.  Увеличьте ширину поля Дата, чтобы в него помещалась дата в длинном формате. При необходимости переместите другие поля. 2.  Замените текст заголовков полей на корректные русские подписи. 3. Распечатайте полученный отчет. Подведение итогов Теперь вы умеете с помощью мастеров строить формы и отчеты, обеспечивающие форматированный вывод записей таблиц базы данных на экран и на печать. Вы познакомились с конструкторами форм и отчетов, позволяющими изменять любые параметры этих объектов MS Access, и научились распечатывать информацию из базы данных. Следующее занятие посвящено описанию запросов, предназначенных для отбора данных и их просмотра на экране, а также фильтров, с помощью которых содержимое таблиц урезается на основе введенных условий отбора. Занятие 3. Запросы, фильтры, подбор требуемых данных ТЕМА ЗАНЯТИЯ На этом занятии рассказывается о способах фильтрации данных, выборки информации из базы данных на основе определенных критериев и о выполнении вычислений. В упражнениях занятия рассматриваются следующие темы: • мастер запросов; • конструктор запросов; • условие отбора; • итоговый запрос; • запрос действия; • фильтры. Таблицы данных предназначены для хранения информации. Вам, конечно, захочется не только заносить в них данные, но и обрабатывать записанную ранее информацию. MS Access предлагает множество способов извлечения данных из таблиц. Вы можете запросить данные, руководствуясь определенным правилом отбора, отфильтровать нужные записи или рассчитать результирующие значения с помощью формул. Фильтры Фильтры, как и запросы, предназначены для отбора определенных записей базы данных. Но фильтр сохраняется вместе с таблицей и не может использоваться с другими объектами, если он не был записан в виде запроса.   Упражнение 1. Установка фильтра Установка фильтра — простейший способ отбора части записей в таблице, запросе или форме. В этом упражнении с помощью фильтра будет выделено несколько записей таблицы Контакты. 1.  В окне базы данных щелкните на кнопке Таблицы, а затем дважды на значке Контакты. 2.  В столбце Фамилия щелкните на ячейке с фамилией Петров. 3. Щелкните на кнопке Фильтр по выделенному (Filter By Selection). В результате видимыми останутся только те записи, в поле Фамилия которых присутствует значение Петров (рис. 53). 4. Чтобы отменить влияние фильтра, щелкните на кнопке Удалить фильтр (Remove Filter). Рисунок 53. Фильтрация записей Примечание MS Access запоминает последний применявшийся фильтр. Его можно снова назначить простым щелчком на кнопке Применение фильтра (Apply Filter). Это та же самая кнопка, с помощью которой отменяется действие фильтра, но теперь она будет иметь другое название. Фильтр может быть и более сложным. Например, чтобы оставить все записи с фамилией Петров и непустым полем телефона, выполните следующие шаги. 1.  Выберите команду Записи > Фильтр > Расширенный фильтр (Records > Filter > Advanced Filter/Sort). Откроется окно, похожее на конструктор запроса. Критерий равенства поля Фамилия значению Петров уже будет присутствовать в бланке фильтра. 2.  Перетащите поле Рабочий Телефон во второй столбец бланка. 3.  В ячейку Условие отбора введите формулу <>Null (рис. 54). Значение любого пустого поля равно величине NuIl Условие < >NuII проверяет поле на неравенство величине Null, то есть отбирает все непустые поля. 1.  Выберите команду Фильтр > Применить фильтр (Filter > Apply Filter/Sort). Настроенный" фильтр будет применен к таблице Контакты. В результате в ней останутся только две записи. 2.  С помощью команды Записи > Удалить фильтр (Records > Remove Filter/Sort) отмените фильтрацию. Рисунок 54. Окно расширенного фильтра Упражнение 2. Преобразование фильтра в запрос К сожалению, назначение нового фильтра автоматически стирает все предыдущие, которые уже невозможно применить повторно. Если фильтр достаточно сложный, его многократная настройка может отнимать много времени, и вам, вероятно, захочется как-то спасти результат титанического труда. MS Access предлагает простой способ сохранения фильтров. Так как окно фильтра подобно конструктору запроса, было бы логично записать фильтр в виде запроса с теми же условиями отбора. Чтобы превратить фильтр в запрос, выполните следующие шаги. 1. Командой Записи > Фильтр > Расширенный фильтр (Records > Filter > Advanced Filter/Sort) активизируйте окно сложного фильтра, созданного в предыдущем упражнении. 2.  Выполните команду Файл > Сохранить как запрос (File > Save As Query). 3.  В открывшемся окне диалога введите имя Фильтр и щелкните на кнопке ОК. 4. Закройте окно фильтра. 5.  В окне базы данных появился новый запрос с названием Фильтр. Дважды щелкните на значке этого запроса. Откроется окно результата выполнения запроса, записи которого будут повторять записи фильтрованной таблицы Контакты. Этим запросом можно пользоваться когда угодно, выводя на экран записи, отобранные по соответствующим критериям. Более того, любой запрос можно превратить в фильтр. Это делается следующим образом. 1.  Щелчком активизируйте окно таблицы Контакты (если таблица закрыта, откройте ее). Рисунок 55. Таблица "Контакты" 2.  Чтобы назначить новый фильтр (и стереть предыдущий), щелкните на ячейке с фамилией Петров правой кнопкой мыши и выберите в контекстном меню команду Исключить выделенное (Filter Excluding Selection). В таблице останутся только те записи, в поле Фамилия которых нет слова Петров. 3.  Щелчком на кнопке Удалить фильтр отмените фильтрацию данных. Теперь давайте вернем прежний сложный фильтр. 4.  Выберите команду Записи > Фильтр > Расширенный фильтр.  5.  Выберите команду Файл > Загрузить из запроса (File > Load From Query). 6.  В открывшемся окне диалога выделите запрос Фильтр и щелкните на кнопке ОК. Старые условия фильтрации появятся в бланке окна фильтра. Теперь для применения сохраненного фильтра достаточно щелкнуть на кнопке Применение фильтра панели инструментов. Запросы Запросы являются инструментом поиска и структурирования данных. Запрос, адресованный одной или нескольким таблицам, инициирует выборку определенной части данных и их передачу в таблицу, формируемую самим запросом. В результате вы получаете подмножество информационного множества исходных таблиц, сформированное по определенному закону. Если обрабатываемый объем информации велик, выделение необходимых данных в такое подмножество позволяет существенно сократить время их обработки. В системах типа клиент-сервер, где основные базы данных хранятся на файловом сервере, система запросов позволяет уменьшить объем информации, передаваемой через локальную сеть. Упражнение 3. Построение запросов с помощью мастера Чтобы упростить задачу пользователя, в состав MS Access включен мастер запросов, позволяющий автоматизировать процесс построения запроса. Давайте с помощью этого мастера выполним выборку информации из таблиц базы данных Контакты. 1.  В окне базы данных щелкните на кнопке Запросы (Queries). 2.  Дважды щелкните на значке Создание запроса с помощью мастера (Create Query By Using Wizard). 3.  В раскрывающемся списке Таблицы и запросы (Tables/Queries) первого окна мастера выберите таблицу Список (рис. 56). 4.  В списке Доступные поля (Available Fields) щелкните на строке Дата. 5.  Щелкните на кнопке >, чтобы переместить выделенное поле в список Выбранные поля. 6.  Повторяя шаги 3-5, добавьте в список Выбранные поля поле Описание таблицы Список, а также ноле Фамилия таблицы Контакты. 7.  Щелкните на кнопке Далее. 8.  Введите имя запроса Перечень контактов и щелкните на кнопке Готово. Рисунок 56. Мастер запросов MS Access построит запрос и выполнит его. Запрос — это набор условий, согласно которым производится выборка информации из таблиц. Запуск запроса формирует новую таблицу данных, единственным отличием которой от обычных таблиц является то, что с помощью повторных запусков запроса ее данные можно обновлять в соответствии с изменением информации источников данных запроса. Примечание Чтобы база данных выглядела интереснее, в таблицу Список надо добавить несколько новых записей. В рассматриваемом случае условие отбора инициирует получение из таблицы Список полей Дата и Описание всех имеющихся записей, а также поля Фамилия таблицы Контакты. Таблицы Список и Контакты связаны между собой через поле Код_Контакты, при этом Контакты является главной таблицей, а Список — подчиненной (то есть каждой записи таблицы Список соответствует только одна запись таблицы Контакты). Поэтому в поле Фамилия результата запроса выводится фамилия из той записи таблицы Контакты, значение поля Код_Контакты которой совпадает со значением одноименного поля таблицы Список. Результат выполнения запроса показан на рис. 57. Рисунок 57. Результат выполнения запроса Упражнение 4. Конструирование сложных запросов Мастер запросов умеет конструировать только простые условия отбора. Чтобы наложить дополнительные ограничения, следует пользоваться конструктором запросов, обеспечивающим полное управление параметрами запроса и построение сложных условий отбора данных. 1.  Чтобы переключиться в режим конструктора, выберите команду Вид > Конструктор (View > Design View). Окно конструктора показано на рис. 58. В его верхней части отображаются списки полей таблиц, к которым обращается запрос, и связи между таблицами. Нижняя область содержит бланк выбора полей таблиц, условий отбора и режимов сортировки. Чтобы добавить в запрос еще одно поле, выполните следующие шаги. 2.  Переместите указатель на пункт Имя таблицы Контакты. Рисунок 58. Конструктор запроса 1.  Нажмите кнопку мыши и перетащите поле Имя в верхнюю ячейку четвертой строки бланка запроса. Его имя появится в этой ячейке, а имя соответствующей таблицы — во второй ячейке того же столбца. Третья строка бланка запроса позволяет сортировать результат запроса по тому или иному полю. 2.  В раскрывающемся списке третьей ячейки третьего столбца бланка выберите пункт По возрастанию. Примечание Чтобы добавить в запрос сразу все поля таблицы, перетаскивайте пункт *. Если нужная таблица отсутствует в верхней части окна запроса, щелкните на кнопке Отобразить таблицу (Show Table) панели инструментов, выделите нужную таблицу или запрос в открывшемся окне диалога, щелкните на кнопке Добавить, а затем на кнопке Закрыть. 1.  Назначьте тот же режим сортировки для поля Имя. Теперь записи результата запроса будут упорядочены по алфавиту фамилий и имен. Имеющийся вариант связи добавляет в результат запроса только те записи связанных таблиц, в которых значения полей Код_Контакты равны. Так как для некоторых людей из таблицы Контакты нет записей в таблице Список, то информация о таких людях не включается в результат запроса. Чтобы запрос возвращал данные даже о тех людях (включенных в таблицу Контакты), с которыми не было никаких контактов, нашедших отражение в таблице Список, следует изменить параметры объединения. 1.  Дважды щелкните на линии связи. 1. В открывшемся окне диалога Параметры объединения (Join Properties) выберите положение переключателя Объединение всех записей из "Контакты" и только тех записей из "Список", в которых связанные поля совпадают (Include All Records From "Контакты" And Only Those Records From "Список" Where The Joined Fields Are Equal). 2.  Щелкните на кнопке ОК. На одном конце линии связи появится стрелка, указывающая на смену режима объединения. Примечание Изменение режима объединения в окне запроса никак не влияет на параметры исходной связи, определяющие правила взаимодействия между таблицами данных, а задает только порядок отбора записей базы данных, включаемых в результат запроса. 1. Щелчком на кнопке Вид панели инструментов запустите запрос повторно. Результат выполнения запроса с учетом сортировки и нового режима объединения будет таким, как показано на рис. 59. 2.  Закройте окно запроса. 3.  В ответ на вопрос о необходимости сохранения новой структуры запроса ответьте Да. Рисунок 59. Результат выполнения запроса Упражнение 5. Условие отбора в запросе Добавление в запрос условия отбора позволяет выбирать из таблицы не все записи, а лишь те, которые удовлетворяют определенным критериям. Например, вас могут заинтересовать контакты, приходящиеся на сентябрь 2004 года. Давайте модифицируем запрос добавлением соответствующего условия отбора. 1.  Выделите в окне базы данных (рис. 60) значок запроса Перечень контактов. Рисунок 60. Окно базы данных 1. Щелкните на кнопке Конструктор. 2. В бланке запроса щелкните на ячейке Условие отбора (Criteria) первого столбца правой кнопкой мыши и выберите в контекстном меню команду Построить (Build). Откроется окно построителя выражений. 3.  В левом списке построителя щелкните на папке Операторы (Operators). 4. В среднем списке выберите категорию Сравнения (Comparison). 5.  В правом списке дважды щелкните на пункте Between, чтобы добавить этот оператор в ноле формулы. 6. Щелчком выделите в поле формулы первый местозаполнитель «Выражение» («Ехрr»). 7.  В левом списке построителя выражений двойным щелчком откройте папку Функции. 8. 9. Щелкните на папке Встроенные функции, содержащей стандартные функции MS Access. 9. В среднем списке построителя выражений щелкните на пункте Дата/время (Data/Time). 10.  В правом списке дважды щелкните на функции DateValue, чтобы заменить ею местозаполнитель «Выражение». 11.  Нажмите два раза клавишу —>, выделив местозаполнитель «stringexpr». 12.  Введите текст "1.09.04". 13.  Повторяя шаги 7-13, замените второй местозаполнитель «Выражение» на выражение DateValue ("30.09.04"). У вас должна получиться формула Between DateValue ("1.09.04") And DateValue ("30.09.04"). Она проверяет условие нахождения даты в интервале от 1 до 30 сентября 2004 г., то есть отбирает те записи, значение поля Дата которых относится к сентябрю 2004 г. Примечание Если вы уже освоили правила построения выражений MS Access, то можете не пользоваться построителем, а непосредственно вводить выражения в бланк запроса. 15.  Щелкните па кнопке ОК. Построенная формула появится в ячейке Условие отбора первого столбца бланка запроса. При выборе записей, относящихся к одному месяцу, точная дата контакта может оказаться несущественной. Совсем выбросить это поле из бланка запроса нельзя, так как оно необходимо для реализации условия отбора. Однако любое поле можно скрыть, то есть не включать в результат запроса. 16.  Сбросьте флажок Вывод на экран (Show) для первого столбца запроса (рис. 61). Рисунок 61. Скрытие поля. 17.  Щелчком на кнопке Запуск (Run) панели инструментов выполните запрос.  Упражнение 6. Создание итогового запроса Запросы позволяют не только выбирать записи из таблиц, но и вычислять различные статистические параметры. Например, можно подсчитать суммарное количество контактов и вывести даты первого и последнего контакта с каждым из людей, включенных в таблицу Контакты. Чтобы построить такой запрос в режиме конструктора, выполните следующие действия. 1.  В окне базы данных щелкните на кнопке Запросы. 2.  Дважды щелкните на значке Создание запроса в режиме конструктора (Create Query In Design View). 3.  В открывшемся окне диалога (рис. 62) выделите строку Контакты. 4. Щелчком на кнопке Добавить добавьте выбранную таблицу в верхнюю область конструктора запроса. 5. Выделите пункт Список и снова щелкните на кнопке Добавить. 6. Щелчком на кнопке Закрыть закройте окно диалога Добавление таблицы (Show Table). Списки полей двух таблиц, соединенные линией связи, появятся в окне конструктора. 7. Щелкните на кнопке Групповые операции (Totals) панели инструментов. В бланке запроса появится дополнительная строка Групповая операция: (Total), позволяющая выполнять статистические операции со значениями конкретных полей. Рисунок 62. Добавление таблицы 1.  Перетащите поле Фамилия в ячейку Поле (Field) первого столбца конструктора. 2.  В ту же ячейку второго столбца перетащите поле Имя таблицы Контакты. 3.  В третий, четвертый и пятый столбцы бланка запроса перетащите поле Дата таблицы Список (рис. 63). Рисунок 63. Запрос с групповыми операциями 1.  В раскрывающемся списке ячейки Групповая операция (Total) третьего столбца бланка запроса выберите пункт Min. 2. В той же ячейке четвертого столбца выберите пункт Мах. 3. В пятом столбце задайте групповую операцию Count. Групповые операции построенного запроса обработают все записи таблицы Список, соответствующие конкретному человеку из таблицы Контакты, и вместо самих данных таблицы Список выведут в соответствующее поле результата запроса только значение величины, вычисляемой по определенной формуле. Доступные групповые операции перечислены в табл. 9. Таблица 9. Групповые операции Название Функция Условие(Where) Режим задания условия отбора для поля, но которому не выполняется  группировка. MS Access автоматически делает такое поле скрытым Выражение (Expression) Вычисляемое поле, значение которого рассчитывается  по сложной формуле Группировка (Group By) Поле, определяющее группу записей, по которой вычисляются статистические параметры. К одной группе относятся все записи, для которых значения поля с режимом Группировка(Group By) одинаковы  Last  Последнее значение в группе  First  Первое значение в группе  Var  Вариация значений поля StDev Стандартное отклонение величин ноля от среднего Count  Количество записей, соответствующее ноле которых не содержит величины Null  Мах  Максимальное значение Min  Минимальное значение  Avg  Среднее значение поля  Sum  Сумма значений поля по всем записям Примечание Поскольку в пятом поле запроса вычисляется количество записей, в ячейку Поле этого столбца можно поместить любое поле таблицы Список. 1. Щелкните на кнопке Вид, чтобы выполнить запрос. Появится таблица с пятью столбцами. Два первых столбца содержат фамилии и имена людей. По ним выполняется группировка, то есть расчет значений остальных полей запроса выполняется для записей таблицы Список, сопоставляемых с одним человеком. Как уже говорилось выше, соответствие контакта таблицы Список и человека из таблицы Контакты определяется полями Код_Контакты, с помощью которых осуществляется связь этих двух таблиц. Третий и четвертый столбцы запроса выводят соответственно дату первого (функция Min) и последнего (функция Мах) контакта с данным человеком. Пятый столбец содержит количество записей в таблице Список (функция Count), соответствующих. данному человеку, то есть число контактов с ним. Единственный недостаток построенного запроса — это непонятные имена столбцов. Давайте скорректируем их. 2.  Щелчком на кнопке Вид вернитесь в конструктор запроса. 3.  В ячейке Поле третьего столбца замените имя Дата на текст Дата первого контакта: Дата. Правая часть этого выражения, расположенная правее двоеточия, по-прежнему задает имя поля, а левая определяет название столбца результата запроса. Таким образом, любому столбцу запроса можно назначить произвольное имя. 4.  В ячейке Поле четвертого столбца введите Дата последнего контакта: Дата. 5.  В первой строке пятого столбца бланка запроса введите Число контактов: Дата. Примечание К сожалению, подобный прием не подходит для смены названия поля, значение которого не вычисляется, а передается из таблицы. То есть таким способом не удастся переименовать поле Имя. 19.  Снова щелкните на кнопке Вид.  20. Закройте запрос. 21.  Для сохранения изменений структуры щелкните на кнопке Да. 22.  В окне диалога Сохранение (Save As) введите имя Итоговый запрос и щелкните на кнопке ОК. Рисунок 64. Результат отработки запроса Упражнение 7. Запрос действия До сих пор вы сталкивались с запросами, выполняющими выборку данных и некоторые вычисления. Однако запросы могут применяться также для добавления, удаления и обновления группы записей таблицы. Такие запросы являются мощным инструментом преобразования данных, они называются запросами действия. Предположим, что по каким-то причинам вам понадобилось скорректировать даты контактов, заменив во всех записях таблицы Список, относящихся к 2003 году, месяц ноябрь на декабрь. Подобную операцию трудно проделать вручную,- если в таблице содержится несколько тысяч записей. Запрос действия позволяет быстро решить поставленную задачу. 1.  В окне базы данных щелкните на кнопке Таблицы. 2.  Выделите таблицу Список, данные которой нужно обновить. 3.  В палитре кнопки Новый объект (New Object) выберите пункт Запрос (Query). Откроется окно диалога Новый запрос (New Query), показанное на рис. 65. Подобное окно открывается и при щелчке на кнопке Создать (New) окна базы данных. Оно позволяет выбрать наиболее удобный способ создания объекта. 4. Дважды щелкните на строке Конструктор (Design View). Таблица Список, выделенная в окне базы данных на шаге 2, автоматически появится в окне конструктора запросов. 5. Перетащите в бланк запроса поле Дата, значение которого нужно обновлять. Примечание Название поля, заключенное в квадратные скобки, является ссылкой на значение поля. Чтобы сослаться на поле другой таблицы, нужно в начале указать имя таблицы, а затем имя поля (оба имени в квадратных скобках) и разделить их восклицательным знаком. Например [Контакты]! [Фамилия]. Рисунок 65. Создание запроса 6.  В палитре кнопки Тип запроса (Query Type) выберите пункт Обновление (Update Query). Описание всех возможных вариантов запросов, предлагаемых программой MS Access, приведено в табл. 10. Структура бланка запроса видоизменяется в соответствии с типом запроса. В бланке выбранного варианта запроса на обновление появляется поле Обновление (Update To), в которое нужно ввести новое значение поля. Чтобы изменить месяц даты с ноября на декабрь, достаточно прибавить к дате 30 дней. 7.  Введите в ячейку Обновление (Update To) формулу [Дата] +30. 8. Чтобы обновлялись только даты, относящиеся к ноябрю 2004 года, введите ячейку Условие отбора формулу Between DateValue ("1.11.04") And DateValue ("30.11.04"), которая подробно обсуждалась в третьем упражнении этого занятия. 9. Закройте запрос, сохранив его под именем Обновление. 10.  Щелкните на кнопке Таблицы окна базы данных и двойным щелчком на значке Список откройте эту таблицу. В ней есть записи, относящиеся к ноябрю 2004 года. Таблица 10. Варианты запросов Тип Описание Выборка (Select) Выборка данных в таблицу результата запроса на основе указанных условий отбора Перекрестный (Crosstab) Результат запроса выводит статистические значения (сумму, количество или среднее) для одного из полей таблицы в зависимости от двух параметров других полей таблицы, задающих заголовки строк и столбцов результата перекрестного запроса Создание таблицы (Make-Table) Создание новой таблицы в текущей или в другой базе данных на базе информации из имеющихся таблиц Обновление (Update) Обновление данных таблицы Добавление (Append) Добавление набора записей в таблицу Удаление (Delete) Удаление записей таблицы в соответствии с указанным критерием Примечание Запросы действия могут выполнять широкомасштабные изменения данных, которые отменить уже невозможно. Будьте внимательны. Перед запуском таких запросов полезно сделать резервную копию базы данных, чтобы иметь возможность возвратиться к исходному состоянию таблиц. Значки запросов действия снабжены восклицательным знаком, предупреждающим об их особой роли. 11.  В окне базы данных щелкните на кнопке Запросы. 12.  Дважды щелкните на значке Обновление (рис. 66). 13. Ответьте Да на вопрос о необходимости запуска запроса действия. MS Access проинформирует вас об обнаружении четырех записей, удовлетворяющих условию отбора, и попросит подтвердить необходимость их изменения. 14. Щелкните на кнопке Да и изучите изменения, произошедшие с данными таблицы Список. Рисунок 66. Запрос на обновление Контрольное упражнение С помощью контрольного упражнения закрепите знания о запросах и фильтрах, выполнив самостоятельно приведенные ниже операции и ответив на предложенные вопросы. 1.  Запустите мастер запросов. 2.  Добавьте в запрос поля Фамилия и Адрес таблицы Контакты, а затем поля Дата и Описание таблицы Список. 3.  Настройте итоговый запрос, подсчитывающий количество записей.  Как с помощью мастера, создать итоговый запрос? 4.  Переключитесь в режим конструктора. 5.  Скройте третье и четвертое поля запроса. 6. Закройте запрос, сохранив его под именем Количество записей. 7. Откройте таблицу Список. 8. Включите фильтр, оставляющий только записи, относящиеся к фамилии Леонидов. 9. Откройте окно сложного фильтра. 10.  Измените условие фильтрации так, чтобы оставались также записи, касающиеся Петрова. Какое условие отбора необходимо добавить? 11.  Назначьте обновленный фильтр. 12.  Сохраните настроенный фильтр в виде запроса. 13.  Откройте запрос Обновление в конструкторе запросов. 14. Просмотрите его в режиме SQL. Как включить режим просмотра SQL? 15.  Закройте базу данных. Подведение итогов Это занятие завершает серию упражнений, посвященных изучению баз данных MS Access. На занятии были изучены запросы, запросы действия и фильтры. Эти объекты позволяют сортировать данные, выбирать их на основе введенных условий, а также обновлять и создавать таблицы. Контрольные вопросы и ответы на них Контрольный вопрос к Занятию 1.1 Какой параметр определяет длину поля? Чтобы укоротить поле Имя, выделите его в списке конструктора и на вкладке Общие измените значение поля Размер поля. Контрольный вопрос к Занятию 1.2 Как запретить добавление пустого поля? Выберите в списке конструктора поле Фамилия. На вкладке Общие в раскрывающемся списке Обязательное поле выберите пункт Да. Контрольный вопрос к Занятию 1.3 Как сделать, чтобы в список подстановки кроме названия метода выводился его кодовый номер? Щелкните на кнопке Вид (View) панели инструментов. Откроется окно конструктора таблицы. В списке типа данных для поля Код_Контакты выберите пункт Мастер подстановок (Lookup Wizard). В первом окне мастера оставьте выбранным положение переключателя Объект «столбец подстановки» будет использовать значения из таблицы или запроса - щелкните на кнопке Далее. Три положения переключателя второго окна мастера выводят на экран список таблиц, запросов или объединяют эти два списка. Здесь нужно выбрать таблицу или запрос на роль источника подстановки. Щелкните на кнопке Далее. Третье окно мастера предлагает выбрать поля, участвующие в подстановке. Щелкните на кнопке со стрелкой вправо на выбранных полях. В список источника подстановки следует также добавить те поля, содержимое которых должно отображаться полей другой таблицы. Затем щелкните на кнопке Далее. В следующем окне мастер продемонстрирует столбцы таблицы-источника, скрыв столбец с ключевым полем. В четвертом окне мастера подстановки сбросьте флажок Скрыть ключевой столбец (Hide Key Column).  Контрольный вопрос к Занятию 1.4 Какие операции закрывают базу данных? Чтобы закрыть базу данных, достаточно закрыть окно базы данных или приложение MS Access.  Контрольный вопрос к Занятию 2.1 Как с помощью мастера создать итоговый запрос? В третьем окне мастера, которое появляется только в том случае, когда первым полем запроса является поле главной из двух связанных таблиц, выберите положение переключателя Итоговый. Затем щелкните на кнопке Итоги и установите в открывшемся окне диалога флажок Подсчет записей.  Контрольный вопрос к Занятию 2.2 Какое условие отбора необходимо добавить? В ячейку или (Or) первого столбца бланка фильтра введите текст «Петров».  Контрольный вопрос к Занятию 2.3 Как включить режим просмотра SQL? Режим просмотра SQL позволяет вывести в текстовое окно эквивалент запроса, написанный с помощью языка структурированных запросов SQL. Чтобы включить этот режим, выберите команду Вид > Режим SQL (View > SQL View).  Контрольный вопрос Занятие 3.3 Как с помощью мастера отчетов сгруппировать записи по дате? В первом окне выбираются параметры связанных таблиц. Во втором окне выбирается вид представления данных. В третьем окне из левой части в правую надо перенести уровни группировки, то есть в списке третьего окна мастера отчетов выберите поле Дата и щелкните на кнопке >.  Контрольный вопрос к Занятию 3.4 Как назначить обратную алфавитному порядку сортировку по двум полям? В верхнем раскрывающемся списке четвертого окна мастера выберите пункт Фамилия. Щелкните на кнопке справа от этого списка, чтобы включить сортировку по убыванию. Во втором раскрывающемся списке выберите пункт FirstName, затем щелкните на кнопке, расположенной справа от второго списка.  Контрольный вопрос к Занятию 3.5 Как быстро убрать рамку всех элементов управления? Нажатием клавиш Ctrl+A выделите все элементы управления, затем в панели инструментов Форматирование выберите в палитре кнопки Цвет линии/границы (Line/ Border Color) пункт Прозрачный. Список литературы 1. Б. Карпов «Microsoft Acccess 2002: справочник» /«Питер», 2001. 2.  Microsoft Access 2000. Шаг за шагом: Практич. пособ. / Пер. с англ.- М.: Изд. ЭКОМ, 1999.- 352 с. Серия «Информатика» Выжигин Александр Юрьевич Семенов Геннадий Николаевич Базы данных в MS ACCESS Учебное пособие Редактор Т. Л. Ожиганова Оригинал-макет авторов Подписано в печать ХХ.ХХ.2005.Формат 60х841/16. Печ. л. 27,5 Тираж 999 экз. Изд. № . Заказ № . Издательство Московского гуманитарного университета 111395, Москва, ул. Юности, 5/1