лабораторная работа  № 6. Цель работы. Общее задание

1. Запустите программу Microsoft Access (меню Пуск | Программы | Microsoft Access ).

После запуска Access на экране отображается стандартное окно приложения (рис. 1), вид которого определяется установками, сделанными на вкладке Вид диалогового окна команды Параметры (Options) в меню Сервис (Tools). На этой вкладке можно установить флажок область задан при запуске (Startup Task Pane), что обеспечит после запуска Access показ в правой части окна области задач Приступая к работе. Ссылки в этой области задач позволяют подключиться к веб-узлу Microsoft Office Online, получить последние сведения об использовании Access, открыть файлы, с которыми вы работали ранее или создать файл.

Рис. 1. Стандартное окно MS Access.

2. Укажите в открывшемся диалоговом окне, что вы хотите создать новую базу данных (рис. 2). Если подобное диалоговое окно вам предложено не было, то вызовите его, дав команду меню " Файл > Создать"

Рис. 2. Создание новой базы данных

Вам будет предложено задать имя файла создаваемой базы данных (выберите свой каталог и укажите имя файла, например, «Учет заказов»).

Обратите внимание, что в MS Access, имя файла задается ДО создания новой базы, а не ПОСЛЕ того, как вся работа проделана и остается только сохранить результат (как, например, в MS Word или Excel). Сделано это из соображений обеспечения сохранности данных. Все изменения, вносимые в базу данных, сразу же отображаются и в ее файлах (а поэтому программе с самого начала надо знать их имена). Таким образом, сводится к минимуму риск потери важных данных даже при непредвиденных сбоях (например таких, как отключение электропитания).

Перед вами откроется окно " База данных" (см. рис. 3). Это окно является исходным элементом управления Microsoft Access.

Рис. 3. Окно " База данных"

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

3. Проектирование базы данных.

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

1. Номер заказа

2. Код клиента

3. Наименование клиента

4. Адрес клиента

5. Код продукта

6. Название продукта

7. Количество

8. Дата поставки

9. Цена

10. Стоимость

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

Если таблица со сведениями о заказах будет оформлена в виде единой таблицы Microsoft Access, то это будет не самым лучшим вариантом организации данных. Например, значения полей " Наименование клиента" и " Адрес клиента" полностью определяются полем " Код клиента" и в базе будет наблюдаться дублирование информации. Может произойти и потеря информации: при удалении записей о заказах будут удаляться и сведения о клиентах.

Аналогичная ситуация складывается и с полями " Код продукта", " Название продукта", " Цена". Кроме того, значение поле " Стоимость" является произведением цены на количество, поэтому данное поле можно вообще не включать в таблицу, при необходимости его следует вычислять.

Таким образом, имеет смысл организовать рассматриваемую нами базу данных в виде трех связанных таблиц:

1. Классификатор " КЛИЕНТЫ" (" Код клиента", " Наименование клиента", " Адрес клиента" );
2. Классификатор " ПРОДУКТЫ" (" Код продукта", " Название продукта", " Цена" );
3. " ЗАКАЗЫ" (" Номер заказа", " Код клиента", " Код продукта", " Количество", " Дата поставки" ).

Поля " Код клиента" и " Код продукта" таблицы " ЗАКАЗЫ" должны быть связаны с соответствующими полями таблиц " КЛИЕНТЫ" и " ПРОДУКТЫ".

3. 1. Создание таблиц.

 Запустите конструктор создания таблиц, сделав двойной щелчок по соответствующему элементу управления или нажав кнопку " Конструктор" на панели инструментов. Перед вами откроется окно проектирования структуры таблицы (см. рис. 4). Создайте таблицу " КЛИЕНТЫ".

Рис. 4. Окно проектирования структуры таблицы

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

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

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

Укажите ключевое поле (в нашем примере поле " Код клиента" ). Это можно сделать через контекстное меню (нажав правой кнопкой мыши на строке соответствующего поля и выбрав пункт " Ключевое поле" выпавшего меню.

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

Сохраните в текущей базе данных созданную таблицу. Сделать это можно с помощью команды меню " Файл > Сохранить" или с помощью кнопки панели инструментов Microsoft Access. Укажите имя сохраняемой таблицы: " КЛИЕНТЫ".

Закройте окно проектирования структуры таблицы и обратите внимание на то, что в окне " База данных" появился новый элемент - только что созданная таблица " КЛИЕНТЫ ". Обратите также внимание, что созданная таблица была сохранена не в виде отдельного файла на диске, а в структуре базы данных.

Создайте с помощью конструктора таблицы " ПРОДУКТЫ" и " ЗАКАЗЫ". Подумайте о назначении ключевых полей в таблицах, типах данных и размерах полей, дополнительных параметрах (необходимости указания значений по умолчанию, признаков обязательности и т. п. ).

3. 2. Настройка связей между таблицами.

В структуре нашей базы данных уже есть 3 таблицы. Однако, они пока существуют независимо и наша цель сделать так, чтобы в таблице " ЗАКАЗЫ" вместо кодов подставлялись их полные наименования из соответствующих таблиц " КАТЕГОРИИ". Для этого необходимо настроить подстановочные параметры полей " Код клиента" и " Код продукта", а также задать параметры связей между таблицами.

Откройте таблицу " ЗАКАЗЫ" в режиме конструктора (переход к конструктору из режима просмотра таблицы или из списка таблиц осуществляется нажатием кнопки ), перейдите к типу данных поля " Код клиента" и из раскрывающегося списка выберите " Мастер подстановок". Перед вами откроется диалоговое окно, где вам будет предложен ряд вопросов о параметрах настраиваемого подстановочного поля (см. рис. 5).

Рис. 5. Диалоговое окно мастера подстановок

Укажите следующие параметры (на каждом шаге нажимайте кнопку " Далее" ):

· Объект " столбец подстановки" будет использовать значения из таблицы или запроса

· Значения будут выбираться из таблицы " КЛИЕНТЫ"

· В столбец подстановки включить поле " Наименование клиента" (надо выделить это поле и кнопкой переместить его из окна доступных полей в окно выбранных полей)

· Оставить предложенные параметры ширины столбцов и скрытия ключевого столбца

· Оставить подпись " Код клиента" для подстановочного столбца

После нажатия кнопки " Готово" вам будет выдано предупреждение о необходимости сохранить таблицу. Согласитесь с этим.

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

Аналогичные действия произведите для поля " Код продукта"

Закройте окно проектирования таблицы " ЗАКАЗЫ".

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

Откройте окно " Схема данных" (см. рис. 6). Для этого надо нажать кнопку на панели инструментов Microsoft Access или выбрать пункт меню " Сервис > Схема данных".

Рис. 6. Окно " Схема данных".

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

Добавьте в схему данных таблицы " ЗАКАЗЫ",  " КЛИЕНТЫ" и " ПРОДУКТЫ". Закройте окно добавления таблиц.

В окне " Схема данных" отображаются окошки со списками полей выбранных таблиц. Связи между полями отображаются в виде линий. При необходимости, здесь же можно и создавать новые связи между полями. Это делается перетаскиванием мышью имени одного поля на имя другого поля. Между данными полями устанавливается связь и сразу же предлагается настроить ее параметры. Удалить связь можно выделив ее мышью и нажав клавишу DEL на клавиатуре (или дав команду меню " Правка > Удалить" ). Таким же образом удаляются и лишние таблицы из окна схемы данных.

Случайно добавленные таблицы или таблицы не нужные в данной схеме могут быть удалены из схемы нажатием правой кнопки и выбором команды «Скрыть таблицу».

Откройте окно " Изменение связей" для настраиваемой связи (см. рис. 7). Это можно сделать через контекстное меню линии связи или выделив линию связи щелчком мыши и дав команду меню " Связи > Изменить связь".

Рис. 7. Окно " Изменение связей"

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

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

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

Сохраните макет схемы данных, дав команду меню " Файл > Сохранить" или нажав кнопку на панели инструментов. Закройте окно " Схема данных".

4. Заполнение базы данных информацией

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

Заполните таблицы следующими данными:

КЛИЕНТЫ

ПРОДУКТЫ

Таблицу " ЗАКАЗЫ" заполните по своему усмотрению (10-30 записей, желательно чтобы у каждого заказчика были заказы разных продуктов, и, наоборот, у каждого продукта было несколько заказчиков).

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

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

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

5. Выполнение простейших запросов

Предположим, что для повседневной работы с базой " Учет заказов" нам не требуется вся информация. В частности, более удобным представляется вариант работы с результирующей таблицей, в которую включены все поля таблицы " ЗАКАЗЫ", за исключением полей " Код клиента" и " Код продукта", а также дополнительное (вычисляемое) поле " Стоимость". Предоставим пользователю возможность работы с такой таблицей. Сделать это можно с помощью запросов.

Откройте окно " База данных" и переключитесь на вкладку объектов " Запросы". Выберите создание запроса в режиме конструктора. Вам будет предложено добавить таблицы. Сделайте это и закройте окно добавления таблиц. Перед вами откроется бланк запроса (рис. 8).

Рис. 8. Бланк запроса по образцу

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

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

Перетащите поля " Имя", " Номер", " Наименование клиента", " Адрес клиента", " Наименование продукта", " Цена" и " Количество" в формируемую таблицу. Создайте также вычисляемое поле " Стоимость".  

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

Стоимость: [Количество]*[Цена]

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

Сохраните составленный запрос (назовите, например, " Все заказы" ) и закройте бланк запросов. Чтобы посмотреть результаты работы запроса, откройте его, сделав двойной щелчок по соответствующей записи в окне " База данных".

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

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

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

К основным групповым операциям можно отнести суммирование (Sum), поиск минимального, максимального и среднего значений (Min, Max и Avg), определение количества записей (Count).

Для решения поставленной задачи, необходимо составить запрос, который будет выполняться на основе анализа полей " Название продукта", " Количество"  и " Стоимость" составленного ранее запроса " Все заказы". Группировку следует сделать по полю " Название продукта", а к полям " Стоимость" и " Количество" следует применить операцию суммирования (рис. 9).

Рис. 9. Итоговый запрос.

В создаваемых запросах в качестве источника данных могут использоваться запросы, созданные ранее. Так, например, можно использовать запрос с ранее посчитанной стоимость заказов. При этом добавленный запрос должен связаться с таблицей «ЗАКАЗЫ» по полю «Код заказа».

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

создайте новый запрос так, чтобы он показывал сведения не за весь период работы фирмы, а лишь за некоторое время (например, за последний год, месяц или день). (Используйте параметр " Условие отбора" в бланке запросов по образцу).

6. Формы и отчеты

Таблицы и запросы обеспечивают не только хранение и обработку информации в базе данных, но и позволяют пользователям выполнять базовый набор операций с данными (просмотр, пополнение, изменение, удаление). Вместе с тем, в Microsoft Access существуют специальные объекты, которые призваны упростить повседневную работу с базой данных. К таким объектам относят, в первую очередь, формы и отчеты.

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

Создадим форму, с помощью которой будет удобно вводить новые записи в базу. Откройте окно " База данных" и переключитесь на вкладку объектов " Формы". Выберите создание формы с помощью мастера. Перед вами откроется диалоговое окно, в котором будет необходимо ответить на ряд вопросов. Укажите следующие параметры создаваемой формы (на каждом шаге нажимайте кнопку " Далее" ):

• Форма строится на основе таблицы " ЗАКАЗЫ". В форму необходимо включить все поля таблицы.
• Внешний вид формы - " в один столбец".
• Требуемый стиль - по вашему усмотрению.
• Имя формы - " Заказы"

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

Закройте созданную форму и откройте ее в режиме конструктора. Перед вами откроется макет формы, а также панель элементов, содержащая заготовки и инструменты для создания элементов управления формы (рис. 10).

Рис. 10. Макет формы " Заказы". Добавление заголовка.

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

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

Рис. 11. Форма " Заказы".

¾ Самостоятельно создайте формы для добавления клиентов и продуктов.

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

Откройте запрос " Все заказы" и в поле " Условие отбора" укажите (именно так, без кавычек и в квадратных скобках): [Введите наименование клиента]. Сохраните файл как “Выбор фирмы”. Запустите запрос на выполнение и протестируйте его.

В окне " База данных" и переключитесь на вкладку объектов " Отчеты". Выберите создание отчета с помощью мастера. Укажите следующие параметры создаваемого отчета:

• Отчет строится на базе запроса " Выбор фирмы ". Необходимо выбрать все доступные поля.
• Уровни группировки - не добавлять.
• Порядок сортировки - по наименование клиента.
• Макет для отчета - " табличный".
• Ориентация –альбомная.
• Стиль отчета - по вашему усмотрению.
• Имя отчета - " Отчет по клиентам".

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

Рис. 12. Отчет по клиентам.