Министерство образования Республики Беларусь БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Кафедра «Экономика строительства» СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ ACCESS Лабораторный практикум для студентов специальности 1-27 01 01 «Экономика и организация производства» М и н с к 2005 Министерство образования Республики Беларусь БЕЛОРУССКИЙ НАЦИОНАЛЬНЫЙ ТЕХНИЧЕСКИЙ УНИВЕРСИТЕТ Кафедра «Экономика строительства» СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ ACCESS Лабораторный практикум для студентов специальности 1-27 01 01 «Экономика и организация производства» М и н с к 2 0 0 5 УДК 681.3.066(075.5) ЕБК 33т97я7 Т Т 1 Л .J1 YZ Составители: О.О. Гурьева, Л.П. Гусева Рецензенты: Л.А. Сюсюткина, Т.Н. Водоносова Система управления базами данных Access: лабора- д 2^ торный практикум для студ. спец. 1-27 01 01 «Эконо­ мика и организация производства» / Сост.:О.О. Гурьева, Л.П. Гусева. - Мн.: БНТУ, 2004. - 47 с. Лабораторный практикум содержит задания, позволяющие освоить основные приемы работы и возможности системы управления базами данных Access. Издание предназначено для студентов II курса специально­ сти 1-27 01 01 «Экономика и организация производства». ISBN 985-479-245-5 © БНТУ, 2005 В в е д е н и е Учебным планом предусмотрен лабораторный практикум по дисциплине «Сетевые технологии и базы данных», вклю­ чающий самостоятельное выполнение лабораторных работ в компьютерном классе. Практикум содержит задания для выполнения лабораторных работ, каждая из которых посвящена конкретной теме. По ус­ мотрению преподавателя задания могут выполняться за одно или несколько занятий и предполагают как создание новых баз данных, так и использование лицензионной учебной базы дан­ ных Tutorial. Задания выполняются в среде Access 2000, которая являет­ ся одной из популярнейших систем управления базами дан­ ных (СУБД). Современные СУБД представляют собой сово­ купность языковых и программных средств, обеспечивающих создание, поддержание, доступ к данным со стороны пользо­ вателей, и прикладных программ - приложений. СУБД MS Access 2000 является реляционной базой дан­ ных (БД). Реляционная база данных представляет собой мно­ жество взаимосвязанных двумерных таблиц — реляционных таблиц, называемых отношениями, в каждой их которых со­ держатся сведения об одной сущности автоматизируемой предметной области. Логическую структуру реляционной ба­ зы данных образует совокупность реляционных таблиц, меж­ ду которыми установлены логические связи. В MS Access 2000 реализованы все основные функцио­ нальные возможности СУБД: 1) определение данных — т.е. можно определить, какая именно информация будет храниться в базе данных, задать структуру данных, их тип, а также указать, как эти данные связаны между собой; 2) обработка данных — т.е. можно выбирать любые харак­ теристики объектов, фильтровать и сортировать данные, объ­ единять их с другой связанной с ними информацией и вычис­ лять итоговые значения; 3 3) управление данными — т.е. можно указать, кому разре­ шено знакомиться с данными, изменять их или добавлять но­ вую информацию, определить правила коллективного исполь­ зования данных. Система MS Access — типичная настольная СУБД. На не­ большом предприятии или фирме ресурсов MS Access вместе с ресурсами остальных программных продуктов Microsoft Office достаточно для обслуживания всего делопроизводства. Простые и в то же время мощные средства MS Access могут использоваться для: 1) бухгалтерского учета; 2) ввода заказов; 3) ведения информации о клиентах; 4) ведения информации о деловых контактах. Поскольку СУБД MS Access может работать в режиме кол­ лективного доступа к базам данных, она является хорошим средством разработки приложений для рабочих групп, кото­ рые хранят данные на серверах локальных сетей своих под­ разделений и в то же время периодически пользуются прило­ жениями других подразделений, сохраняя свои данные на серверах учреждения или предприятия. Если речь идет о не­ больших рабочих группах, то хранение и коллективный дос­ туп к данным могут осуществляться только при помощи MS Access. Большие приложения для хранения данных использу­ ют специальный сервер (например, SQL Server), a MS Access на рабочей станции выступает в этом случае в роли клиента. Кроме того, в крупных учреждениях MS Access может ис­ пользоваться как пользовательская среда для обработки дан­ ных - создания собственных запросов на поиск нужной ин­ формации, построения диаграмм, отчетов и т.д. Для автоматизации проектирования объектов БД MS Access 2000 имеет специализированные программы — Конструкторы и Мастера. Конструктор предоставляет пользователю ряд ин­ струментальных средств, с помощью которых можно быстро и просто создавать и модифицировать объекты БД; Мастер де­ 4 лает это по-другому: задает пользователю ряд вопросов и на основе его ответов создает вполне законченный объект БД. Справочная система Access Справочная система Access аналогична тем, что использу­ ются другими Windows-приложениями. После запуска Access можно воспользоваться любым видом справки, если обра­ титься к пункту меню Справка. Выбор в нем пункта Справка по Microsoft Access приводит к появлению окна справки с тремя вкладками. Вкладка Со­ держание используется для просмотра оглавления справки и поиска информации по темам справки; вкладка Мастер от­ ветов — для ввода своего вопроса; вкладка Указатель — для поиска информации по конкретным словам или фразам. Выбор пункта Показать помощника приводит к появлению окна с анимационным персонажем. Помощник дает советы для конкретной рабочей ситуации и отыскивает необходимые разделы справочной документации в ответ на вопросы, вво­ димые в поле ввода. Его можно временно скрыть по команде Справка/Скрыть помощника или отключить на весь сеанс ра­ боты в Access, если в его выноске нажать кнопку Параметры и снять флажок Использовать помощника. Пункт Что это такое? предназначен для получения крат­ кой справки о назначении элементов в главном окне Access. Его выбор приводит к появлению указателя мыши в виде стрелки с вопросительным знаком; щелчок им по интересуе­ мому элементу дает сведения о нем. Пункт Office на Web позволяет соединиться с Web-узлом корпорации Microsoft для получения обновленных сведений о программе. Объекты базы данных и их размещение СУБД Access 2000 ориентирована на работу с объектами БД. Основными объектами MS Access являются таблицы, за- 5 просы, формы, отчеты, страницы доступа к данным, макросы и модули. Таблица — это основная структура, предназначенная для хранения информации в БД. В таблицах БД хранятся все дан­ ные, необходимые для решения задач предметной области. Каждый элемент данных должен храниться в базе только в одном экземпляре. Минимальное дублирование данных в ре­ ляционной БД обеспечивает высокую эффективность поддер­ жания БД в актуальном и непротиворечивом состоянии, од­ нократный ввод и корректировку данных. Запрос — это требование на отбор данных, хранящихся в таб­ лицах, выполнение вычислений над данными, изменения в БД. Форма — созданный на экране шаблон, используемый для ввода, просмотра и редактирования записей БД. Отчет — отображение на принтере или на экране инфор­ мации из БД в виде, удобном для ее восприятия и анализа пользователем. Страница доступа к данным — диалоговая Web-страница, которая поддерживает динамическую связь с БД и позволяет просматривать, редактировать и вводить данные в базу, работая в окне браузера Internet Explorer. Макрос — последовательность макрокоманд для автома­ тизации выполнения операций в среде Access без программи­ рования. Модуль — это программа для работы с БД, написанная на языке Visual Basic for Applications (VBA). Объекты БД могут быть объединены в именованные груп­ пы объектов по функциональному или иному признаку. Все объекты, за исключением страниц доступа к данным, можно хранить в одном файле — файле БД с расширением .Mdb. Страницы доступа сохраняются как самостоятельные файлы с расширением .html отдельно от БД, с которой они связаны, а в файле базы данных размещаются только ярлыки на них. Отчеты можно сохранять в файле с расширением .snp (формат снимка отчета) и распространять среди пользователей по электронной 6 почте. С целью защиты форм, отчетов и модулей VBA БД может быть сохранена в файле приложения с расширением .mde; при этом БД сжимается, оптимизируется использование памяти и по­ вышается быстродействие БД. Проект размещается в файле с расширением .adp на компьютере пользователя. Основные термины и определения Таблица — объект базы данных, используемый для хране­ ния данных. Каждая таблица состоит из строк и столбцов, ко­ торые принято называть соответственно записями и полями. Запись — строка таблицы базы данных, в которой собрана вся информация о конкретном предмете. Например, в таблице «Студенты» базы данных «Кафедра» это — информация о конкретном студенте — его фамилия, год его рождения, сред­ ний балл при поступлении в вуз и т.д. Поле — столбец таблицы базы данных, составляющий часть записи, которая отводится для отдельной характеристи­ ки предмета. Так, в предыдущем примере полями являются фамилия студента, год его рождения, средний балл при посту­ плении в вуз и т.д. Порядок расположения полей с указанием их имен, тип хранимых в них данных, размер этих данных оп­ ределяют структуру таблицы. Первичный ключ (ключевое поле) — поле (совокупность полей), значения которого позволяют однозначно определить (идентифицировать) каждую запись таблицы. Например, в таблице «Сотрудники» значения поля «Табельный номер» яв­ ляются уникальными, и поэтому оно может быть ключом для данной таблицы. Для повышения эффективности поиска требуемых данных ис­ пользуются индексы. Индекс — это внутренняя таблица приложения Access, имеющая два столбца: в первом находятся значения выражения, содержащего все поля, включенные в индекс, во втором — ме­ стоположение каждой записи таблицы с данным значением ин­ 7 дексного выражения. Таким образом, Access сначала осуществ­ ляет поиск данных именно в индексе; если же индекс отсутству­ ет, он просматривает все записи таблицы. После определения таблиц необходимо указать Access, какие действия следует предпринимать для объединения содержимого различных таблиц, т.е. установить связи между таблицами. Межтабличная связь — это отношение, устанавливаемое между полями двух таблиц. Эта связь может быть установлена при наличии в таблицах полей, содержащих совпадающие дан­ ные. Поля не обязательно должны иметь одинаковые имена, но необходимо, чтобы совпадали типы данных и размеры связую­ щих полей. В нормализованной реляционной БД связь между двумя таблицами характеризуется отношениями записей типа один-к- одному (1:1) или один-ко-многим (1:N). Отношение 1:1 предпо­ лагает, что каждой записи одной таблицы соответствует одна запись к другой. Отношение 1 :N предполагает, что каждой за­ писи первой таблицы соответствует много записей во второй, но каждой записи второй таблицы соответствует только одна запись в первой. Например, связь между таблицами «Читатели» и «Заказы» в базе данных Библиотека — связь типа один-ко- многим, т.к. один читатель может сделать много заказов, но любой заказ относится только к одному читателю. Если при этом связующее поле в одной из таблиц является ключевым, такая таблица называется главной, или родительской', вторая таблица, участвующая в связи, называется подчиненной. При этом связующее поле подчиненной таблицы обычно называют внешним ключом. Внешний ключ - это одно или несколько по­ лей в таблице, содержащих ссылку на поле (или поля) первич­ ного кльоча в другой. Для ускорения поиска информации реко­ мендуется создавать индексы по внешним ключам. Связь типа многие-ко-многим (N:M) — это межтабличное от­ ношение, при котором каждой записи в первой таблице могут соответствовать несколько записей во второй таблице, и каждой записи во второй таблице — несколько записей в первой табли­ 8 це. При наличии подобной связи лучше разбить ее на две связи типа один-ко-многим с помощью дополнительной таблицы. Access 2000 поддерживает 3 типа связей (или отношений): один-ко-многим, один-к-одному, многие-ко-многим. При опре­ делении связей между таблицами в Access 2000 можно уста­ новить следующие параметры: 1) обеспечение целостности данных; 2) каскадное обновление связанных полей; 3) каскадное удаление связанных записей. При этом Access автоматически будет отслеживать целост­ ность данных, при которой не допускается наличия в базе данных подчиненной записи без связанной с ней главной. Второй параметр означает, что при изменении значения свя­ зующего поля в главной таблице соответствующие поля под­ чиненной таблицы будут автоматически обновлены. Третья опция означает, что в случае удаления записи из главной таб­ лицы все связанные с ней записи из подчиненной таблицы бу­ дут удалены автоматически. Тема 1. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ При создании БД необходимо решить вопрос о ее наиболее эффективной структуре. Правильно спроектированная БД позволяет: 1) обеспечить быстрый доступ к данным; 2) исключить ненужное повторение данных, которое приво­ дит к нерациональному использованию дискового пространст­ ва компьютера и может явиться причиной ошибок при вводе; 3) обеспечить целостность данных таким образом, чтобы при изменениях в одном объекте автоматически происходили соответствующие изменения в связанных с ним объектах. К основным принципам проектирования БД можно отнести отсутствие в таблице повторяющихся групп полей и повто­ ряющихся записей. Проектирование БД начинается с определения всех объек­ тов, сведения о которых будут включены в базу, и их атрибу- 9 тов. Атрибуты затем сводятся в одну таблицу — исходное от­ ношение. Например, надо создать БД для учета заказов в биб­ лиотеке. Вся информация для этой задачи может быть разме­ щена в исходное отношение -— таблицу «Заказы». Это могут быть следующие данные: 1) номер заказа по порядку; 2) фамилия, имя, отчество читателя, сделавшего заказ; 3) его должность; 4) место работы; 5) рабочий телефон; 6) домашний адрес; 7) домашний телефон; 8) название книги; 9) автор; 10) год издания; 11) стоимость; 12) дата заказа. Информацию в полях рекомендуется хранить в атомарном виде, т.е. в виде минимально возможных элементов, так как наличие в одном поле нескольких элементов затрудняет из­ влечение отдельного элемента из этого поля. Исходное отношение, построенное по перечисленным дан­ ным, содержит избыточное дублирование данных. Явная избы­ точность заключается в том, что строки с данными о читателе, берущем разные книги, повторяются соответствующее число раз. То же можно сказать о повторении данных о книге, взятой разными читателями. Избыточность данных приводит к значи­ тельным затратам на время ввода повторяющихся данных, к росту числа ошибок при их вводе и времени изменения какого- либо данного, так как придется корректировать все записи, со­ держащие это данное. Кроме того, избыточность данных приве­ дет к увеличению размера БД. Средством исключения избыточности в таблицах является их нормализация. Первое правило при проектировании БД: в таблицах долж­ ны отсутствовать повторяющиеся группы полей. Этого можно 10 добиться, поместив поля с повторяющейся информацией о чи­ тателях в отдельную таблицу, присвоив каждому читателю уникальный код, записываемый в новое поле КодЧитателя. В исходном отношении в таблице «Заказы» останется информа­ ция о заказах и книгах, а информация о читателе будет замене­ на полем КодЧитателя. В результате будут получены две таб­ лицы: «Читатели» и «Заказы». Таблица «Читатели» будет иметь атрибуты: КодЧитателя, фамилия, имя, отчество чита­ теля, его должность, место работы, рабочий телефон, домаш­ ний адрес, домашний телефон. Исходная таблица «Заказы» бу­ дет иметь атрибуты: номер заказа, КодЧитателя, название книги, автор, год издания, стоимость, дата заказа. Информацию о книге также необходимо выделить в от­ дельную таблицу «Книги», добавив в нее дополнительное по­ ле, однозначно идентифицирующее книгу, — поле КодКниги. При этом информацию о книге надо исключить из исходной таблицы, заменив ее одним атрибутом — КодКниги. В резуль­ тате в исходной таблице «Заказы» останутся четыре атрибута: номер заказа, КодЧитателя, КодКниги, дата заказа. Поскольку в отдельный заказ может быть включено не­ сколько книг, имеет смысл информацию о заказанных книгах выделить в отдельную таблицу и фиксировать в ней код зака­ за. В результате новая таблица «Заказанные книги» будет со­ держать поля: КодЗаказа, КодКниги; таблица «Заказы» — по­ ля: КодЗаказа,Дата заказа, КодЧитателя. Второе правило при проектировании БД: таблица не долж­ на иметь повторяющихся записей. Для выполнения этого пра­ вила в таблице должен быть уникальный идентификатор (пер­ вичный ключ). В рассматриваемом примере атрибут КодЧи­ тателя является уникальным в таблице «Читатели» и может быть первичным ключом. Первичным ключом может быть КодКниги в таблице «Книги» и КодЗаказа в таблице «Заказы». В таблице «Заказанные книги» уникальной является комбина­ ция ключей КодЗаказа и КодКниги, поэтому первичный ключ здесь — составной и включает в себя поля КодЗаказа и Код- 11 Книги. В результате описанных операций окончательный про­ ект БД включает в себя четыре таблицы. Процессы разработки конкретных программных приложе­ ний в среде Access, в первую очередь, определяются специфи­ кой каждой из автоматизируемых предметных областей. Но для большинства из них можно выделить ряд типичных этапов: 1) разработка и описание структур таблиц данных; 2) разработка схемы данных и задание системы взаимосвязей между таблицами; 3) разработка системы запросов к таблицам БД и их интегра­ ция в схему данных; 4) разработка экранных форм ввода/вывода данных; 5) разработка системы отчетов по данным; 6) разработка программных расширений для базы данных, решающих специфические задачи по обработке содержащейся в ней информации, с помощью инструментов макросов и модулей; 7) разработка системы защиты данных, прав, ограничений по доступу. Между этими этапами существует большое количество об­ ратных связей. З а д а н и е 1 Спроектировать таблицы по данным заданного варианта. Вариант 1. Номер зачетки; фамилия, имя, отчество студен­ та; группа; предмет; оценка; год рождения; адрес. Вариант 2. Фамилия, имя, отчество владельца квартиры; его адрес; сколько человек прописано; наименование комму­ нальной услуги; стоимость коммунальной услуги. Вариант 3. Фамилия, имя, отчество сотрудника; долж­ ность; оклад; адрес; семейное положение; автобиография; дата приема на работу; дата рождения. Вариант 4. Наименование лекарства; аннотация; место хранения; дата поступления; приход; остаток на конец месяца; фирма-производитель; поставщик. 12 Вариант 5. Фамилия, имя, отчество рабочего; номер брига­ ды; табельный номер; специальность; тарифная ставка; семей­ ное положение; адрес; дата рождения; дата приема на работу. Вариант 6. Наименование отдела магазина; наименование продукта; реализовано за первую декаду месяца; стоимость продукта; реализовано за вторую декаду месяца; характери­ стика продукта; производитель продукта; реализовано за тре­ тью декаду месяца. Вариант 7. Наименование предприятия; наименование из­ делий, поставляемых предприятием; характеристика изделий; адрес предприятия; директор; телефон отдела снабжения; план поставок предприятия. Вариант 8. Номер склада; грузополучатель; ответственное лицо грузополучателя; его телефон; местоположение склада; главный кладовщик; его рабочий телефон; отгружено товара. Вариант 9. Спроектировать базу данных в предметной об­ ласти «Футбол в Белоруссии» (не более трех таблиц). Вариант 10. Спроектировать базу данных в предметной области «Агентство недвижимости» (не более трех таблиц). Тема 2. СОЗДАНИЕ И КОРРЕКТИРОВКА БАЗ ДАННЫХ В СИСТЕМЕ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ MS ACCESS 2000 2.1. Пользовательский интерфейс Access Для работы с интерфейсом Access необходимо запустить СУБД MS Access 2000. На экране появляется окно запуска. Для создания файла новой базы данных переключатель уста­ навливают в положение Новая база данных. В появившемся окне Файл новой базы данных надо указать, на каком диске (на диске D:, в папке Мои документы или на своем диске А:), в какой папке, под каким именем создается файл БД, и нажать кнопку [Создать]. Выполнение одной из предложенных опе­ раций вызывает главное окно Access. 13 В строке заголовка отображается имя программы и содер­ жатся кнопки для управления окном. Строка меню содержит несколько пунктов. Их перечень и содержание изменяются в зависимости от режимов работы системы. Панель инструмен­ тов представляет собой набор графических кнопок, назначе­ ние которых — ускоренный вызов команд меню. Access имеет большое количество панелей инструментов, используемых в различных режимах работы. Панели инструментов, соответст­ вующие некоторому режиму, выводятся на экран автоматиче­ ски при переходе в этот режим. 2.2. Создание таблицы В приложении Access 2000 существуют 3 способа создания таблицы: 1) в режиме таблицы; 2) с помощью Мастера; 3) в режиме Конструктора. Наиболее просто таблицы создаются в режиме таблицы выбором строки Создание таблицы путем ввода данных в ра­ бочем поле окна БД или строки Режим таблицы в окне Новая таблица. После выбора этого режима сразу открывается пустая таб­ лица, в которую можно ввести данные. При сохранении этой таблицы Access анализирует данные и автоматически при­ сваивает соответствующий тип данных каждому полю. Таб­ лица имеет 20 столбцов и 31 строку. Полям таблицы по умол­ чанию присваиваются имена Поле! , Поле2 и т. д. Любое поле этой таблицы можно переименовать в соответ­ ствии с требованиями пользователя, непосредственно редак­ тируя имена в заголовке столбцов. Для этого следует дважды щелкнуть кнопкой мыши на области выделения столбца, со­ держащей его имя, или выполнить команду Формат — Пере­ именовать столбец. В результате курсор будет установлен на наименовании поля, и откроется возможность его переимено­ 14 вания. Для сохранения нового имени надо щелкнуть мышкой в любом месте таблицы. По окончании ввода данных во все нужные столбцы таблицу необходимо сохранить. Созданные в этом режиме таблицы тре­ буют доработки в режиме Конструктора. Создание таблицы с помощью Мастера сводится к созданию новой таблицы на основе образца из деловой или личной сфе­ ры. Мастер автоматически создает таблицу по одному из шаб­ лонов. Пользователю предлагается для выбора более 50 образ­ цов таблиц, предназначенных для использования в различных целях. Каждая из них содержит определенный набор полей, из которого пользователь может выбрать нужные. Включаемые в таблицу поля могут быть переименованы. Начать работу Мас­ тера можно, выбрав строку Создание таблицы с помощью Мастера в рабочем поле объекта Таблицы окна БД или строку Мастер таблиц в окне Новая таблица. После создания табли­ цы Мастером можно в любое время доработать структуру таб­ лицы в режиме конструктора. Создание таблицы в режиме Конструктора начинается с вы­ бора строки Создание таблицы в режиме Конструктора в ра­ бочем поле окна БД или Конструктор в окне Новая таблица. При выборе режима Конструктор появляется окно Табли­ ца!: таблица, в котором определяется структура таблицы БД. При переходе в режим Конструктор таблиц меняется состав команд меню и появляется панель инструментов Конструк­ тор таблиц. В этом окне надо описать структуру таблицы, т.е. указать имя поля, выбрать соответствующий тип поля из раскрывающе­ гося списка, задать размер поля и другие свойства в окне Свой­ ства поля, которое становится доступным после выбора типа поля. Если создается главная таблица, необходимо определить в ней первичный ключ. При создании подчиненной таблицы надо учитывать тип связи между таблицами. Для связи типа 1:1 для вторичного ключа задается свойство Индексированное поле со значением “Да (Совпадения не допускаются)”, для связи типа 15 1 :М — поле со значением ‘‘Да (Допускаются совпадения)”. По­ сле описания структуры таблицы окно Конструктора закрыва­ ется, таблица сохраняется, и ей присваивается имя. Для заполнения созданной таблицы конкретными данными она открывается в режиме таблицы посредством кнопки [От­ крыть]. Переход к новой записи вызывает автоматическое со­ хранение предыдущей записи. З а д а н и е 2 Требуется создать БД Библиотека, состоящую из 3 таблиц: «Книги», «Читатели», «Заказы». Таблицу «Книги» создать, используя режим Создание таблицы с помощью Мастера, таблицу «Читатели» — в режиме Конструктора, таблицу «За­ казы»—в режиме таблицы. Ввести данные в таблицы и сохра­ нить их. В таблицах «Книги» и «Читатели» должно быть не менее 10 записей. Для создания таблицы «Книги»: 1) выбрать кнопку [Таблицы](панель Объекты), кнопку [Создать] (панель инструментов окна БД), в диалоговом окне (ДО) Новая таблица выбрать Мастер Таблиц, [ОК]; 2) выбрать образец таблицы для создания собственной таблицы: в диалоговом окне Создание таблиц установить пе­ реключатель Личные, в списке Образцы таблиц выбрать «Книги»; 3) включить в новую таблицу поле КодКниги, поле Перево­ дчик (переименовать его в Автор), MeстоПубликации (пере­ именовать его в Город), Издательство, ГодИзданш, ЦенаПо- купки (переименовать его в Стоимость), ЧислоСтраниц, кноп­ ка [Далее]; 4) в качестве имени таблицы оставить имя «Книги», в раз­ деле Выберите способ определения ключа установить пере­ ключатель Microsoft Access автоматически определяет ключ, кнопка [Далее]; 5) ввести две записи в таблицу «Книги»; 16 6) ввести в структуру таблицы «Книги» следующие изме­ нения: переименовать поле ЧислоСтраниц в Количество страниц-, добавить новое поле Название (текстовый размер 50), вставив его перед полем Город; поменять местами поля Стоимость и Количество страниц; для поля Стоимость из­ менить значение свойства Число десятичных знаков на 0; 7) перейти в режим таблицы; 8) заполнить таблицу данными. 2. Для создания в режиме Конструктора таблицы «Читате­ ли» необходимо: 1) выбрать кнопку [Создать] на панели инструментов окна базы данных; в ДО Новая таблица выбрать Конструктор, кнопка [ОК]; 2) определить поле КодЧитателя (указать имя поля, тип данных - Счетчик)-, 3) определить поля Фамилия, Имя, Отчество, Должность (указать имя поля, тип данных - текстовый, размер поля - по умолчанию); 4) определить поле Место работы (указать имя поля, тип данных - текстовый, размер поля -100); 5) определить поле Индекс (указать имя поля, тип данных - текстовый, размер поля - 10);, задать маску ввода на вкладке Общие свойства поля в нижней части окна в строке Маска ввода (для этого ввести: 000000;;_); по Справке определить, что означает такая маска; 6) определить поле Город (указать имя поля, тип данных - текстовый, размер поля - 50); определить значение, автомати­ чески добавляемое в поле для новой записи (щелчок мыши; в строке Значения по умолчанию ввести "Минск"); 7) определить поле Домашний адрес как поле Место работы-, 8) определить поле Домашний телефон (для этого указать имя поля, тип данных — текстовый, размер поля - 15); задать маску ввода: !\(999") "999\-99\-99;;_); по Справке определить, что означает такая маска; 9) создать копию поля Домашний телефон и поместить ее перед полем Город' переименован» ш ш о в Рабочий телефон', 17 10) задать первичный ключ таблицы (выделить поле Код­ Читателя, меню Правка/Ключевое поле или соответствую­ щую пиктограмму на панели инструментов; обратить внима­ ние, что ключевое поле автоматически индексируется; озна­ комиться с назначением индекса и способами его создания в Справке. Для этого выполнить команду Справка/Справка по MS Access/вкладка Содержание/пункт Создание и разработка таблиц/ Работа с первичными ключами и индексами/Создание индекса для ускорения поиска и сортировки записей; 11) перейти в режим таблицы; 12) заполнить таблицу данными; 13) завершить работу с таблицей «Читатели». 3. Для создания таблицы «Заказы» в режиме таблицы необ­ ходимо определить поля (полями этой таблицы являются: КодЗаказа, КодЧитателя, КодКниги, Дата заказа, Отметка о возврате)', скорректировать структуру таблицы следующим образом в режиме Конструктора: 1) определить поле КодЗаказа (указать тип данных - Счетчик)', 2) определить поле КодЧитателя (указать тип данных — числовой, размер поля — Длинное целое', удалить значение О, автоматически добавляемое в поле для новой записи, клави­ шей [Delete] или [Backspace]; создать индекс по этому полю - Совпадения допускаются)', 3) определить поле КодКниги (указать тип данных — чи­ словой, размер поля — Длинное целое, удалить значение 0, ав­ томатически добавляемое в поле для новой записи, клавишей [Delete] или [Backspace]); 4) определить поле ДатаЗаказа (указать тип данных — Да­ та/время, формат вывода значений даты на экран и на печать — Краткий формат даты, маску ввода с использованием Мастера по созданию масок ввода — Краткий формат даты); 5) определить поле ОтметкаОВозврате (указать тип дан­ ных — логический, формат вывода значений — Да/Нет, зна­ чение, автоматически добавляемое в поле для новой записи, — Нет, тип элемента управления для вывода поля — Флажок); 18 6) определить первичный ключ — КодЗаказа; 7) перейти в режим таблицы; 8) заполнить таблицу данными с учетом того, что один чи­ татель может прийти в библиотеку несколько раз за разными книгами, и одна книга может быть взята разными читателями; 9) завершить работу с таблицей «Заказы». Тема 3. СОЗДАНИЕ СХЕМЫ ДАННЫХ Схема данных является удобным и наглядным средством отображения логических связей таблиц БД, используемых для объединения записей связанных таблиц при любой обработке. Это упрощает процесс конструирования многотабличных за­ просов, форм и отчетов. Если создается связь с поддержкой целостности данных, то можно предусмотреть в БД следую­ щие каскадные операции (т.е. операции по корректировке БД): 1) каскадное обновление связанных полей — изменение значений ключа в записи главной таблицы, приводящее к ав­ томатическому изменению значений вторичного ключа в под­ чиненных записях; 2) каскадное удаление связанных записей — удаление за­ писи из главной таблицы, приводящее к автоматическому удалению всех подчиненных записей. Для создания схемы данных необходимо: 1) открыть окно Схема данных по команде Сервис/Схема данных; найти на панели инструментов соответствующую пиктограмму; 2) определить таблицы, между которыми необходимо соз­ дать связи; в ДО Добавление таблицы выделить протяжкой мыши все связываемые таблицы или щелчком мыши - первую таблицу; нажать клавишу [Shift] и, не отпуская, щелчком мы­ ши выделить последнюю таблицу; 3) нажать кнопки [Добавить] и [Закрыть]; 4) установить связи между таблицами по одноименным полям, перетащив первичный ключ на вторичный; 19 5) установить флажок Обеспечение целостности данных; 6) нажать кнопку [Создать]; 7) сохранить изменения схемы данных и закрыть окно Схе­ ма данных, в котором межцу таблицами установится связь, обозначенная на схеме (например, 1:1 или 1: со). При создании схемы данных возможны неточные действия пользователя: например, добавление лишней таблицы, В этом случае из окна Схема данных надо выделить эту таблицу и на­ жать [Delete], Для удаления линии связи, образовавшейся при перетаскивании первичного ключа на вторичный, ее тоже надо выделить и нажать [Delete], Для печати схемы данных исполь­ зуется команда Файл / Печать схемы данных. Для изменения параметров существующей связи следует: 1) открыть окно Схема данных; 2) сделать двойной щелчок по линии связи, подлежащей изменению; 3) изменить параметры связи в появившемся окне Измене­ ние связей. З а д а н и е 3 Создать схему данных БД Библиотека. З а д а н и е 4 Открыть учебную БД Tutorial. Ознакомиться с данными этой базы данных, создать ее схему данных. Тема 4. СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ЗАПРОСОВ Запрос — это объект БД, который используется для извле­ чения нужной информации из одной либо нескольких таблиц БД или для выполнения определенных действий с данными. Например, чтобы из БД Библиотека получить информацию о книгах всех издательств, расположенных в Минске, необходи­ мо сформировать запрос, в котором будут указаны конкретные 20 условия отбора («Город Минск»). Для записи подобных усло­ вий поиска данных используются специальные операторы. При выполнении запроса из всей совокупности информа­ ции будут отобраны и выведены на экран в табличном виде только те данные, которые удовлетворяют поставленным ус­ ловиям. Представленный на экране результат выполнения за­ проса называют выборкой, или динамической таблицей. В БД запрос сохраняется в виде конкретных условий отбора, т.е. при каждом выполнении запроса выборка формируется вся­ кий раз заново на основе реальных таблиц с учетом всех по­ следних изменений данных. Запросы предназначены для просмотра, анализа и измене­ ния данных, хранящихся в БД. Они используются для созда­ ния форм, отчетов, таблиц, других запросов и страниц доступа к данным. Запросы бывают разных типов: 1) запросы на выборку; 2) перекрестные запросы; 3) запросы действия; 4) запросы SQL. Наиболее часто используемым типом запроса является за­ прос на выборку, предназначений для отбора данных из од­ ной или нескольких таблиц по заданным условиям и отобра­ жающий отобранные данные в виде динамической таблицы. Такие запросы можно также использовать для группировки записей и вычисления сумм, средних значений, подсчета за­ писей и нахождения других типов итоговых значений. З а д а н и е 5 Создать запрос Выбор ! в режиме Конструктор. Его ре­ зультатом должна быть динамическая таблица, содержащая данные всех полей таблицы Office плюс значения нового поля с именем Новое, которые рассчитываются по формуле: YtdSales + YtdSales/2. В динамической таблице должны быть выведены записи для значений поля YtdSales, больших 212000. Данные следует отсортировать по полю State. Вывод 21 всех полей из таблицы «Office» осуществляется выбором сим­ вола *, который расположен первым в таблице «Office». Со­ хранить запрос. З а д а н и е 6 Создать запрос Выбор_2 в режиме Конструктор. Его ре­ зультатом должна быть динамическая таблица, содержащая следующие данные: шифр офиса (Опо), место расположения офиса (City) из таблицы «Office», названия компаний, отно­ сящихся к этому офису (Company), из таблицы «Customer», дату (Date) и числовое значение Itotal из таблицы «Invoices». Упорядочить значения шифра офиса. Вывести перечисленные данные для компаний Info Ltd и Citco Ltd. Сохранить запрос. З а д а н и е 7 Открыть предыдущий запрос, модифицировать его и опре­ делить количество записей, в которых названия компаний на­ чинаются с символов В, I, Q. Модифицировать запрос и вывес­ ти данные для компаний, название которых начинается с сим­ волов System. Сколько таких записей? Сохранить запрос под именем Выбор_3, используя команду Файл/Сохранить как... З а д а н и е 8 Открыть предыдущий запрос. Изменить его и определить количество записей, в названии компаний которых слово Systems находится в конце названия. Отобрать данные для дат, находящихся в промежутке 11.05.90 и 17.05.90. Сколько таких записей? Сохранить запрос под именем Выбор_4. З а д а н и е 9 Открыть запрос Выбор_2. Убрать условия отбора. Создать новое вычисляемое поле Itot, значение которого вычисляется 22 по формуле Itotal (из таблицы «Invoices»)*Опо (из таблицы «Office»). Сохранить запрос под именем Выбор_5. Опреде­ лить количество записей, в которых значение поля Itot больше или равно 8361, меньше 11665 и равно 14223. Закрыть запрос без сохранения условий отбора. З а д а н и е 10 Открыть предыдущий запрос Выбор_5. Удалить поля Com­ pany и Idate. Упорядочить значения поля Опо. Просмотреть ре­ зультирующую таблицу. Использовать команду Вид/Групповые операции, чтобы вывести для каждого офиса значение суммы значений поля Iotal, среднее значений поля Itot. Просмотреть результирующую таблицу. Вывести количество городов, в ко­ торых расположены филиалы офисов с одним шифром. Сохра­ нить запрос под именем Выбор_6. З а д а н и е 11 Построить запрос Выбор 7 по таблицам «Invoices» и «De­ tail». Результирующая таблица должна содержать поля: Ino, Cno, Price, Line, Итого. Значение поля Итого вычисляется по формуле: значения поля Price умножаются на значения поля Line. Отобрать записи, для которых значения поля Ino нахо­ дятся в промежутке 1100 и 1200. Для каждого значения поля Ino вывести значение суммы по полю Price, минимальное зна­ чение поля Итого и количество записей, участвующих в под­ ведении итогов. Сохранить запрос под именем Выбор ?. К запросам на выборку относятся запросы с параметрами — запросы, которые при выполнении отображают в собственном диалоговом окне приглашение ввести данные, - например, ус­ ловие для отбора записей или значение, которое требуется вставить в поле. Диалоговое окно создается MS Access авто­ матически после задания параметра и определения типа вво­ димых данных. Для поля, по которому проводится выборка данных, задается параметр в строке "Условие отбора" в квад- 23 ратных скобках, который представляет собой приглашение для ввода значения. Например: [Введите, пожалуйста, фами­ лию студента]. По команде Запрос/Параметры можно до­ полнительно задать тип вводимых данных: в нашем примере вводимая фамилия имеет тип "текстовый". Чтобы запросить у пользователя один или несколько символов для поиска запи­ сей, которые начинаются с этих символов или содержат их, создается запрос с параметрами, использующий оператор Like и подстановочный знак (*). Например, выражение: Like [Вве­ дите первый символ для поиска: ] & будет выполнять по­ иск слов, начинающихся с введенного символа. Выражение: Like "*" & [Введите любой символ для поиска: ] & выпол­ няет поиск слов, которые содержат указанный символ. З а д а н и е 12 Создать запрос с параметром по таблицам «Office», «Custo­ mer», «Invoices». Для задаваемого штата вывести количество офисов (Опо), среднее значение поля Itotal и максимальное значение поля Itot. Параметром является фраза [Введите на­ звание штата]. По команде Запрос/Параметры задать тип вводимых данных. З а д а н и е 13 Вывести для задаваемых начальным символом городов из таблицы «Office» (Опо) и из таблицы «Customer» название ком­ паний; город и штат, в которых расположены компании; число­ вое поле Ytdpurch, заданное в промежутке значений 1000... 10000. Сколько получится записей для городов, начи­ нающихся с символа "N"; какое значение Ytdpurch минимально, какое максимально? Сколько получится записей для городов, на­ чинающихся на символ "В", на символы "Во"? Сколько получит­ ся записей для городов, начинающихся на символ "М"? Какой город - первый по алфавиту? Какой последний? Сколько записей получится для городов, начинающихся на символы "Mi"? 24 З а д а н и е 14 Вывести для штатов, содержащих символ "Т", из таблиц «Customer», «Invoices», «Detail» суммарное значение поля Itotal, количество деталей, определяемое по их шифру (поле Рпо), максимальное значение цены деталей (поле Price). Оп­ ределить количество записей. Вывести эти данные для шта­ тов, содержащих символ "А". Определить количество записей. Вывести эти данные для штатов, содержащих и символ "О". Определить количество записей. Перекрестные запросы группируют данные, представляя их для анализа в более наглядном и компактном виде. В таком за­ просе данные выводятся подобно сводным таблицам приложе­ ния «Excel»: каждому столбцу и каждой строке соответствует поле исходной таблицы. При создании перекрестного запроса для каждой из пар строка/столбец исходной таблицы вычисля­ ются значения в соответствии с заданной функцией. Данные мо­ гут быть разбиты на группы, для которых выполняются группо­ вые вычисления (суммы, количества записей и средних значе­ ний). Перекрестный запрос создается с помощью Мастера или самостоятельно в режиме Конструктора. В этом режиме можно указать поля, значения которых будут заголовками столбцов и строк, а также поле, значения которого следует использовать в вычислениях. Мастер перекрестного запроса вызывается из диа­ логового окна Новый запрос. З а д а н и е 15 С помощью Мастера создать перекрестный запрос Запр перек ! . Результат этого запроса представляет собой таб­ лицу, в которой поле Company (из таблицы «Customer») со­ ставляет заголовки строк, поле Опо (таблица «Office») - заго­ ловки столбцов, а поле Itotal (таблица «Invoices»)—суммарное значение для каждого офиса. Создание перекрестного запроса начинается с создания запроса на выборку по заданным полям; 25 далее производится операция группировки данных по шифру офиса; затем, используя клавишу [Тип запроса], выбирают тип - перекрестный. В соответствии с заданием задаются заголовки строк, столбцов, выводимые значения. З а д а н и е 16 В режиме Конструктора создать перекрестный запрос Запр_перек_2. Данные предыдущего задания вывести для компаний, название которых начинается на символы "Во". З а д а н и е 1 7 В режиме Конструктора создать перекрестный запрос Запр перек З. Данные предыдущего задания вывести для ком­ паний, в названии которых присутствует слово «Computer». Запросы действия или запросы на изменения — это запро­ сы, в результате действия которых изменяются данные самой БД. К таким относятся запрос на обновление, запрос на удаление, за­ прос на добавление, запрос на создание таблиц. Результаты рабо­ ты этих запросов являются необратимыми. Для предотвращения ошибок, связанных с этим обстоятельством, необходимо сделать резервную копию таблицы, прежде чем применить к ней запрос на изменение. Лучше всего первоначально запустить запрос на изменение в качестве запроса на выборку. Убедившись, что он выделяет необходимые записи, надо преобразовать его в запрос на изменение и запустить на выполнение. Ознакомиться с созда­ нием запросов действия в справке Access. З а д а н и е 18 Создать копию таблицы «Customer» и назвать новую таб­ лицу «Custl». Удалить из таблицы «Custl» все записи, создав для этого запрос на удаление. 26 З а д а н и е 19 Создать запрос на добавление записей в таблицу «Custl». Для этого создать запрос на выборку по таблице «Customer», отобрать поля Company, City, State, Ytdpurch. Отобрать записи для штата Оризона (OZ). Проверить результаты: должно быть отобрано 8 записей. Используя клавишу [Тип запроса], в ре­ жиме Конструктора выбрать запрос на добавление записей. Записи добавлять в таблицу «Custl». Продемонстрировать выполнение запроса. Дважды выполнить этот запрос, каждый раз проверяя результат выполнения. З а д а н и е 2 0 Создать запрос на обновление значений поля Ytdpurch таб­ лицы «Custl». Новое значение этого поля вычисляется по формуле: Ytdpurch-2,3. З а д а н и е 2 1 Создать запрос на поиск повторяющихся записей в таблице «Customer» (значения повторяются в поле State), дополнительно вывести названия компаний и их адреса. З а д а н и е 2 2 Создать таблицу «Новая», которая должна содержать поля Опо, Ytdsales, Company, Itotal. Выполнить запрос, проверить результат. Модифицировать запрос, вводя условие отбора для поля Опо. Тема 5. ФОРМЫ В ПРИЛОЖЕНИИ ACCESS 5.1. Назначение и способы проектирования форм Для ввода, редактирования и просмотра данных Access предоставляет удобное и широко используемое средство — 27 формы, которые позволяют ограничить объем информации, отображаемой на экране, и представить ее в требуемом виде. Создавая форму, можно выбрать, из каких таблиц, запросов, какие поля и в какой последовательности должны быть в ней представлены. Можно разбить их на логически связанные группы, задать удобное расположение на экране, вставить ил­ люстрации, графически представлять хранящуюся в базе дан­ ных информацию. Таким образом, форма — объект базы дан­ ных, позволяющий создать удобный пользовательский интер­ фейс для работы с данными. Создание форм требует дополнительных усилий, однако потраченное время возмещается за счет уменьшения ошибок при вводе, удобства доступа к информации, наглядности ее представления. Кроме того, они могут служить защитой базы данных от действий неквалифицированных пользователей. Форма для ввода представляет собой бланк, подлежащий заполнению. При этом появляется возможность осуществить контроль вводимых данных и исключить ввод неверных. Бланк-форма упрощает процесс заполнения базы данными (благодаря чему в БД может вводить информацию пользова­ тель невысокой квалификации), позволяет ограничить доступ пользователя к информации БД, заблокировав служебные и засекреченные поля или записи. Формы могут быть простыми и составными (включающи­ ми другие формы), содержать различные элементы - поля БД, подписи к ним, списки, флажки, переключатели, кнопки, вкладки и другие элементы управления. Их можно спроекти­ ровать на базе одной или нескольких таблиц и/или запросов; на основе одной таблицы или запроса можно построить не­ сколько форм. Имена полей в форме берутся из описания таб­ лицы, а сами поля пользователь располагает в соответствии со своими вкусами и требованиями и вносит различные элемен­ ты оформления - линии, рисунки, заливку и др. Форма может быть создана "вручную" — с помощью Конст­ руктора форм, автоматизированным способом — с помощью 28 Мастера форм и автоматически — с использованием автофор­ мы. Конструктор форм предоставляет пользователю набор инструментов, с помощью которого пользователь может соз­ дать форму соответственно своим вкусам и требованиям. Мас­ тер форм руководит процессом проектирования форм, задает пользователю вопросы о структуре и оформлении формы, предлагая выбор из нескольких вариантов. Автоформа автома­ тически создает на основе выбранной таблицы одну из форм: в столбец, ленточную, табличную. 5.2. Инструментальные средства конструкторов форм Для создания формы с помощью Конструктора необходимо открыть окно Конструктора формы, выполнив определенную последовательность действий: окно База данных/объекты Фо/шы/[Создать]/окно Новая форма/выбрать Конструктор/уквзтъ источник формы — таблицу или запрос/[ОК]. Окно Конструктора формы содержит следующие инстру­ ментальные средства конструирования: 1) панель Конструктора с набором графических кнопок, вклю­ чаемая/выключаемая по команде Вид/Панели инструментов; 2) разделы проекта, в которых размещают различные гра­ фические объекты, определяющие вид и содержание формы, называемые элементами управления; 3) панель элементов управления, включаемая/выключаемая по команде Вид/Панель элементов; 4) список полей таблицы или запроса, служащих источни­ ком данных для формы, включаемый/выключаемый по ко­ манде Вид/Список полей; 5) сетка, отображаемая в разделах проекта для удобства проектирования, включаемая/ выключаемая по команде Вид/ Сетка; 6) линейки горизонтальная и вертикальная, включае­ мые/выключаемые по команде Вид/Линейка. 29 Инструментальные средства конструирования предназна­ чены для удобства проектирования форм. По умолчанию в окне Конструктора формы выводится раз­ дел проекта Область данных, в котором размещают поля БД из таблицы или запроса, а также вычисляемые поля. Кроме данного раздела, при конструировании могут быть востребо­ ваны и другие разделы: Заголовок формы и Примечание формы, включаемые/выклю­ чаемые попарно соответственно по командам: Вид / Заголовок / Примечание формы; Верхний колонтитул и Нижний колонтитул, включае­ мые/выключаемые попарно по команде Вид /Колонтитулы. Заголовок формы содержит сведения, общие для всех запи­ сей (название формы, дата, время). Примечание формы ото­ бражает сведения, общие для всех записей (инструкции по ра­ боте с формой, командные кнопки, общие суммы в счетах). Верхний колонтитул и Нижний колонтитул используются в случае конструирования формы табличного вида: в Верхнем колонтитуле отображаются названия столбцов, в Нижнем колонтитуле — номер страницы, дата. Изменение высоты и ширины раздела производится путем перетаскивания соответ­ ственно нижней и правой границ раздела. 5.3. Элементы управления и работа с ними Разделы проекта формы наполняются различными графи­ ческими объектами — элементами управления, используе­ мыми для отображения данных, выполнения действий или в качестве украшений. Элементы управления делятся на связанные и несвязанные. Связанные элементы управления присоединены к полю базо­ вой таблицы или запроса и используются для просмотра, вво­ да или редактирования значений из полей БД. Ввод связанно­ го элемента управления в проект формы осуществляется пу­ тем перетаскивания из окна со списком полей нужного поля в 30 раздел Область данных. В результате появляются два прямо­ угольника — подпись поля и поле. Маркеры перемещения служат для отдельного перемеще­ ния подписи и поля. При установке на них указателя мыши появляется изображение руки с вытянутым указательным пальцем, и тогда следует выполнить перемещение. Для одновременного перемещения подписи и поля указа­ тель мыши устанавливается на границу выделенной подписи или поля. Когда появляется изображение раскрытой ладони, производят перетаскивание. Для удаления подписи осуществ­ ляют ее выделение и нажимают [Delete]; для удаления поля с подписью выделяют поле и нажимают [Delete]. Для несвязанных элементов управления источника данных не существует. Они используются для отображения линий, прямоугольников, рисунков. Ввод в проект формы такого эле­ мента управления осуществляется следующим образом: производится щелчок по кнопке нужного элемента управления на панели элементов, а затем - щелчок в том разделе проекта, где требуется разместить этот элемент. Выделение группы элементов управления производится путем щелчков по элементам управления, входящим в группу, при нажатой клавише [Shift] или очерчивания рамки вокруг этой группы. Чтобы изменить размер элементов группы, вы­ ровнять их, изменить интервал между ними, необходимо вос­ пользоваться пунктом меню Формат. Возможны перенос и копирование элементов управления в другой раздел проекта посредством команд для работы с буфером обмена. Для придания элементу управления необходимых свойств его выделяют и вводят команду Вид/Свойства или вызывают контекстное меню и выбирают в нем пункт Свойства. В ре­ зультате появляется окно свойств элемента управления, в ко­ тором можно произвести его форматирование, например, с помощью вкладки Макет. 31 При необходимости спроектировать форму, в которой должны быть определенные заголовки, поля БД, вычисляемое поле и комментарий, следует: 1) открыть окно Конструктора формы, не забыв указать источник формы; 2) настроить это окно для работы, включив (если не вклю­ чены) линейки, сетку, панель элементов и три раздела: Заголо­ вок формы, Область данных, Примечание формы; 3) задать заголовок формы, для чего в раздел Заголовок формы вставить элемент управления Надпись и ввести текст заголовка; 4) из окна со списком полей перетащить в раздел Область данных те поля БД, значения которых должны просматривать­ ся по форме, и расположить их в нужном порядке; 5) создать вычисляемое поле, для чего вставить в раздел Область данных свободное поле, используя элемент управле­ ния Поле; в само поле ввести выражение для расчета, а в под­ пись — имя вычисляемого поля; 6) задать необходимый комментарий в разделе Примеча­ ние формы, используя элемент управления Надпись; 7) произвести желаемое оформление формы. 5.5. Работа с формой Спроектированная форма приобретает реальный вид в окне формы, открываемом из окна БД с помощью кнопки [Открыть] и из окна Конструктора по команде Вид / Режим формы или Вид / Режим таблицы. В режиме формы в форме отображает­ ся только одна запись, а в режиме таблицы — группа записей. В обоих режимах можно редактировать данные, просматри­ вать их с помощью кнопок перехода к записям (к предыдущей, к следующей, к первой, к последней). Кнопка перехода к пус­ той записи позволяет добавлять в базу новые данные. 5.4. Рекомендации для создания формы 32 Форму можно сохранить, введя в окне Конструктора или в окне формы одну из команд: Файл / Сохранить как, Файл / Сохранить или просто закрыв эти окна. Форма может быть предварительно просмотрена из окна БД, из окна Конструктора и из окна формы по команде Файл / Предварительный просмотр и распечатана по команде Файл / Печать. При печати ее заголовок появляется только в начале первой страницы, а ее примечание — в конце последней. Верхний и нижний колонтитулы видны только при печати со­ ответственно в начале и в конце каждой страницы. Изменения в форму вносятся в окне Конструктора. З а д а н и е 2 3 Создать по таблице «Detail» экранную табличную автоформу. В режиме Конструктор расширить раздел Примечание. Вста­ вить в этот раздел элемент управления Календарь. В рабочей об­ ласти выделить все поля, щелкнув мышью слева на линейке. Для всех полей задать цвет фона — желтый, тип фона — обычный, оформление — утопленное, цвет границы и цвет текста — тем­ но-зеленый, шрифт — Times Roman Суг, размер шрифта — 11, насыщенность — плотный, выравнивание текста — по центру. Использовать для этого контекстное меню: команда Свойст- еа/вкладка Макет диалогового окна Несколько элементов управления. Расширить раздел Заголовок формы. Сдвинуть все элементы этого раздела вниз на 2,5 см. Создать заголовок формы — Detail. У этого элемента задать размер шрифта — 14, сверхжир- ный, Courier Суг, цвет фона — желтый, цвет текста — темно­ зеленый, оформление — приподнятое. Сохранить форму под именем Detail. Просмотреть форму в режиме таблицы и в режи­ ме формы. Определить количество записей, выводимых в экран­ ную форму. Сохранить под именем Форма_1. З а д а н и е 2 4 Создать по таблице «Invoices» экранную автоформу в стол­ бец. В режиме Конструктор расширить разделы Заголовок 33 формы и Примечание формы. В разделе Заголовок формы соз­ дать надпись “Таблица Invoices”, в разделе Примечание формы — “Автоформа в столбец”, задать размер области данных — 6 см. Просмотреть форму в режиме таблицы и в режиме формы. Со­ хранить под именем Форма_2. З а д а н и е 2 5 Создать экранную форму, в которой из таблицы «Customer» выводятся поля Company, Address, City, State, Zip, Lat, Long, Ytdpurch, из таблицы «Office» — Ono, Address, City, State, Zip, Phone. Использовать для этого в качестве источника данных соответствующий запрос. Использовать Мастер форм. Про­ смотреть форму в режиме формы. Сохранить под именем ФормаЗ. З а д а н и е 2 6 Создать по таблицам «Office» и «Customer» экранную фор­ му с подчиненной формой, используя Мастер форм. Выводи­ мые поля взять из задания 2. В подчиненной форме изменить заголовок Company на Компании. Вставить гиперссылки для вызова документа из приложений Word, Excel, PowerPoint. Просмотреть форму в режиме формы. Сохранить под именем Форма_4. З а д а н и е 2 7 Создать связанные формы по таблицам «Customer» (выво­ димые поля из задания 2) и «Invoices» (выводимые поля — Idate, Itotal, Salesman). Основную форму сохранить под име­ нем Cust_s_inv, связанную — под именем Связанная. В ос­ новной форме создать вычисляемое поле Сумма, которое со­ держит сумму значений полей Lat, Long, Ytdpurch, для чего перейти в режим Конструктор. Элемент управления Поле пе- 34 ретащить в область данных формы Cust_s_inv. Изменить над­ пись на Сумма. Выделить поле, вызвать контекстное меню, выбрать команду Свойства..., вкладка Данные, строка Дан- ные. Выбрать Построитель выражений, (±3 фор­ мы, Cust s inv, поля Lat + Long + Ytdpurch. Просмотреть форму в режиме формы. Применить фильтр Расширенный по полю State = СА. З а д а н и е 2 8 Построить плоскую гистограмму по запросу Дляформы по таблице «Office», содержащему поля Опо, City, Ytdsales и вы­ числяемое поле Расчет = (Ytdsales + Zmin)/10. Гистограмма должна отображать объемы продаж (Ytdsales) и рассчитанную величину {Расчет) по офисам компаний (Опо) или по городам (City), в которых расположены эти офисы. Просмотреть гисто­ грамму в режиме формы. Изменить тип гистограммы в режиме Конструктора на объемный. Отредактировать заголовок гисто­ граммы, удалить легенду, изменить шрифты, развернуть гисто­ грамму. Просмотреть результат в режиме формы. Сохранить под именем Диаграмма. Тема 6. ОТЧЕТЫ В ПРИЛОЖЕНИИ ACCESS 6.1. Создание отчета Отчет является важным средством извлечения информа­ ции из БД и вывода ее на экран или на печать в виде, удобном для восприятия и анализа пользователем. В отчете можно сор­ тировать и группировать данные, осуществлять расчеты в строках и проводить итоговые вычисления над группами строк и над всеми строками с использованием статистических функ­ ций. Отчет может основываться на множестве таблиц, пред­ ставлять сложные зависимости между различными наборами данных, быть составным, — т.е. включать другие отчеты. 35 Access предоставляет большие возможности для оформле­ ния отчетов: шрифтовое, фоновое и цветовое оформление, об­ рамление, рисунки, деловую графику, вставку объектов дру­ гих приложений. Существует 3 способа создания отчета: с помощью Конст­ руктора, с помощью Мастера отчетов и автоматическое — ав­ тоотчеты. Конструктор дает возможность самостоятельного проектирования отчетов; Мастер отчетов позволяет создать отчет на основе ответов пользователя на вопросы, касающиеся структуры, содержания и оформления отчета; автоотчет соз­ дает отчет в столбец и ленточный. Для создания отчета с помощью Конструктора необходимо открыть окно Конструктора отчета, выполнив следующие действия: открыть БД/ выбрать объект Отчеты!в меню окна базы данных выбрать Создать/в открывшемся окне Новый отчет выбрать команду Конструктор/укюаль источник отчета — таблицу, запрос или другой отчет/нажать [ОК]. Так как методика проектирования формы и отчета одина­ кова, то и окно Конструктора отчета содержит те же инстру­ ментальные средства конструирования, что и окно Конструк­ тора формы: 1) панель Конструктора с набором графических кнопок, вклю­ чаемая/выключаемая по команде Вид/Панели инструментов', 2) разделы проекта, в которых размещаются различные элементы управления: заголовок отчета, верхний колонтитул, заголовок группы, область данных, примечание группы, ниж­ ний колонтитул, примечание отчета; 3) панель элементов управления, включаемая/выключаемая по команде Вид/Панель элементов', 4) список полей таблицы или запроса, служащих источни­ ком данных для отчета, включаемый/выключаемый по коман­ де Вид/Список полей; 5) сетка, отображаемая в разделах проекта для удобства проектирования, включаемая/ выключаемая по команде Вид/ Сетка; 36 6) линейки — горизонтальная и вертикальная, включае­ мые/выключаемые по команде Вид/Линейка, также предназна­ ченные для удобства проектирования отчетов. 6.2. Работа с отчетом Спроектированный отчет можно предварительно просмот­ реть в одном из двух представлений: общий вид и страничный вид. Общий вид доступен только из окна Конструктора по ко­ манде Вид/Образец и позволяет оценить внешний вид отчета, не вникая в подробности. Страничный вид дает возможность увидеть отчет таким, каким он будет после печати. Странич­ ное представление отчета можно получить из окна Конструк­ тора по команде Вид/Предварительный просмотр, а также из окна БД по команде Файл/Предварительный просмотр. Печать отчета можно выполнить из окна БД, из окна Кон­ структора или из окна предварительного просмотра с помо­ щью команды Файл/Печать. Установка параметров печати осуществляется перед печатью по команде Файл/Параметры страницы. Изменения в отчет вносятся в окне Конструктора. З а д а н и е 2 9 Создать и просмотреть ленточный в столбец автоотчет по данным двух таблиц: «Office», «Invoices». Для выполнения за­ дания использовать соответствующий запрос. В режиме Конст­ руктора в заголовок отчета вставить объект WordArt, используя Буфер обмена. Сохранить отчет под именем Отчет 1. З а д а н и е 3 0 Создать с помощью Мастера отчет по таблице «Customer». Выбрать для отчета поля Company, Address, City, State, Lat, Long. Задать группирование по полям State, City. Внутри груп­ пы City упорядочить данные по полю Company. По числовому полю Lat задать подведение итогов, по группе State — сумми- 37 рование, по числовому полю Long определить максимальное и минимальное значение в группе данных State. Отредактировать отчет в режиме Конструктора: выделить область примечаний группы City; вызвать контекстное меню, выбрать команду Сортировка и группировка; в появившемся диалоговом окне для поля City убрать строку Примечание группы, задав для него значение Нет. Сохранить отчет под именем Отчет 2. З а д а н и е 31 Построить отчет с помощью Мастера на базе запроса, по­ строенного по таблицам «Customer» и «Invoices», включающе­ го поля: Company, State, City, Lat, Long, Idate, Itotal. Данные за­ проса должны быть упорядочены по полю Company. При по­ строении отчета использовать данные предыдущего задания. В режиме Конструктора отредактировать отчет, выделив цветной рамкой заголовок группы — слово State — и итоговые данные по этой группе. Сохранить отчет под именем Отчет З. З а д а н и е 3 2 Построить многотабличный отчет с помощью Мастера по таблицам «Customer» и «Invoices», используя данные преды­ дущего задания. Подготовить в графическом редакторе лого­ тип фирмы, готовящей отчет. В режиме Конструктора вста­ вить логотип в заголовок отчета, убрать итоги по полю City, уменьшить размеры надписи и поля State, передвинуть их к левому краю области, в которой они находятся. Задать одина­ ковый цвет тексту поля State и итогам по этому полю. В раз­ дел Примечание отчета вставить элемент управления Кален­ дарь. Сохранить отчет под именем Отчет_4. З а д а н и е 3 3 В режиме Конструктор построить многотабличный отчет по таблицам «Office» и «Customer», используя для этого пред- 38 варительно созданный запрос, в который включены поля: Опо, City (из таблицы «Office»), Company, City, State, Ytdpurch, Lat, Long (из таблицы «Customer»). В отчет добавить новое вычис­ ляемое поле Formula = (Ytdpurch + Lat)/Long и сгруппировать его по полям Опо, City (из таблицы «Office»). Подвести итоги по группе и общие итоги по отчету для полей Long, Lat, Ytdpurch, Formula, найти минимальное, максимальное, сред­ нее значения и сумму для соответствующих полей. Для создания отчета необходимо: 1) открыть окно Конструктора отчета, не забыв указать ис­ точник отчета; 2) настроить окно для работы, включив (если не включены) линейки, сетку, панель элементов и все разделы; 3) задать заголовок отчета, для чего в раздел Заголовок от­ чета вставить элемент управления Надпись и ввести текст за­ головка “Отчет по офисам компании”; 4) спроектировать строки отчета: а) из окна со списком полей перетащить в раздел Область данных поля, значения которых должны выводиться в строках отчета; б) в разделе создать вычисляемое поле, значения которого будут выводиться в расчетных столбцах, для чего вставить свободное поле, используя элемент управления Поле, и ввести в него необходимое выражение; в) подписи всех полей переместить из раздела Область дан­ ных в раздел Верхний колонтитул; г) поля в разделе Область данных расположить в строку в нужном порядке, выровнять их по верхнему краю раздела и сделать интервалы между ними равными по горизонтали; 5) спроектировать шапку отчета: а) в разделе Верхний колонтитул подписи полей располо­ жить в строку над соответствующими полями; 6) названия столбцов выровнять по верхнему краю раздела; б) спроектировать частные итоги: а) в раздел Заголовок группы переместить названия и зна­ чения полей Опо, City (из таблицы «Office»); 39 6) в раздел Примечание группы вставить свободные поля, используя элемент управления Поле, и в них ввести выражения для подсчета частных итогов; подписи этих полей удалить; в) в строке слева ввести название для строк с частными итогами при помощи элемента управления Надпись; 7) спроектировать общие итоги: а) в раздел Примечание отчета вставить свободные поля, используя элемент управления Поле, и в них ввести выражения для подсчета общих итогов; подписи этих полей удалить; б) созданные вычисляемые поля расположить в строку под полями, по которым подводятся общие итоги, и выровнять их по верхнему краю раздела; в строке слева ввести название для строки с общими итогами при помощи элемента управления Надпись; 8) предусмотреть нумерацию страниц отчета, воспользо­ вавшись командой Вставка/Номера страниц; 9) произвести оформление отчета, например: а) в разделе Заголовок отчета провести нижнюю и верхнюю линии шапки отчета, используя элемент управления Линия; б) выделить разными шрифтами шапку отчета, вычисляемые поля для частных и общих итогов; 10) осуществить предварительный просмотр отчета; если отчет при просмотре соответствует заданию, сохранить и на­ печатать его, если нет, — модифицировать его, используя Конструктор отчета. З а д а н и е 3 4 Подготовить отчет для печати каждой группы на отдельной странице. Для выполнения задания воспользоваться Справкой. Тема 7. СТРАНИЦЫ ДОСТУПА К ДАННЫМ Страницы доступа к данным — специальный тип Web- страниц, предназначенный для просмотра и работы с данны­ 40 ми, хранящимися в базе данных MS Access, через Internet или Интрасеть. Страницы доступа к данным разрабатываются в MS Access, однако представляют собой отдельный HTML-документ, хра­ нящийся за пределами базы данных. При создании страницы доступа к данным MS Access автоматически добавляет ярлык на нее в окно базы данных. Таким образом, доступ к странице можно получить как из MS Internet Explorer (для работы с данными через компьютерную сеть), так и из MS Access. Разработка страниц доступа к данным аналогична разра­ ботке форм и отчетов. Страницы доступа к данным использу­ ются для просмотра, ввода и редактирования, наглядного ото­ бражения, анализа данных. Более подробно эта тема рассмат­ ривается в курсе “Сетевые технологии”. З а д а н и е 3 5 С помощью Мастера создать по таблице «Office» страницу доступа к данным и просмотреть ее в MS Internet Explorer. Тема 8. МАКРОСЫ Для автоматизации часто выполняемых операций служат макросы. Макрос — объект базы данных, представляющий со­ бой последовательность операций, собранных в виде инструк­ ций таким образом, чтобы их можно было выполнять, исполь­ зуя только одну команду. Макрос запускается, чтобы выпол­ нить всю последовательность необходимых действий, каждое из которых реализуется определенной макрокомандой. Созда­ ние макросов осуществляется путем выбора нужных макроко­ манд и задания дополнительных параметров, используемых ими при выполнении. Для запуска макроса на выполнение чаще всего использу­ ются формы. Макросы, как правило, автоматически выполня­ ются в ответ на определенное событие. Например, с помощью 41 макроса можно осуществить проверку значения какого-либо поля при изменении его содержимого, открыть форму, запрос, распечатать отчет или запустить другой макрос. Технология создания макросов в приложении Access отлича­ ется от технологии создания их в приложениях Excel, Word, PowerPoint. Макросы создаются в окне Макрос, для открытия которого в окне БД надо выбрать объекты Макросы и кнопку [Создать] в меню окна БД. Откроется окно создания макроко­ манд в режиме Конструктор, имеющее по умолчанию два столбца — Макрокоманда и Примечания. Макрокоманды можно ввести путем: 1) ввода их с клавиатуры; 2) выбора их имен из раскрывающегося списка; 3) перетаскивания объекта из окна БД в ячейку (при этом автоматически добавляется команда открытия объекта). Наиболее просто использовать второй или третий способ ввода макрокоманд. В списке Макрокоманды выбираются не­ обходимые макрокоманды, при этом в нижней части окна Макрос открываются аргументы, соответствующие выбран­ ной макрокоманде, указывающие, к какому объекту (таблица, запрос, форма, отчет) применяется данная макрокоманда, и задающие условия выполнения действия. Например, макрос должен открывать таблицу "Customer!'В списке макрокоманд надо отыскать команду ОткрытъТаблщу. Для этого доста­ точно набрать первую букву искомой макрокоманды, и список представит все команды, начинающиеся на эту букву, затем набрать вторую букву и т.д. Как только команда будет выбра­ на, внизу окна появится список аргументов этой команды: Имя таблицы, Режим, Режим данных. Для аргумента Имя таблицы надо задать имя Customer, для аргумента Режим — выбрать Таблицу, Конструктор или Просмотр (в нашем слу­ чае — Таблица), для аргумента Режим данных задать один из режимов: Изменение, Добавление, Только для чтения (в нашем примере — Добавление). Для работы с одним объектом БД могут понадобиться не­ сколько макросов, каждый из которых автоматизирует от­ 42 дельный процесс. Один и тот же отчет может быть нужен для использования в режиме просмотра, для печати, редактирова­ ния, выборки из отчета определенных записей с целью пере­ носа в таблицу Excel, в документ Word или в подготавливае­ мую презентацию в PowerPoint. Такие макросы могут быть созданы отдельно и запускаться по мере надобности. Можно объединить их в один макрос под общим именем, создав внутри него соответствующую группу макросов и присвоив каждой группе уникальное имя, которое записывается в от­ дельном столбце окна Конструктор макроса. Столбец выво­ дится при нажатии пиктограммы Имена макросов. З а д а н и е 3 6 Создать макрос, который открьюает таблицу Office, запрос Выбор 1, форму 5, используя перетаскивание объектов. Макрос размещает открытые окна вместе с окном БД в окне приложения Access по макрокоманде ВыполнитьКоманду. Способ размеще­ ния указывается в аргументе: РядомВертикально или РядомГо- ризонтально. З а д а н и е 37 Создать макрос под именем Объекты Свернуть; в нем соз­ дать макрогруппу Таблица, в которой открывается таблица «Customer», которая по команде Свернуть сворачивается. Соз­ дать макрогруппу Запрос, в которой открывается и сворачива­ ется запрос, выбранный исполнителями. Создать макрогруппу Форма, в которой открывается форма Форма_3. К ней приме­ няется фильтр Запрос ТХ, в котором данные таблицы Customer выбраны для штата ТХ. Создать макрогруппу Горизонт, в ко­ торой выполняется команда, располагающая в окне Access от­ крытые окна по горизонтали. Запуск макрогрупп осуществля­ ется после выполнения команды Сервис/Макрос/Выполнить макрос... Запустить все макрогруппы. Отредактировать мак­ 43 рогруппу Таблица, поставив первой команду Свернуть. Запус­ тить макрогруппу. Для чего была использована эта команда? З а д а н и е 3 8 Создать макрос под именем Отчеты, а в нем - макрогруп­ пу Просмотр, в которой Отчет ! открывается в режиме Просмотр. Создать макрогруппу Печать для печати отчета Отчет 1; макрогруппу Редактор, в которой должен редакти­ роваться Отчет_2; макрогруппу Выбор для выборки из От­ чет _3 записей, относящихся к штатам Мичиган (MI) и Нью- Йорк (NY). Перенести отобранные данные в таблицу'ЕхсеГ,' и по ним построить диаграмму. З а д а н и е 3 9 В форме Cust_s_inv создать кнопку, открывающую создан­ ную в задании 6 (тема 5) гистограмму, для чего необходимо создать макрос, открывающий форму Диаграмма. Кнопку разместить в области Примечание формы, для чего на панели элементов отключить кнопку [Мастер], нажать элемент Кноп­ ка и в области Примечание формы нарисовать курсором мы­ ши кнопку. В свойствах кнопки задать выполнение созданно­ го макроса при нажатии кнопки, для чего надо выделить кнопку, в пиктографическом меню выбрать пиктограмму Свойства, в диалоговом окне Кнопка на вкладке Макет в строке Подпись задать надпись на кнопке — Диаграмма. На вкладке События, в строке Нажатие кнопки выбрать имя макроса, открывающего форму Диаграмма. С помощью Мас­ тера создать кнопку Назад для закрытия формы Диаграмма. 44 1. Бекаревич, Ю.Б., Пушкина, Н.В. MS Access 2002. - СПб.: БХВ-Санкт-Петербург, 2002. 2. Вейскас, Дж. Эффективная работа с Microsoft Access 2000. - СПб: Издательство «Питер», 2000. 3. Экономическая информатика: учебник/ Под ред. П.В. Ко- нюховского, Д.Н. Колесова. - СПб.: Питер, 2000. 4. Гончаров, A. Access 97 в примерах. - СПб.: Питер, 1997. 5. Оскерко, B.C. Технологии организации, хранения и об­ работки данных. - Мн.: БГЭУ, 2002. 6. Основы компьютерных технологий в образовании: В 3 ч. Ч. 3. Технологии обработки данных: учеб. пособие / Г.М. Троян [и др.]; под ред. Г.М. Троян. - Мн.: РИВШ БГУ, 2002. Л и т е р а т у р а 45 С о д е р ж а н и е В в е д е н и е ........................................................................... 3 Справочная система Access...................................................... 5 Объекты базы данных и их размещение................................ 5 Основные термины и определения......................................... 7 Тема 1. ПРОЕКТИРОВАНИЕ БАЗЫ ДАННЫХ.................. 9 Тема 2. СОЗДАНИЕ И КОРРЕКТИРОВКА БАЗЫ ДАН­ НЫХ В СИСТЕМЕ УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ MS ACCESS 2000 ...................................................................... 13 2.1. Пользовательский интерфейс Access.............................. 13 2.2. Создание таблицы............................................................. 14 Тема 3. СОЗДАНИЕ СХЕМЫ ДАННЫХ.............................. 19 Тема 4. СОЗДАНИЕ И ИСПОЛЬЗОВАНИЕ ЗАПРОСОВ ... 20 Тема 5. ФОРМЫ В ПРИЛОЖЕНИИ ACCESS........................ 27 5.1. Назначение и способы проектирования форм............... 27 5.2. Инструментальные средства конструкторов форм........29 5.3. Элементы управления и работа с ними.......................... 30 5.4. Рекомендации для создания формы................................ 32 5.5. Работа с формой................................................................ 32 Тема 6. ОТЧЕТЫ В ПРИЛОЖЕНИИ ACCESS.....................35 6.1. Создание отчета................................................................ 35 6.2. Работа с отчетом............................................................... 37 Тема 7. СТРАНИЦЫ ДОСТУПА К ДАННЫМ................... 40 Тема 8. МАКРОСЫ..................................................................41 Л и т е р а т у р а ...................................................................45 Учебное издание СИСТЕМА УПРАВЛЕНИЯ БАЗАМИ ДАННЫХ ACCESS Лабораторный практикум для студентов специальности 1-27 01 01 «Экономика и организация производства» Составители: ГУРЬЕВА Ольга Олеговна ГУСЕВА Лариса Петровна Редактор Т.А. Палилова Компьютерная верстка Л.Н. Юргилевич Подписано в печать 19.10.2005. Формат 60x84 1/16. Бумага офсетная. Отпечатано на ризографе. Гарнитура Таймс. Уел, печ. л. 2,8. Уч.-изд. л. 2,2.Тираж 100. Заказ 28. Издатель и полиграфическое исполнение: Белорусский национальный технический университет. ЛИ № 02330/0056957 от 01.04.2004. 220013, Минск, проспект Независимости, 65.