Практическая работа по MS Access «Туризм»

Практическая работа №1

Тема: Создание таблиц в СУБД MS Access.

Цель: - изучение технологии создания базы данных в системе управления базами данных (СУБД) Microsoft Access.

Вид работы: фронтальный

Время выполнения: 4 часа

Теоретические сведения

База данных (БД) - упорядоченная совокупность данных, предназначенных

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

MS Access – это система управления базами данных (СУБД). Как и другие программные продукты этой категории, Access предназначен для хранения и получения данных, представления их в удобном виде и автоматизации часто выполняемых операций (например, для ведения счетов, учета материальных ценностей, планирования и т.п.).

С другой стороны, Access является мощным приложением Windows. Таким образом, все преимущества Windows доступны в Access. В то же время Access – это реляционная СУБД. Это означает, что с помощью Access можно получить доступ к любым данным любого типа и использовать одновременно несколько таблиц базы данных. Использование реляционной СУБД позволяет упростить структуру данных и, таким образом, облегчить выполнение работы.

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

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

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

Запрос представляет собой обращение к данным для получения информации и выполнения действий с данными.

Существуют два основных типа запросов:

1.     Запросы на выборку - это запросы, выполняющие извлечение данных, предназначенных для отображения.

2.     Управляющие запросы - так называют сохраненные процедуры, выполняющие вставку, изменение или удаление данных. Эти запросы позволяют создавать новые таблицы базы данных из итоговых таблиц запросов или вносить значительные изменения в уже имеющиеся таблицы.

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

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

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

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

3.       Перекрестные запросы - суммируют данные из одной или нескольких связанных таблиц и формируют новую электронную таблицу. Такие запросы предназначены для задач анализа данных или создания графиков и диаграмм, основанных на сумме значений числовых полей многих таблиц. Например, доходов по месяцам или неделям.

Способы создания запросов:

1) с помощью мастера запросов,

2) с помощью Конструктора запросов.

В данной лабораторной работе рассматриваются запросы на выборку.

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

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

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

1) при работе с формами ограничен доступ к таблицам (самому ценному в базе данных);

2) разные пользователи могут иметь разные права доступа к информации, хранящейся в базе. Для ввода данных им предоставляются разные формы, хотя данные из форм могут поступать в одну таблицу;

3) вводить данные в форму легче, чем в таблицу, и удобнее, так как в окне формы отображается, как правило, одна запись таблицы;

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

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

Способы создания форм:

1) с помощью Мастера,

2) используя средство Автоформы,

3) «вручную» в режиме Конструктора,

4) сохраняя таблицу или запрос как форму.

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

Рассмотрим некоторые из перечисленных способов.

В Microsoft Office Access 2007 доступен ряд средств, которые помогают быстро создавать привлекательные, наглядные отчеты, где данные представлены в наиболее удобном для пользователей этих отчетов виде.

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

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

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

Создавать отчеты в базе данных Access можно несколькими способами:

1) с помощью Мастера отчетов;

2) на основе таблиц или запросов;

3) в режиме Конструктора.

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

Структура отчета в режиме Конструктора

Microsoft Access отображает в отчете данные из запроса или таблицы, добавляя к ним текстовые элементы, которые упрощают его восприятие.

К числу таких элементов относятся:

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

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

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

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

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

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

Задания к практической работе

Задание 1. С помощью «Конструктора создания таблиц» по образцу создать БД «Фирма» состоящую из трех таблиц: «Сотрудники», «Клиенты», «Заказы».

Ход работы

1.     Запустите Microsoft Access 2007 (Пуск – Все программы – Microsoft Office – Microsoft Office Access 2007).

2.     Создайте базу данных «Туризм».

3.     Нажмите на кнопку .

4.     Задайте имя новой базы данных – «Туризм». Сохраните файл в личной папке. Обратите внимание, файл базы данных сохраняется с расширением имени .accdb.

5.     Создайте таблицу Сотрудники. На вкладке ленты Создание в группе Таблицы нажмите на кнопку Конструктор таблиц. В открывшейся форме введите имена полей и укажите типы данных, к которым они относятся, согласно таблице 1.

Таблица 1 - Структура таблицы Сотрудники

6.     Для поля Домашний телефон задайте маску, набрав, например, следующий шаблон (999) 999-99-99.

7.     Для поля Размер оклад задайте условие, что он больше 5000 р., но не больше 10000. Для этого в свойстве «Условие на значение» установите (>5000) АND (< 10000). Предусмотрите выдачу сообщения при ошибке ввода данных.

8.     Установите для Даты рождения и Даты найма краткий формат даты.

9.     Создайте первичный ключ, используя поле Табельный номер (Код сотрудника).

10. Сохраните таблицу под именем Сотрудники.

11. Создайте таблицу Клиенты, структура которой представлена в таблице 2.

Таблица 2 - Структура таблицы Клиенты

12. В качестве первичного ключа задайте Код Клиента.

13. Создайте таблицу Страны, структура которой представлена в таблице 3.

Таблица 3 - Структура таблицы Страны

14. В качестве ключевого поля задайте Код Тура.

Задание 2. Связи между таблицами.

1.         Связать таблицы «Сотрудники», «Страны» и «Клиенты» с таблицей «Договоры».

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

Таблица 4 - Структура таблицы Договоры

3.         Поля Код сотрудника, Код клиента, Код тура являются полями подстановки. Для их задания используется Мастер подстановок.

Для подстановки Кода сотрудника нужно в режиме Конструктора:

>• в Типе данных поля Код сотрудника раскрыть список типов и выбрать Мастер подстановок;

>• указать, что столбец подстановки получает свои значения из таблицы Сотрудники;

>• выбрать поля Код сотрудника и Фамилия;

>• установить мышью подходящую ширину столбца;

>• согласиться с предлагаемой подписью столбца подстановок Фамилия;

>• сохраните таблицу с именем Договоры.

Аналогично для подстановки Кода клиента и Кода тура вызывается Мастер подстановок. При этом для Кода клиента выбираем поля Код клиента и Название клиента из таблицы Клиенты, а для Кода тура — поля Код тура и Страна из таблицы Страны.

4.         Закройте все открытые таблицы, так как создавать, или изменять связи между открытыми таблицами нельзя.

5.         Выполните команду: вкладка ленты Работа с базами данных - кнопка

Если ранее никаких связей между таблицами базы не было, то при открытии окна Схема данных одновременно открывается окно Добавление таблицы, в котором выберите таблицы Сотрудники, Клиенты, Страны и Договоры.

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

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

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

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

Рисунок 1 - Создание связи между таблицами

10.      Флажки Каскадное обновление связанных полей и Каскадное удаление связанных записей обеспечивают одновременное обновление или удаление данных во всех подчиненных таблицах при их изменении в главной таблице.

11.      Параметры связи можно изменить, нажав на кнопку Объединение.

12.      После установления всех необходимых параметров нажмите кнопку ОК.

13.      Связь между таблицами Клиенты и Договоры, Страны и Договоры установите самостоятельно.

14.      В результате должна получиться схема данных, представленная на рисунке 2.

Рисунок 2 - Схема данных

В приведенном примере используются связи «один - ко - многим». На схеме данных они отображаются в виде соединительных линий со специальными значками около таблиц. Связь «один - ко - многим» помечается «1» вблизи главной таблицы (имеющей первичный ключ) и «∞» вблизи подчиненной таблицы (имеющей внешний ключ). Связь «один - к - одному» помечается двумя «1» (оба поля таблиц имеют первичные ключи). Неопределенная связь не имеет никаких знаков. Если установлено объединение, то его направление отмечается стрелкой на конце соединительной линии (ни одно из объединенных полей не является ключевым и не имеет уникального индекса).

15.      Закройте Схему данных.

16.      Откройте таблицу Сотрудники двойным щелчком мыши и заполните в ней 10 строк произвольными данными.

17.      В таблицу Клиенты внесите данные о десяти предприятиях, с которыми работает данная турфирма.

18.      В таблице Страны оформите 10 строк.

19.      В таблице Договоры оформите 10 строк.

Задание 3. Создание запросов

Запрос на выборку

20.      Перейдите на вкладку Создание - Запрос.

21.      В режиме Конструктора создайте и сохраните следующие запросы на выборку, определив нужные таблицы:

P список всех путешествий в определенную страну (например, Испанию);

P список всех регионов в конкретной стране (например, Англии). Сохраните запрос под именем «Страна-Регион»;

P все туры, проданные в *.*.20?? году. Сохраните запрос с именем «Туры 20??»;

P список сотрудников, работающих с 1995 года и раньше. Сохраните запрос с именем «Ветераны». Добавьте в запрос строку «Сортировка» и установите сортировку по фамилиям.

22.      Опробуйте инструмент Построить при построении следующих запросов в сочетании с вводом критериев поиска вручную. Создайте запросы для извлечения данных по:

P сотрудникам, которые родились в 1973 г., используя в качестве критерия выражение: Between... and (Построить àОператоры àСравнения àВеtwееп), а затем повторите запрос, построив выражение с помощью знаков «<» и «>»;

P сотрудникам, фамилии которых с «Г» по «Я»;

P сотрудникам, фамилии которых начинаются с «И» по «Я» и с «А» по «В»;

P индивидуальным клиентам, фамилии которых имеют вторую букву «о»;

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

P постоянным клиентам, количество договоров с которыми больше 3.

Запросы с вычисляемыми полями

23.      Создайте запрос для расчета ведомости заработной платы для сотрудников агентства, включив в нее следующие поля: Фамилия сотрудника, Размер оклада, Стаж, Надбавка, Налог, На руки.

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

Стаж : (Date()-Сотрудники!ДатаНайма)/365

Для поля Надбавка нужно исходить из того, что она составляет 10% от Размера оклада, если Стаж меньше 5лет, и 20% — если стаж больше 5 лет: IIf([стаж]< 10;0,1 *[Сотрудники]![Размер оклада]; 0,2* [Сотрудники]! [Размер оклада])

Поле Налог рассчитывается как 13% от Размера оклада:

[Сотрудники]![Размер оклада] *0,13

Поле На руки рассчитывается как:

[Размер оклада]+[надбавка]-[налог].

В результате выполнения запроса будет получена ведомость:

24.      Создайте запрос для определения стоимости путевок корпоративных клиентов, включив в него поля Клиент, Стоимость путевки: Sum(договоры![Цена тура]*договоры![Число туристов])

Параметрические запросы

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

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

27.      Создайте запрос по всем клиентам, оформившим договоры в определенную страну и регион.

Итоговые запросы

28.      Создайте запрос, используя подходящие функции, найдите наибольший и средний размеры цены тура.

29.      Создайте запрос для подсчета объема продаж: путевок в конкретную страну. Для этого:

P добавьте в Конструкторе запросов таблицу Договоры и Страны;

P добавьте в бланк запроса поля Название страны (из таблицы Страны) и расчетное поле Цена тура * Число туристов, которому присвоим название Стоимость путевок;

P выберите команду Вид →Групповые операции и в выпадающем списке в строке «Группировка» для поля Стоимость путевок установите функцию SUM;

P запустите запрос и просмотрите результаты.

30.      Создайте запрос для определения средней цены и общей суммы туров за 200? год (год туров вашей БД).

31.      Для объединения записей в группы и получения итоговых значений по каждой группе используется опция «Группировка». Создайте новый запрос для БД Туризм, в котором определите общие суммы продаж путевок по годам:

P добавьте таблицу Договоры в окно запроса;

P в первый столбец поместите поле Год начала тура, рассчитав его с помощью функции Year, во второй — сумма общих продаж путевок — Sum(договоры![Цена тура]*договоры![Число туристов]);

P установите для первого столбца в строке «Групповая операция» — «Группировка», для второго — Выражение;

P выполните запрос и прокомментируйте результаты.

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

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

32.      Составьте запрос для выяснения: сколько туров организовано в каждую страну в конкретный регион.

33.      Составьте перекрестный запрос по теме: сколько туров начались в июле 2006 г. в разные страны.

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

Задание 4. Модификация БД с помощью запросов на изменение

Запрос на создание

35.      Создайте обобщенную таблицу Договоры по странам, включив в нее следующие поля:

Из таблицы Договоры: Номер договора

Название клиента

Из таблицы Страны: Название страны;

Регион.

Для этого:

P создайте запрос на выборку этих данных, выполните его и проверьте результаты;

P если результаты корректны, то поменяйте статус у запроса: Запрос — Создание таблицы — укажите новое имя таблицы Договоры по странам;

P выполните запрос с новым статусом еще раз;

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

Запрос на обновление

36.      Увеличьте Размер оклада у менеджеров по продажам на 15%. Для этого:

P составьте новый запрос на выборку, включив в него поля Фамилия, Должность и Размер оклада;

P проверьте составленный запрос;

P видоизмените запрос, установив ему статус «Обновление» (Запрос — Обновление). В появившейся в бланке запроса строке «Обновление» для поля Размер оклада внесите с помощью Построить выражение

[Размер оклада]* 1,15

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

Запрос на добавление

37.      Создайте путем копирования дубликат таблицы Договоры без данных, назвав ее Договоры 2005 года. Для этого в контекстном меню для таблицы Договоры выберите Копировать, затем выполните команду Вставить, в параметрах вставки укажите «Только структуру». Просмотрите таблицу Договоры 2005 года — она должна быть пустой и иметь такую же структуру, как и таблица Договоры.

38.      Отберите в таблицу Договоры 2005 года записи обо всех договорах этого года. Для этого:

P создайте запрос на выборку, включив в него все поля таблицы, Договоры в любой последовательности, и критерий по дате, выполните его для проверки правильности;

P измените, статус запроса на «Добавление», в появившемся окне задайте имя таблицы для добавления Договоры 2005 года, обратите внимание на появление строки «Добавление» в бланке запроса;

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

Запрос на удаление

39.      Удалите из таблицы Договоры записи о договорах 2005 года, используя копию сохраненного запроса на добавление в таблицу Договоры 2005 года, изменив его статус на «Удаление».

Задание 5. Работа с формами

Создание формы с помощью Мастера

40.      Выберите на вкладке Создание выберите Другие формы – Мастер форм для таблицы Клиенты создайте Автоформу. Оцените результаты.

41.      Зарегистрируйте новые договоры, используя кнопку со звездочкой введите 1-2 новые записи.

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

43.      Выберите фон, на котором будут размещаться поля формы, перебрав в окне Мастера несколько вариантов оформления.

44.      Завершите проектирование формы с помощью Мастера.

45.      Перейдите в режим Конструктора. Вставьте Заголовок формы инструментом .

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

47.      Произведите сортировку данных по Дате начала тура. Для это­го в режиме Форма установите курсор в это поле и выполните команды Записи →Сортировка.

Создание формы с помощью Конструктора форм

48.      Создайте форму для таблицы Договоры в режиме Конструктора форм (см. на образец). Для этого:

P кнопка Создать в окне БД →Конструктор — на основе табли­цы Договоры;

P увеличить поле формы, растянув его за уголок;

P перетянуть каждое поле из окна Списки полей в область формы (если Списка полей нет на экране, то можно его активизиро­вать с помощью команды Вид →Список полей);

P разместить поля в соответствии с образцом;

P добавить на форму некоторые дополнительные элементы, ис­пользуя панель элементов: прямоугольники различных типов оформления, заголовок формы и др.

Рисунок 3 - Список полей формы

Рисунок 4 - Форма Договоры в режиме Конструктора

49.      Измените размеры нескольких полей с помощью команды Формат →Размер. Задайте группе полей одинаковые размеры, например По самому широкому.

50.      Задайте текст сообщения в строке состояния, которое будет появляться в момент ввода информации в поле (например, Дата окончания тура). Для этого введите текст «Окончание тура в день вылета до 12 часов» в строке «Текст строки состояния» (контекстное меню поля Дата окончания тура -Свойства - вкладка Другие — «Текст строки состояния»). Проверьте в режиме формы, появляется ли в строке состояния заданный текст при активизации этого поля.

51.      Задайте всплывающую подсказку «Номер договора не должен повторяться» для поля Номер договора (Свойства — вкладка Другие — «Всплывающая подсказка»).

52.      Добавьте любую картинку в заголовок.

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

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

=[Цена тура]*[Число туристов]

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

55.      Для БД Туризм постройте подчиненные формы для таблиц Сотрудники (отношение «один») и Договоры (отношение «много»).

Рисунок 5 - Форма Сотрудники-Договоры

56.      Постройте подчиненную форму для таблиц Клиенты (отношение «один») и Договоры (отношение «много»).

Задание 6. Применение отчетов для наглядного отображения данных

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

58.      Составьте для БД Туризм отчет, в котором указаны фамилии и сотрудников, а для каждого сотрудника — договоры, которые он заключил, сведения о клиенте, стоимости тура. Сохраните отчет с именем «Отчет по сотрудникам».

59.      Создайте отчет «Общая стоимость договоров по странам», в котором будут использованы вычисляемые поля. Для этого следует выполнить указанные ниже действия:

P создайте с помощью Мастера отчет для таблицы Договоры и произведите группировку по полю Код тура.

P откройте этот отчет в режиме Конструктора;

P подведите итог по каждой группе (по каждому туру). Для этого:

ÿ  добавьте область итогов по группе в отчет Вид — Сортировка/Группировка — Примечание группы — Да;

ÿ  в появившейся области «Примечания группы Код тура» инструментом  вставьте новое поле;

ÿ  в свойствах названия поля укажите подпись «Общая сумма договоров»;

ÿ  в свойствах поля Все — Данные с помощью Построить сформируйте формулу: =sum([Цена тура]*[Число туристов])

ÿ  на этой же вкладке установите Формат поля Основной.

60.      Задайте в макете Цвет текста — яркий.

61.      Просмотрите полученный отчет.

62.      Измените отчет для подсчета общих итогов по всем сотрудникам.

63.      На основе таблицы Сотрудники создайте отчет с группировкой по годам рождения сотрудников: каждое десятилетие должно образовывать группу (1950-1959, 1960-1969 и т.д.).

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

Возраст-=Round(((Date()-[ДатаРождения])/365)).

В разделе Заголовок группы поместить:

Годы рождения с [МинГр] по [МаксГр],

где «Годы рождения с» и «по» — надписи

[МинГр] и [МаксГр] — вычисляемые поля:

[МинГр]=(Min(DatePart(«уууу»;[ДатаРождения]))/10)*10

DatePart((«уууу»;[ДатаРождения]) — возвращает год даты,

Min() — возвращает минимальный год рождения в группе.

Целочисленное деление и последующее умножение на 10 дает значе­ние года, кратное 10.

[МаксГр] =[МинГР]+9

64.      Создайте почтовые наклейки на основе таблицы Клиенты. Включите в наклейки поля Фамилия и Адрес клиента, а также произвольный текст и оформление.

Задание 7. Экспорт и импорт данных

Экспорт данных в Ехсеl

65.      В БД Туризм выделите таблицу Сотрудники.

66.      Экспортируйте эту таблицу в файл типа Ехсе1. Для этого в команде Файл →Экспорт в поле Тип файла выберите из предлагаемого списка «Microsoft Excel».

67.      Запустите Excel и откройте полученный файл. При необходимости поменяйте шрифт. Обратите внимание на наличие установленного системой примечания в первой ячейке. Измените подпись рабочего листа на «Адреса».

Импорт данных в Access

68.      Создайте в Excel на основе таблицы Адреса новую таблицу Картотека адресов, исключив из таблицы Адреса поля Код сотрудника, Должность, Размер оклада, Дата Найма. Сохраните ее в файле с именем Address.

69.      Импортируйте ее (создайте на ее основе новую таблицу) в БД Туризм:

P откройте БД Туризм;

P Файл → Внешние данные → Импорт;

P если в файле Excel, который используется для импорта, находится несколько листов с таблицами, то необходимо указать, какую взять за основу для построения таблицы БД;

P далее Мастер импорта попросит уточнить, считать ли первую импортированную строку заголовками таблицы (на этом можно остановиться, нажав Готово);

P разрешите Мастеру импорта самому установить первичный ключ для импортируемой таблицы;

P присвойте новой таблице имя «Адреса».

70.      Проверьте наличие новой таблицы на вкладке Таблицы. Откройте и просмотрите ее.

71.      Закройте БД.

База данных как источник при слиянии документов в MS Word

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

73.      Откройте Word. Создайте в окне следующее письмо:

74.      Выделенные и заключенные в кавычки поля должны соответствовать полям таблицы Клиенты.

75.      Отправьте каждому групповому клиенту созданное письмо, осуществив слияние документов — текста письма и атрибутов адресата, взятых из БД. Для этого в окне Word выполните:

P Сервис — Письма и рассылки →Мастер слияния;

P далее нужно следовать указаниям Мастера слияния в нижней части окна;

P Источник данных: Получить дан­ные — Выбор получателей — выберите БД Туризм — в нем таблицу Клиенты;

P для выбора из таблицы только групповых клиентов установите автофильтр по Признаку группы;

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

P осуществите слияние данных, используя кнопку Просмотр писем панели инструментов Слияние.

76.      Просмотрите полученные результаты.

Задание 8. Макросы

Создание и редактирование макросов

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

«Autoехес» — запуск исходной заставки с кнопкой для запуска ос­новных объектов БД.

«Завершение» — макрос для выдачи на экран сообщения типа «До свидания!» и выхода из приложения.

«Открыть и расположить» — макрос, позволяющий одновременно открыть несколько объектов базы данных и размещающий их на экране в определенном порядке.

«Поиск продавца» — открывающий форму для таблицы «Продавцы» и выполняющий поиск записи, содержащей определенную фамилию, а также копирующий информацию в Буфер обмена.

77.      Откройте БД Туризм.

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

79.      Откройте форму, построенную на основе таблицы Договоры.

80.      Для создания макроса Завершение:

Рисунок 6 - Аргументы макрокоманды

P Макросы – Создать

P Макрокоманда - Сообщение;

P Макрокоманда — Выход — Сохранить все.

81.      Для создания макроса Открыть и расположить:

P Макросы — Создать;

P расположите окна БД и макроса без перекрытия командой Окно — Слева направо;

P в окне БД выберите вкладку Формы. Выделите и перетащите форму в окно макроса. Разместите в первой ячейке столбца Макрокоманда. В поле появится макрокоманда Открыть Форму. В столбец Примечание той же строки введите текст: «Открытие формы»;

P перейдите во вторую строку столбца Макрокоманда. С вкладки Таблицы перетащите таблицы Клиенты и Сотрудники в окно макроса во вторую и третью ячейки столбца Макрокоманда. Введите, если нужно, соответствующие примечания;

P в следующей свободной ячейке столбца Макрокоманда выберите команду Выполнить Команду. В области Аргументы макрокоманды в поле Команда выберите элемент Рядом Вертикально. В макрос будет включена операция разделения экрана в случае открытия нескольких окон.

82.      Запустите созданный макрос. Закройте все окна, кроме окна БД.

83.      Отредактируйте макрос Открыть и расположить. Для этого на вкладке Макросы выберите его. Войдите в Конструктор и щелкните мышью в одном из полей первой строки (или выделите ее целиком), вызовите контекстное меню — Добавить строки. В вставленной пус­той строке в поле Макрокоманда поместите команду Свернуть. Сохраните и запустите макрос.

84.      Создайте макрос Открыть форму Договоры. Проверьте его.

85.      Закройте все окна, кроме окна БД.

Создание группы макросов

86.      Создайте группу макросов с общим именем Группа. Включите в эту группу следующие макросы (см. рис.7):

Рисунок 7 - Конструктор группы макросов

P подача сигнала — макрокоманда Сигнал;

P открытие таблиц (Открыть таблицу) Договоры, Клиенты, Страны, Сотрудники;

P размещение открытых таблиц на экране в виде горизонтальной мозаики (Выполнить команду — Рядом Вертикально);

закрытие всех таблиц (Закрыть);

P выход из Ассеss (Выход).

87.      Проверьте работу каждого из макросов созданной группы.

Связывание макроса с событием

88.      Для создания макроса Autoexec:

P создайте макрос Открытие формы Договоры.

89.      Создайте новую форму, включив в нее заголовок «Вас приветствует база данных Туризм», какую-либо картинку и кнопку «Открыть форму Договоры».

P назначьте для этой кнопки макрос «Открыть форму Договоры» (контекстное меню на кнопке — Свойства — вкладка События — Нажатие кнопки — имя макроса). Закройте созданную форму и дайте ей название Заставка.

90.      Создайте новый макрос с именем Аutоехес, состоящий из следующих макрокоманд:

P ВыполнитьКоманду — Окно Закрыть;

P Открыть Форму;

P Развернуть.

91.      Выполните макрос Завершение.

92.      Загрузите БД Туризм. Проверьте работу макросов Autoexec и Открыть форму Договоры.

93.      Закройте БД. Запустите ее вновь с нажатой клавишей SН1FТ. При этом макрос Autoexec не выполняется.

Рисунок 8 - Форма заставка

94.      Создайте и выполните макрос «Поиск договора». Для этого:

ü откройте новое окно макроса и перетащите форму Договоры в первую строку;

ü  во второй строке выберите макрокоманду К элементу Управ­ления. Для аргумента «Имя элемента» установите значение Код клиента. Так задается поле, среди значений которого бу­дет осуществляться поиск;

ü  в следующей строке макроса выберите макрокоманду НайтиЗапись. Для аргумента «Образец поиска» задайте любой код клиента. Остальные аргументы оставьте без изменения.

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

Связывание макроса с кнопкой

95.      Создайте три макроса для открытия форм Клиенты, Сотрудники, Страны.

96.      Откройте форму Договоры и создайте в ней несколько кнопок для открытия всех форм БД. Каждую кнопку снабдите понятным названием или изображением.

97.      Свяжите каждую кнопку с соответствующим макросом.

98.      Проверьте все кнопки.

99.      Создайте кнопку на форме Договоры с изображением самолета. Свяжите ее с макросом Завершение. Снабдите кнопку всплывающей подсказкой «Завершение работы».

Выполнение макроса с условиями

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

100.   Создайте новый макрос и присвойте ему имя Групповой клиент:

ü если столбец Условие не отображается на экране, щелкните на кнопке Условие на панели инструментов или выполните команду Вид →Условие;

ü в первую ячейку столбца условий введите с помощью команды Построить логическое выражение [Forms]![Договор]![ Число туристов]>1;

ü в той же строке, но в столбце Макрокоманда выберите макрокоманду Сообщение и задайте значение «Групповой клиент» для аргумента «Сообщение». Именно эта фраза отобразится на эк­ране в окне сообщения при выполнении заданного условия. Для аргумента «Тип» установите значение «Информационное». При этом в окне сообщения, кроме текста, появится значок с изображением литеры «i» (стандартный вариант в Windows при выдаче сообщений), в поле Заголовок введите — «Внимание!»;

ü сохраните созданный макрос с именем Групповой клиент.

101.   Свяжите макрос с формой Договоры:

ü откройте форму Договоры в режиме Конструктора;

Рисунок 9 - ФормаДоговоры в режиме Конструктора

ü откройте окно свойств формы, выполнив двойной щелчок в указанной на рисунке области;

ü найдите на вкладке События поле После обновления и выберите в списке макрос Групповой клиент;

ü закройте окно свойств формы и перейдите в режим заполнения;

ü перейдите к новой записи и заполните ее (в поле Число туристов введите любое число больше 1. СООБЩЕНИЕ ПОЯВИТСЯ ТОЛЬКО ПОСЛЕ ЗАВЕРШЕНИЯ РАБОТЫ С ЗАПИСЬЮ.

102.   Закройте форму Договоры.

Комбинации клавиш для запуска макросов

103.   Назначьте для открытия таблицы Договоры — СТRL + нуль, для открытия формы Сотрудники — СТRL+F1.

104.   Сохраните файл.

Создание на основе макросов меню, контекстных меню и панелей инструментов

На основе группы макросов Группа создайте меню, панель инструментов и контекстное меню.

Свяжите появление меню (контекстного меню или панели инструментов) с активизацией формы Договоры («Строка меню» («Панель инструментов», «Контекстные меню» в Свойствах) — Группа).

Рисунок 10 - Свойства формы Договоры

105.   Откройте форму Договоры и проверьте все кнопки из группы макросов.

106.   Отправьте работу преподавателю на электронную почту – MarinaNazina@mail.ru, ответьте на контрольные вопросы.

Контрольные вопросы и задания

1.         Дайте понятие запроса.

2.         Для чего предназначены запросы?

3.         Какие виды запросов вы знаете?

4.         Дайте определение запроса на выборку.

5.         Какими способами можно создавать запросы?

6.         Для чего используют запрос с параметром?

7.         Какое средство используется для выполнения вычислений в запросах?

8.         Что означает запись в условии запроса «>=50»?

9.         Что означает запись в условии запроса «Like [Введите дату]»?

10.      Можно ли создавать запросы на основе нескольких таблиц?

11.      Каким образом отменяется вывод на экран какого-либо поля запроса?

12.      Какие виды сортировки записей предусмотрены в запросе?

13.      Для чего предназначены формы?

14.      Почему форма является незаменимым средством в БД?

15.      Назовите способы создания форм?

16.      На основе чего можно создавать формы?

17.      В каком режиме редактируется структура формы?

18.      Как создать кнопку на форме?

19.      Как можно разместить несколько таблиц и запросов на одной форме?

20.      Как создать главную кнопочную форму?

21.      На какой вкладке располагаются элементы управления для форм?

22.      Назовите основное отличие кнопочной формы от прочих форм?

23.      Для чего предназначены отчеты?

24.      Назовите структурные элементы отчета.

25.      Какие объекты базы данных могут служить основанием для отчета?

26.      Какие способы создания отчетов вы знаете?

27.      Назовите самый простой способ создания отчета.

28.      Как в отчетах можно посчитать итоговые значения?

29.      Какие итоговые значения можно посчитать в отчетах?

30.      Можно ли изменить отчет, если он создан в режиме Мастера отчетов?

31.      Как в Access напечатать почтовые наклейки?

32.      В каком режиме можно вносить изменения в конструкцию отчета?

33.      Что называется базой данных (БД)?

34.      Какие объекты базы данных Microsoft Access вы знаете?

35.      Какие существуют типы связей между таблицами?

36.      Как установить связи между таблицами?

37.      Как называется структура, отображающая связи между таблицами?

38.      Какие виды запросов вы знаете?

39.      Для чего используют запрос с параметром?

40.      Какие объекты служат основой для создания запросов?

41.      На основе чего создаются формы?

42.      Как добавить на форму какой-либо элемент управления?

43.      Какой режим предназначен для редактирования структуры объектов Access?

44.      Почему форма является незаменимым средством в БД?

45.      Как создать кнопку на форме?

46.      Какие объекты базы данных могут служить основанием для отчета?

47.      Для чего предназначены отчеты?

48.      Назовите основные способы создания объектов Access.