Работа с базами данных Microsoft Access 2000

3

Учебно-практическое пособие

"Работа с базами данных Microsoft Access 2000"

Введение

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

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

Система управления базами данных (СУБД) Microsoft Access 2000 поддерживает самую распространенную модель хранения данных - реляционную. Ссылка из одной таблицы на другую через какое-нибудь общее (ключевое) поле называется отношением (relation), а база данных, построенная на таком принципе, - реляционной.

1. Структура реляционной базы данных

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

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

Таблица 1

Объемы продаж фирмы «Гигант»
Покупатель	Товар	Ед.измер.	Кол-во	Цена за ед., руб.	Сумма, руб.	НДС%	Сумма с НДС	Дата сделки
Атлант	Бананы	кг	25	12		25		02.11.05
Рога и копыта	Бананы	кг	30	12		25		02.11.05
Атлант	Консервы	банка	20	15,5		25		01.11.05
Метеор	Консервы	банка	50	8,3		25		02.11.05
Атлант	Консервы	банка	20	7,5		25		05.11.05
Рога и копыта	Консервы	банка	60	8,7		25		05.11.05
Метеор	Конфеты	шт.	30	45		25		01.11.05
Рога и копыта	Конфеты	кг	25	10,5		25		01.11.05
Метеор	Конфеты	кг	40	12,5		25		05.11.05
Рога и копыта	Яблоки	кг	50	10		25		01.11.05
Метеор	Яблоки	кг	50	8		25		04.11.05
Атлант	Яблоки	кг	10	11,5		25		07.11.05
Атлант	Конфеты	кг	25	40		25		08.11.05
Рога и копыта	Бананы	кг	30	12		25		08.11.05
Атлант	Конфеты	кг	20	30		25		07.11.05
Метеор	Консервы	банка	20	8,3		25		09.11.05
Атлант	Консервы	банка	40	12		25		08.11.05
Рога и копыта	Консервы	банка	30	8,7		25		05.11.05
Метеор	Конфеты	шт.	10	45		25		08.11.05
Рога и копыта	Консервы	банка	25	5		25		08.11.05
Метеор	Бананы	кг	40	12,5		25		07.11.05
Рога и копыта	Конфеты	кг	50	25		25		09.11.05
Метеор	Яблоки	кг	50	8		25		08.11.05
Атлант	Яблоки	кг	100	11,5		25		05.11.05

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

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

Для повышения эффективности нормализуем таблицу Объемы продаж фирмы «Гигант» (табл. 1), разделив ее на отдельные таблицы: Покупатели (табл. 2), Товары (табл. 3), Заказы (табл. 4) со соответствующей структурой.

Таблица 2

Таблица Покупатели		Таблица Покупатели (структура)
ИДКл Покупатель Фамилия Адрес Город 1 Атлант Балаганов ул. Садовая, 45, кв. 8 Черноморск 2 Рога и копыта Бендер пр. Героев, 31, кв. 5 Москва 3 Метеор Щукина Речной пер., 3, кв. 17 Москва		ИДКл Покупатель Фамилия Адрес Город

Таблица 3

Таблица Товары		Таблица Товары (структура)
Код товара Товар Поставщик Ед. изм. Цена В на-личии 1 Бананы АО Экзотика кг. 300 200 2 Конфеты ЗАО Рот Фронт кг. 15 300 3 Яблоки АО Умелец кг. 45 500		КодТовара Товар Поставщик ЕдИзм. Цена В_наличии

Таблица 4

Таблица Заказы		Таблица Заказы (структура)
Код Заказа ИДКл Дата КодТовара НДС Кол-во 1 1 02.11.06 1 25% 25 2 2 02.11.06 1 25% 30 3 1 01.11.06 2 25% 20		КодЗаказа ИДКл Дата НДС КодТовара Кол-во

По сравнению с таблицей 1 в таблицы Покупатели (табл. 2) и Товары (табл. 3) добавлены поля (соответственно Фамилия, Адрес, Город и Поставщик, В наличии), которые позволяют поместить в них дополнительные сведения, необходимые для работы с клиентами и поставщиками товаров. Поля Сумма и СуммаНДС в таблицу Заказы не включены с целью экономии памяти компьютера. Значения этих атрибутов будем вычислять при обращении к БД с помощью запросов.

Каждая запись имеет ключ. Ключ - это поле или группа полей в таблице, которые индексируются для быстрого извлечения данных. Ключ может быть однозначным (unique) или неоднозначным в зависимости от того, разрешается ли в нем наличие повторений. Однозначный ключ может быть назначен первичным ключом (primary key), являющимся неповторяемым идентификатором для каждой строки таблицы. Если ключевое поле не установлено, то Microsoft Access автоматически устанавливает в качестве ключевого поле Счетчик. В приведенных таблицах идентификационные номера записей (выделены жирным шрифтом) являются первичными ключами таблиц, потому что однозначно определяют одну и только одну запись.

В таблице 5 внешние ключи выделены жирным курсивом.

Таблица 5

Покупатели		Заказы		Товары
ИДКл		КодЗаказа		КодТовара
Покупатель		ИДКл		Товар
Фамилия		Дата		Поставщик
Адрес		НДС		Ед.изм.
Город		КодТовара		Цена
		Кол-во		В наличии

Тип отношений, приведенный в таблице Заказы, называется отношением один ко многим, так как одни клиент может сделать несколько заказов, но конкретный заказ может быть размещен только одним заказчиком, и один товар может быть заказан несколькими клиентами.

Между таблицами Покупатели и Товары установлено отношение многие ко многим. Один клиент может заказать несколько товаров, а один товар может быть заказан несколькими клиентами. Отношение «многие ко многим» реализовано путем создания двух отношений «один ко многим», с общей таблицей «многие» (таблица Заказы), которая содержит внешние ключи других таблиц: таблица Заказы связана как с таблицей Товары (через поле КодТовара), так и с таблицей Покупатели (через поле ИДКл).

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

2. Создание базы данных

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

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

После этого появится диалоговое окно с предложением ввести имя базы данных. Введите имя базы данных и нажмите кнопку Создать. На экране появится окно База данных с именем базы (рис. 1).

Задание 1

1. Последовательно создайте таблицы Покупатели и Товары, структура которых определена в таблицах 2, 3 (или 5).

Для этого нажмите кнопку Таблицы в разделе Объекты и кнопку Создать на панели инструментов окна базы данных. Это приведет к появлению диалогового окна Новая таблица (рис. 2). Выберите Мастер таблиц и нажмите кнопку ОК.

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

Рисунок 1. Окно приложения Microsoft Access

	Рисунок 2. Диалоговое окно Новая таблица

Для добавления нужного поля в таблицу поместите на него маркер и нажмите кнопку со стрелкой . Если имя поля, предложенное Мастером таблиц, по какой-либо причине не устраивает, поместите маркер на соответствующее поле в списке Поля новой таблицы, нажмите кнопку Переименовать поле… и введите нужное имя. После того, как поля новой таблицы заданы, нажмите кнопку Далее и в появившемся диалоговом окне Создание таблицы введите имя таблицы - Покупатели и вновь нажмите кнопку Далее.

Рисунок 3. Диалоговое окно Создание таблиц

2. Создайте таблицу Заказы, структура которой определена в таблице 4 (или 5). При создании таблицы Заказы в диалоговом окне Создание таблицы нажмите кнопку Связи и выберите связи создаваемой таблицы Заказы с таблицами Покупатели и Товары базы данных. Например, один и тот же покупатель может оформить несколько заказов, т.е. одной записи таблицы Покупатели будет соответствовать много записей таблицы Заказы. Поэтому в диалоговом окне Связи нужно пометить соответствующий переключатель (рис. 4).



	Рисунок 4. Диалоговое окно Связи

После пометки нужного переключателя нажмите кнопку ОК, а затем - Далее.

Рисунок 5.

В диалоговом окне Создание таблиц (рис. 5) выберите переключатель Изменение структуры таблицы, если Вы хотите изменить структуру вновь созданной таблицы, и нажмите кнопку Готово. После этого будет выведено окно редактора структуры таблицы. Установите нужные свойства полей таблицы: размер поля, формат, значение по умолчанию, число десятичных знаков. Например, для таблицы Заказы окно будет иметь вид, показанный на рис 6.



	Рисунок 6. Окно редактора структуры таблицы Заказы

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

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

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

3. Проверьте правильность создания структуры базы данных.

Для проверки правильность организации таблиц базы данных нажмите кнопку Схема данных панели инструментов окна Microsoft Access. В результате на экране появится окно Схема данных (рис. 7), в котором будут показаны структуры таблиц БД и связи между ними. Для того, чтобы БД правильно выполняла свои функции, должна быть обеспечена целостность данных.



Рисунок 7. Окно Схема данных

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

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

Ё связанные поля имеют один тип данных;

Ё обе таблицы принадлежат одной базе данных Microsoft Access.

Ё

	Рисунок 8. Диалоговое окно Изменение связей

Для установления целостности данных созданной БД дважды щелкните мышкой на конкретной связи. После этого на экране появится диалоговое окно Изменение связей (рис. 8) установите в нем флажок Обеспечение целостности данных.

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

Для обеих связей нашей БД установите флажок Обеспечение целостности данных. После выполнения этой операции схема данных примет вид, показанный на рис. 9.

	Рисунок 9 Окно Схема данных

Задание 2

Создайте формы для ввода данных в таблицы Покупатели, Товары и Заказы.

3. Ввод данных в таблицы

Заполнить таблицу данными можно в режиме открытой таблицы. Это не всегда удобно. Лучше пользоваться для этой цели специально созданными формами. Чтобы создать формы для заполнения таблиц (Покупатели и Товары), воспользуйтесь Мастером форм. В окне База данных нажмите в списке кнопку Формы, а затем кнопку Создать. В окне Новая форма (рис. 10) выберите Мастер форм и имя таблицы, для которой создается форма, и нажмите кнопку ОК. В появившемся окне Создание форм выберите все поля таблицы (кнопка ).

	Рисунок 10. Диалоговое окно Новая форма

После этого нажмите кнопку Далее. В последовательности появляющихся окон Создание форм (рис. 11) выберите понравившееся вам оформление формы. С помощью созданных форм заполните таблицы Покупатели и Товары БД.

	Рисунок 11. Диалоговое окно Создание форм

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

Порядок замены поля ввода полем со списком:

Ё Откройте форму Заказы в режиме конструктора (рис. 12).

Ё Щелчком мыши выберите нужное поле ввода данных, а затем из меню Формат выберите последовательно Преобразовать элемент Поле со списком.

	Рисунок 12. Окно конструктора форм

Ё Нажмите кнопку Свойства панели инструментов и в появившемся окне свойств Поле со списком (рис. 13) установите свойства поля.

Рисунок 13. Окно свойств Поле со списком	Ё На вкладке Данные свойства: Источник строк - Покупатели, Присоединенный столбец - это столбец, данные из которого будут занесены в поле (первый столбец). Ё На вкладке Макет свойства: Число столбцов - количество столбцов таблицы, которое будет выведено в поле со списком (установите 2), Ширина списка - 6 см.

Ё Закройте окно свойств.

Проделайте то же самое для поля ввода Код товара. После настройки свойств полей формы закройте окно конструктора форм.

После открытия формы она примет вид, показанный на рис. 14.

	Рисунок 14. Форма для ввода данных в таблицу Заказы

Задание 3

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

Задание 4

Получить новую таблицу, подобную той, которая приведена в разделе 2:

Объемы продаж фирмы «Гигант»
Покупатель	Товар	Ед.измер.	Кол-во	Цена за ед.	Сумма	НДС	Сумма с НДС	Дата сделки
Атлант	Бананы	кг	25	12		25		02.11.05
Рога и копыта	Бананы	кг	30	12		25		02.11.05
Атлант	Консервы	банка	20	15,5		25		01.11.05

4. Создание запроса к БД

Для этой цели создайте Запрос к базе данных - новую таблицу, в которой будут использованы данные таблиц Запросы, Покупатели и Товары:

Рисунок 15. Окно Новый запрос	Ё Нажмите кнопку Запросы в области Объекты и кнопку Создать панели инструментов окна База данных. Ё В окне Новый запрос (рис. 15) выберите Простой запрос и нажмите кнопку ОК. Ё В открывшемся окне Создание простых запросов выберите из таблиц базы данных нужные поля и нажмите кнопку Далее.

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

Ё В последнем окне Создание простых запросов задайте имя запроса - ЗаказыЗапрос, пометьте переключатель Изменить макет запроса и нажмите кнопку Готово.

На экране появится окно конструктора запросов (рис. 16).

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



Рисунок 16. Окно конструктора запросов

1. В созданной таблице-запросе отсутствуют поля Сумма и Сумма с НДС. Любым из описанных способов добавьте их в таблицу-запрос.

2. В таблице-запросе поля Сумма и СуммаНДС вычисляемые.

Для определения содержимого поля Сумма надстройки поместите в него курсор и нажмите кнопку Построить на панели инструментов окна Microsoft Access.

После этого на экране появится окно Построитель выражений (рис. 17)

Общие сведения о построителе выражений

В области Элементы выражений окна Построителя выражений находятся три поля.

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

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

Ё В правом поле выводится список значений (если они существуют) для элементов, заданных левым и средним полями

Порядок создания выражения:

Ё в поле Элементы БД, функции и др. раскройте список таблиц и выберите нужную таблицу;

Ё в поле Категории элементов выберите нужное поле, дважды щелкните его мышкой, чтобы вставить его в Поле построителя выражений, или нажмите кнопку Вставить;

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

Рисунок 17 Построитель выражений

После создания выражения нажмите кнопку ОК окна Построитель выражений. Тем самым Вы вернетесь в окно конструктора запросов. В Надстройке появится созданное выражение. Отредактируйте его, если оно выглядит не так: .

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

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

5. Создание отчетов и диаграмм

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

Задание 5

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

Для создания отчёта нажмите кнопку Отчеты в области Объекты и кнопку Создать панели инструментов окна База данных.



Рисунок 18. Окно конструктора отчета

В окне Новый отчет выберите Мастер отчетов, а в поле Источника данный основную таблицу ЗаказыЗапрос, и нажмите ОК.

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

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

Изменение названия отчета и названий колонок производится в области Заголовок отчета и Верхний колонтитул вводом нужного текста.

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

Задание 6

3. Используя Мастер отчетов, создайте отчет по созданному Вами запросу.

4. Создайте перекрестный запрос для получения таблицы:

Ё В окне Новый запрос Мастера запросов выберите Перекрестный запрос и нажмите кнопку ОК.

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

Ё В окне Создание перекрестных таблиц из поля Доступные поля поле Дата переместите в поле Выбранные поля. В результате в таблице в поле Образец окна появится заполненный столбец (рис. 19). Нажмите кнопку Далее.

Ё В окне Создание перекрестных таблиц выберите в качестве заголовков столбцов поле Товар. Нажмите кнопку Далее.

Ё В окне Создание перекрестных таблиц в списке Поле выберите поле СуммаНДС, в списке Функции - Сумма. Нажмите кнопку Далее (рис. 20).



Рисунок 19. Окно Создание перекрестных таблиц

Ё Задайте имя запросу Объемы продаж по датам. Посмотрите созданный запрос.

	Рисунок 20.

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

4. Получите отчет с нарастающим итогам по столбцам. Для этого откройте отчет Объемы продаж по датам в режиме конструктора. В области Данные выберите поле Бананы нажмите кнопку Свойства на панели инструментов Microsoft Access. В окне свойств Поле: Бананы (рис. 21) на вкладке Данные в поле Сумма с накоплением выберите Для всего.

	Рисунок 21. Окно свойств: поле - Бананы

Проделайте то же самое для других полей (Конфеты, Консервы, Яблоки). Просмотрите отчет:

Объемы продаж по датам

Дата	Итоговое значение	Бананы	Консервы	Конфеты	Яблоки
01.11.2005	3 028,13 р.	387,50 р.	2 015,63 р.	625,00 р.
02.11.2005	1 343,75 р.	825,00 р.	906,25 р.	2 015,63 р.	625,00 р.
04.11.2005	500,00 р.	825,00 р.	906,25 р.	2 015,63 р.	1 125,00 р.
05.11.2005	3 228,75 р.	825,00 р.	2 072,50 р.	2 640,63 р.	2 562,50 р.
07.11.2005	1 518,75 р.	1 450,00 р.	2 072,50 р.	3 390,63 р.	2 706,25 р.
08.11.2005	3 518,75 р.	1 900,00 р.	2 828,75 р.	5 203,13 р.	3 206,25 р.
09.11.2005	1 770,00 р.	1 900,00 р.	3 036,25 р.	6 765,63 р.	3 206,25 р.

5. На основе запроса Объемы продаж по датам создайте диаграмму. Воспользуйтесь для этой цели Мастером отчетов: в окне Новый отчет выберите Мастер диаграмм. Нажмите кнопку ОК.

Ё В окне Создание диаграмм из списка Доступные поля выберите все поля, кроме поля Итоговое значение СуммаНДС и нажмите кнопку Далее.

Ё В окне Создание диаграмм выберите тип диаграммы Гистограмма и нажмите кнопку Далее.

Рисунок 22. Окно Создание диаграмм

Ё В окне Создание диаграмм (рис. 22) в области диаграммы нажмите кнопку Итоги по месяцам. На экране появится окно Группировка. Выберите в нем По дням и нажмите кнопку ОК.



Рисунок 23. Окно Группировка	Ё Перетащите кнопки Конфеты, Консервы, Яблоки к кнопке Бананы в области диаграммы. Ё Закончите построение диаграммы, просмотрите ее и сохрание отчет под именем Диаграмма. 6. Откройте отчет Диаграмма в режиме конструктора. Отредактируйте внешний вид диаграммы. Для этого дважды щелкните мышкой в поле диаграммы. Дальнейший порядок редактирования такой же, как и при редактировании диаграмм в Microsoft Excel.