Использование СУБД Microsoft Access на примере создания базы данных по учету товаров

Объект Модуль.

Если возможностей макросов недостаточно, можно обратиться к модулям. Модуль - объект, содержащий программы (процедуры или функции) на языке VBA (Visual Basic for Applications). Эти процедуры или функции можно использовать для сложных вычислений, которые не могут быть представлены последовательностью простых математических вычислений. Обычно процедура или функция связывается с некоторым событием, таким как нажатие кнопки в активной форме или отчете.

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

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

Таблица 4.1. Таблица товаров на складе

Номер товара	Номер поставщика	Название товара	Цена, руб./шт.	Количество на складе
		Магнитола
		Приемник
		Плеер
		Кофеварка
		Электрочайник
		СD - Плеер

Таблица 4.2. Таблица поставщиков

Номер поставщика	Название фирмы	Город	Адрес	Телефон
	ЗАО «Посредник»	Омск	ул. Космонавтов, 12
	«Импульс»	Москва	Пр. Мира, 5
	«Старт»	Тверь	ул. Зеленая, 11

Таблица товаров на складе связана с таблицей поставщиков через поле «Номер поставщика» (внешний ключ для таблицы товаров).

При запуске Access для создания БД выбираем режим “Новая БД”. Затем в появляющемся окне задаем имя и расположение файла новой базы данных Microsoft Access (расширение файла -.mdb).

После создания файла базы данных появляется окно для работы с созданной базой (разделы «Таблицы», «Запросы», «Формы», «Отчеты», «Макросы», «Модули»). Так как таблицы базы данных еще не созданы, то доступна только кнопка «Создать» на странице «Таблицы».

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

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

В Microsoft Access свойства полей используются при просмотре или изменении данных пользователем. Например, заданные пользователем значения свойств «Формат поля» (Format), «Маска ввода» (InputMask) и «Подпись» (Caption) определяют вид данных таблицы и запроса. Элементы управления в новых формах и отчетах, присоединенные к полям таблицы, наследуют эти свойства полей базовой таблицы по умолчанию. Другие свойства позволяют определить условия на значения полей или задать обязательный ввод данных в поле. Microsoft Access будет проверять выполнение этих условий при каждом добавлении или изменении данных в таблице.

В появляющейся пустой таблице нужно задать имена всех полей таблицы и соответствующие им типы данных. Поле “Номер_товара” является ключевым и для него выбирается тип “Счетчик” (уникальность этого поля будет поддерживаться системой Microsoft Access автоматически).

Для остальных полей зададим соответствующие им типы данных (см. рис. 4.1). Поле «Номер поставщика» обеспечивает связь со второй таблицей.

После задания имен полей и их типа, таблица сохраняется с заданием ее имени («Товары на складе»).

Рис. 4.1. Макет таблицы “Товары_на_складе”

Создание второй таблицы проведем в режиме «Мастера таблиц» (пункт меню «Мастер таблиц» при создании новой таблицы). В окне последовательно выбираются образец таблицы и необходимые поля из имеющихся в списке образца. После выделения нужного поля осуществляется перенос в окно полей новой таблицы с помощью соответ-ствующих кнопок (рис. 4.2).

При щелчке по кнопке «Далее» появляется окно для задания имени таблицы. В нем вводится имя таблицы – «По-ставщики», выби-рается способ оп-ределения ключа.

Рис. 4.2. Выбор полей из имеющихся образцов таблиц

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

Рис. 4.3. Выбор способа определения ключа

Далее осуществляется переход к окну (рис. 4.4) для задания связей между таблицами (изначально таблица «Поставщики» не связана с таблицей «товары на складе»). При щелчке по кнопке «Связи», появляется окно для задания связи между таблицами и выбора типа этой связи (рис. 4.5).

Рис. 4.4. Возможность выбора установления связей между таблицами

Возможны несколько типов связи:

1. Одной записи таблицы «Поставщики» будет соответствовать много записей таблицы «товары на складе», т.е. один поставщик может поставлять несколько товаров. Такая связь называется «один ко многим».

2. Одной записи таблицы «Поставщики» будет соответствовать одна запись таблицы «товары на складе», т.е. один поставщик может поставлять только один товар. Такая связь называется «один к одному».

3. Одной записи таблицы «товары на складе» будет соответствовать много записей таблицы «Поставщики», т.е. один товар может поставляться несколькими поставщиками. Такая связь также называется «один ко многим».

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

Так как в исходных таблицах видно, что на складе имеется несколько товаров одного поставщика, то выбираем связь « один ко многим» (рис. 4.5). Мастер таблиц автоматически добавляет поле «Код поставщика» в таблицу «товары на складе» и создает эту связь. Таким образом, созданное ранее поле «Номер поставщика» в таблице «товары на складе» оказывается излишним и его надо в дальнейшем удалить. Вместо него система автоматически добавила нужное поле.

Рис. 4.5. Связывание таблиц

Рис. 4.6. Возможность выбора способа ввода данных

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

Заданные макеты таблиц и связи между ними можно посмотреть с помощью соответствующих средств. Для обеспечения целостности данных надо выполнить «Обеспечение целостности данных» (в горизонтальном меню выбрать пункт «Сервис», а в раскрывающемся подменю пункт «Схема данных»). Схема данных показана на рис. 4.7. В окне «Схема данных» надо выбрать правой кнопкой контекстное меню объекта «связь» и из двух пунктов меню «изменить связь» и «удалить» выбрать первый. Далее включить необходимые флажки (см. п. 3.2).

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

Рис. 4.7. Схема данных без целостности данных

Рис. 4.8. Удаление поля

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

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

Рассмотрим некоторые из средств для работы с БД в Microsoft Access. Раздел «Формы» (рис. 4.9) позволяет создавать различные формы данных для заполнения таблиц информацией на основе существующих связных таблиц. Используем для этого «мастер форм».

Рис. 4.9. Выбор способа создания формы

Последовательно из таблиц выбираются требуемые поля для формы, в нашем случае мы выбираем все поля обеих таблиц, т.к. необходимо внести все исходные данные (рис. 4.10 и 4.11). После выбора полей таблиц выбирается тип представления данных (в нашем случае выбираем «Подчиненные формы», т.к. при этом внешний ключ во второй таблице будет формироваться автоматически, что предотвратит внесение лишних ошибок), внешний вид («ленточный») и стиль представления данных (рис. 4.12 и 4.13).

Рис. 4.10. Выбор полей таблицы «Поставщики»

Рис. 4.11. Добавление полей таблицы «Товары_на_складе»

Рис. 4.12. Выбор типа представления данных

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

Рис. 4.13. Выбор стиля оформления формы

Для выбранной структуры, показанное на рис. 4.13 окно позволяет выбрать стиль представления данных.

В следующем окне (рис. 4.14) нужно задать имена для каждой связанной формы («Поставщики», «Товары на складе»). При щелчке по кнопке «Готово» в соответствии с заданной опцией «открытие формы на просмотр и ввод данных» появляется окно формы «Поставщики1» с кнопкой открытия связанной формы «Товары на складе». Обе связанные формы показаны на рис. 4.15.

Рис. 4.14. Окно для выбора возможностей работы с формой

Значения ключей в поле «код поставщика» и «Номер_товара» формируются автоматически.

Для каждого выбранного в форме поставщика в связанной форме «товары на складе» можно просмотреть список поставляемых им товаров.

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

Заполним формы данными из таблиц 4.1 и 4.2 соответственно и добавим еще произвольно по одной записи по каждому поставщику.

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

Рис. 4.15. Подчиненные формы с введенной информацией

Например, на рис. 4.16 выбраны поля «Название_товара», «Цена», «Количество_на_складе» из таблицы «Товары_на_складе», поле «Город» из таблицы «Поставщики» и заданы следующие условия:

для «Цены» задано условие: >1000 And < 4000;

для «Количества_на_складе» задано условие: >10 And < 40;

для «Города» задано условие: «Омск».

Другими словами, требуется получить данные о товарах, поставляемых из города Омска, цена которых лежит в пределах от 1000 до 4000, а количество на складе в пределах от 10 до 40.

При закрытии данного окна выводится окно для задания имени запроса (запрос сохраняется в списке запросов). При выделении запроса и щелчке по кнопке «Открыть» появляется таблица с результатами выполнения этого запроса (рис. 4.17).

В пункте «Вид» горизонтального меню можно выбрать пункт «Режим SQL». В результате выводится окно с текстом запроса на языке SQL (рис. 4.18). Правила построения SQL-запросов приводятся в главе 2.

Рассмотренный запрос относятся к запросам с критериями поиска. Для избежания создания нового запроса для каждого значения критерия служат запросы с параметром. Для построения запроса с параметром необходимо в окне «Параметры запроса» (пункт горизонтального меню «Запрос \ Параметры») указать имя и тип параметра (рис. 4.19).

Рис. 4.16. Бланк простого запроса на выборку.

Рис. 4.17. Результат выполнения запроса

Рис. 4.18. Запрос на языке SQL

Рис. 4.19. Построение запроса с параметром

В этом случае, при выделении запроса и щелчке по кнопке «Открыть» появляется окно (рис. 4.20), где необходимо ввести конкретное значение критерия. Таблица с результатами запроса по форме аналогична представленной на рис. 4.17.

Рис. 4.20. Окно для задания значения параметра

Как правило, при проектировании таблиц нужно стремиться не перегружать их данными. Так, например, в таблице, содержащей сведения о товарах, достаточно сохранить цену за единицу товара и количество товаров на складе, поле для общей стоимости будет лишним, т.к. его можно определить на основании уже имеющейся информации. Для этого следует составить запрос с вычисляемым полем «Стоимость» при помощи следующего выражения в новом столбце (рис. 4.21).

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

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

Результат выполнения запроса представлен на рис. 4.22.

Рис. 4.22. Результат выполнения запроса

Если в таблице содержится большое количество записей, то для вычисления итоговых значений используют итоговые запросы. Для построения итогового запроса необходимо воспользоваться командой «Вид \ Групповая операция» и выбрать для конкретных полей подходящую функцию. Пример такого запроса и результат его выполнения приведены на рис. 4.23 и рис. 4.24.

В СУБД Access имеются развитые средства для создания отчетов по имеющейся в базе данных информации (раздел «Отчеты», щелчок по кнопке «Создать»). В окне «Новый отчет» выбирается «мастер отчетов» для создания отчета (рис. 4.25). В качества источника данных для отчета можно использовать исходные таблицы или результаты выполнения запросов. Список для выбора таблиц или запросов находится в нижней части окна (рис. 4.25). Рассмотрим возможность построения отчета посредством «Мастера отчетов».

Рис. 4.23. Бланк итогового запроса

Рис. 4.24. Результат выполнения итогового запроса

После нажатия кнопки «ОК» в окне «Новый отчет» появляется окно мастера (рис. 4.26) для задания полей, включаемых в отчет. Можно включить данные из разных таблиц (на рис. 4.26 и 4.27 последовательно выбираются данные для отчета из таблиц «Товары_на_складе» и «Поставщики»).

Рис. 4.25. Выбор метода и источника построения отчета

Рис. 4.26. Выбор полей из таблицы «Товары_на_складе»

Рис. 4.27. Добавление полей из таблицы «Поставщики»

После выбора полей данных для отчета, выбираются тип представления (рис. 4.28), вид сортировки данных в выбранных полях (задана сортировка данных по алфавиту в поле «Название товара») и ввод в отчет рассчитываемых итоговых данных (рис. 4.29). Для ввода в отчет итоговых данных используется кнопка «итоги» (рис. 4.29) и задание вида итоговых расчетов (рис. 4.30).

Рис. 4.28. Выбор типа представления данных

Рис. 4.29. Выбор порядка сортировки и переход к окну «Итоги»

Рис. 4.30. Выбор итоговых вычислений

Здесь выбрано суммирование по полям «Стоимость» и «Количество на складе» (флажки «Sum»). Заданные итоговые поля будут добавлены к отчету.

Рис. 4.31. Выбор макета отчета

Окно (рис. 4.31) позволяет задать макет отчета (расположение информации на странице, ориентация страницы).

Выбор стиля отчета показан на рис. 4.32.

Рис. 4.32. Выбор стиля оформления отчета

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

Если что-нибудь не устраивает во внешнем виде сформированного отчета, то в режиме конструктора можно исправить все недостатки и неточности в его оформлении. Результаты выполнения полученного отчета показан на рис. 4.33.

Рис. 4.33. Общий вид отчета

Дата добавления: 2014-12-27